Any way to speed up a PK?

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?

avatar image By Dale 3 asked Nov 22, 2016 at 10:41 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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.

sp.png (39.0 kB)
sp.png (15.5 kB)
avatar image By SQLkiwi ♦ 6.6k answered Nov 23, 2016 at 07:27 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.