5

I run the following (really simple) query in SQL Server:

    SELECT MAX(PK_Field1)
    FROM MainTable kh
    WHERE kh.PK_Field1 >= '2014-12-01T00:00:00'
    AND kh.PK_Field2 = 1572
    AND kh.PK_Field3= 'FD5BF2F3-8ED7-479C-A71F-D04E4288CBFC'

And I get these stats from it:

Table 'MainTable'. Scan count 9, logical reads 31078, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 171 ms,  elapsed time = 44 ms.

Although the elapsed time is not that much, this query is executed around a thousand times every ten minutes, so having more than 30k logical reads doesn't seem quite optimal to me. However, the main index in this table is designed in a way that a query like that can take full advantage from it. The table MainTable contains this:

PK_Field1 datetime
PK_Field2 int
PK_Field3 uniqueidentifier
Another_Field datetime

This table contains no other columns, it has 3 million records and the only index is the clustered one on PK fields (in same order as defined in the table, ordered ASC). The actual execution plan shows the following:

enter image description here

Additionally, fields PK_Field2 and PK_Field3 are foreign key referencing two other tables. Referenced columns are IDs in their respective tables.

What do you think? Is there room for some optimization?

Improve this question
5
  • 1
    You need an index on (field2, field3, field1)
    – ypercubeᵀᴹ
    Apr 23, 2015 at 11:12
  • The clustered index contains these three fields already (but ordered as they appear defined in the table), and that's why the operation used is Index Seek.
    – Hauri
    Apr 23, 2015 at 11:24
  • 4
    Yes but the order matters.
    – ypercubeᵀᴹ
    Apr 23, 2015 at 11:25
  • 5
    An index in a different order may be slightly more efficient, depending on how selective each column is (and in turn how many more reads the first column can eliminate). However, your query ran in 44 milliseconds. So out of 10 minutes, this query is running 1000 times for a total of 44 seconds. If this is the only query in your system that needs optimizing, you've done a great job. I suspect you have bigger fish to fry, though.
    – Aaron Bertrand
    Apr 23, 2015 at 12:34
  • 1
    @Hauri, the inequality operator for date will require all rows >= the specified date to be touched. I suspect the suggested index with field1 last will greatly reduce the logical reads and likely eliminate the nasty parallelism operator.
    – Dan Guzman
    Apr 24, 2015 at 0:41

1 Answer 1

Reset to default
3

Sorry for the late response, I've been quite busy. I tried reordering the columns in the index by adding a new non-clustered index with the following fields and order:

PK_Field3 uniqueidentifier
PK_Field1 datetime
PK_Field2 int

The results are great:

(1 filas afectadas)
Table 'MainTable'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 filas afectadas)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

The actual query plan shows no parallellism. Now I have a tough work to decide whether to keep it or not, despite the good results, since I don't find quite optimal to keep a 100 MB index for a single query. If I find out it is useful for other usual queries I'll keep it. Thanks for your suggestions!

Improve this answer
2
  • 1
    This is as @Dan Guzman pointed out before to do with the selectivity of the individual keys. As you are seeking a single record by UUID and int with the date as the first key in the index the optimizer has no way of finding that record by other means than to scan through the index. By having the field with the highest selectivity at the top you help the optimizer.
    – Spörri
    Apr 28, 2015 at 8:41
  • 2
    @Spörri it has to do with the selectivity but also - and more important - has to do with the range condition on the field1, which affects the selectivity for this particular query.
    – ypercubeᵀᴹ
    Apr 28, 2015 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.