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.

avatar image By Brian J. Campbell 16 asked May 11, 2016 at 05:24 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By SQLkiwi ♦ 6.6k answered May 12, 2016 at 12:37 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x166
x117
x44
x39

asked: May 11, 2016 at 05:24 PM

Seen: 54 times

Last Updated: May 12, 2016 at 12:37 AM