Any way to speed up a PK?

Dale 2016-11-22 22:41:54

There is a table with a billion rows that we are querying. In the statement that is taking up 96% of the total runtime, 42% of that is taken up by a Clustered Index seek. What are some of the avenues I should pursue when speeding it up?

SQLkiwi 2016-11-23 07:27:29
The index seek in question:

alt text

…would be responsible for 42.5% of the runtime if the optimizer's cost model exactly matched the performance characteristics of your hardware and the plan actually produced the number of rows, with the same distribution, as the optimizer expected. The cost numbers in execution plans for your version of SQL Server are always estimates, even in a post-execution (actual) plan.

Looking at the same plan operator with the row counts actually encountered:

alt text

…you can see that no rows arrived at the nested loops join driving the seek, so the seek was never asked to return any rows at all.

The cost percentages are there to indicate the operations the optimizer expected to be expensive. If it was wrong, it was wrong, and all bets are off: the whole basis for choosing the plan is suspect.

Look for operations that seem inefficient or produce a large number of rows as a starting point, and try to understand what could be done to make those more efficient. Often that means working with smaller parts of the query, and improving the indexes, statistics, and/or query text until performance is acceptable.