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.

Brian J. Campbell 2016-05-11 17:24:48

SQLkiwi 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:

  1. Use alias names consistently for table and column references
  2. Quote reserved words (or use names that are not reserved)
  3. 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
  4. Terminate statements with a semicolon
  5. 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.