So in the case of this index, where it's one column (totalcount) and INCLUDEs the other 4 columns. Correct me if I'm wrong, when the index is used, the engine seeks on the index and returns many pages (80%) which satisfy the totalcount > 0 clause.
Then from that 80%, the engine does an additional scan on those pages to return the remaining data (we'll still use the 1% number)
But if you were to use the 4 columns in the index and INCLUDE totalcount, the engine would seek the 1% of records in the index and then scan 80% of the 1% to find totalcount > 0
Then it has to do additional work, for the WHERE and GROUP BY because the data are in rather random order, regarding cgcode, year, month
@JacobH Yes (more or less) for that. It would seek the 1% of the data and then additional work to keep only the 80% of them (or whatever) checking the totalcount>0 and month/year conditions
First of all, I would rewrite this condition. As it is, it can't use any index effectively.:
datefromparts(cast(year as char(4)),cast(month as char(2)),'01') < getdate()
Rewrite it so there is no functions or calculations applied to the columns:
( year < year(getdate())
or year = year(getda...
Nice answer. Theoretically, would include (id, totalcount) in the index be better performance than the filtered where totalcount > 0? I guess only for this query and not for any that don't have the where clause? It would be interesting to see what the existing index has for columns at the moment.
@JacobH right, if there are different conditions (regarding totalcount) used in different queries, then option A is better. If only this one (totalcount > 0) is used, then option B looks much better. It may be much more efficient (space-wise and efficiency-wise) if the number of rows matching the condition is small. And even if it isn't (very small), the index would still store one column less than the include (totalcount) index.
So since the actual index is on totalcount INCLUDE cgcode, year, month, fein how much is the performance difference from cgcode, year, month, feing INCLUDE totalcount? Assuming the same query is run.
@JacobH Ah, about the existing index on the table? The query has to do a full index scan on it (well, a "partial" scan, only for rows with totalcount>0). So, if the number of rows with totalcount>0 is (say) 80%, 80% of the index has to be read. For the (cgcode, year, month, fein, id) INCLUDE (totalcount) - even without my rewrite - only the part that has cgcode = 123 has to be read. If there are 100 distinct cgcodes (and even distribution), only 1% of the index will be read.
Even when the existing index includes those 4 columns as well? What effect does INCLUDE have in the latter case? A seek on "80% of 1%" instead of a seek on "1% of 80%"? Would be interesting to see if @Kevin3NF tests your index and reports back with the performance increase.
First of all, I would rewrite this condition. As it is, it can't use any index effectively.:
datefromparts(cast(year as char(4)),cast(month as char(2)),'01') < getdate()
Rewrite it so there is no functions or calculations applied to the columns:
( year < year(getdate())
or year = year(getda...
T-SQL Optimization
T-SQL Optimization