Trying to figure out if there is a way to avoid the index scan and make it a seek in this plan. The query is performing fine, this is more for a learning opportunity for me.
2016-05-11 17:24:48
2016-05-12 00:37:19
The scan is the optimal choice because all rows are needed. There are no predicates (filtering conditions) that could be used to seek.
You can turn any index scan into a (meaningless) index seek by adding a predicate that covers the whole index, for example:
WHERE Client_Hostname >= '' OR Client_Hostname IS NULL
…but this would be pointless, and counter-productive. A seek can be useful if it helps reduce the number of rows returned by a data access operator, but a seek that returns the same rows a scan would is simply less efficient. Many people new to SQL Server fall into the trap of thinking a seek is always better than a scan; this is not so.
There are some small improvements I would make to your query:
- Use alias names consistently for table and column references
- Quote reserved words (or use names that are not reserved)
- Timestamp is a particularly poor choice because it has a (deprecated) special meaning in SQL Server that has nothing to do with dates or times
- Terminate statements with a semicolon
- Defer the conversion from KB to MB until after the aggregates
With those in place:
SELECT DISTINCT EEFG.Database_Name, EEFG.Client_Hostname, Days_Measured = DATEDIFF(DAY, MIN(EEFG.[timestamp]), MAX(EEFG.[timestamp])), Change_In_File_Size = (MAX(EEFG.Total_Size_KB) - MIN(EEFG.Total_Size_KB)) / 1024 FROM EE.File_Growth AS EEFG GROUP BY EEFG.Database_Name, EEFG.Client_Hostname;
You may notice a small increase in efficiency by performing the arithmetic on the result of the aggregates rather than on every row before it is aggregated.