Improving Index Seek

Question

Post reply

Improving Index Seek

DBABC

SSCarpal Tunnel

Points: 4813
More actions
October 10, 2014 at 2:18 pm

#291472

I have a query that is performing index seek on a large table and taking about 5 minutes, is it possible to further improve the attached index seek?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 1

Are your statistics up to date for sure?

The Redneck DBA

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 2

DBABC

SSCarpal Tunnel

Points: 4813

October 10, 2014 at 2:59 pm

#1751746

Yes, they are.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

Jason beat me toops it...Estimated rows are 1, actual are 860... sounds like a possible stats issue.

Could also be parameter sniffing. Have you tried running your query with a recompile? Isthis a production environment? If not you could consider clearing your query cache (dbcc freeproccache) if possible.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 4

I can try to rebuild the stats, but if stats are out of date it still picked the correct index.

It's a prod server so can't clear the cache but i can try the query with recompile.

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 5

I rebuild the stats and reran the query, attached is the Index Seek values on the same index.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 6

Estimated 20, Actual 861K.... Uhm, I'm thinking a seek is absolutely a bad thing for this query to be running. Are we looking at a bad parameter sniffing? Really bad data skew? Without the full execution plan (and not just a picture), it's hard to say everything that might be going on here. Is the optimizer finding a good enough plan or is it timing out?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 7

UDBA (10/10/2014)
I have a query that is performing index seek on a large table and taking about 5 minutes, is it possible to further improve the attached index seek?

Look at the number of executions. It's doing a SEEK for each row. That's worse than a table scan.

You need to write a different index (or change your query)... one that will do a single seek through the B-Tree to the begining of a contiguous group of rows at the leaf level of the index.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 8

The table has about 1.5 billion rows, maybe that's why it's not able to correctly estimate the "Estimated Rows". Query is not timing out, it is using the execution plan i attached to the original post but it takes about 10 minutes to run. So i noticed the row by row execution of Index Seek with very low estimated rows so wondered if we can further improve it.

Unfortunately i cannot provide the full execution plan due to some security restrictions.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 9

UDBA (10/13/2014)
The table has about 1.5 billion rows, maybe that's why it's not able to correctly estimate the "Estimated Rows". Query is not timing out, it is using the execution plan i attached to the original post but it takes about 10 minutes to run. So i noticed the row by row execution of Index Seek with very low estimated rows so wondered if we can further improve it.
Unfortunately i cannot provide the full execution plan due to some security restrictions.

I've already explained a couple of methods... one is to provide an index more appropriate to the query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

UDBA (10/13/2014)
...Query is not timing out, it is using the execution plan i attached to the original post but it takes about 10 minutes to run...

Grant isn't talking about the query timing out - he's talking about the optimiser. Right-click on the leftmost operator, the SELECT or whatever, and look for "Time out".

This looks like the inner input of a nested loops inner join. The actual number of rows returned is 861207 and 861374.85033 singleton seeks are estimated to retrieve those rows one at a time (hence estimated number of rows ~ 1), more than close enough and suggesting stats are not the problem.

That's a heck of a lot of seeks though, as Jeff points out. You could try a FORCESCAN hint just for fun. You're not returning much from the index, does it contain columns in the key or INCLUDE list which the query doesn't reference?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Can you please post the actual execution plan, along with table and index definitions? You can do a search/replace through the XML to replace the table names if you're paranoid about people knowing them (table names are usually not sensitive info), but we really do need to see it to help.

As it is, it's incredibly hard to say anything useful about this. It may be the inner table of a nested loop (explaining the high executions) which means the problem is the choice of join, not the index. But without the entire execution plan, can't say.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 12

UDBA (10/13/2014)
The table has about 1.5 billion rows, maybe that's why it's not able to correctly estimate the "Estimated Rows". Query is not timing out, it is using the execution plan i attached to the original post but it takes about 10 minutes to run. So i noticed the row by row execution of Index Seek with very low estimated rows so wondered if we can further improve it.
Unfortunately i cannot provide the full execution plan due to some security restrictions.

Understood. But it's not about making the seek faster. It's whether or not the seek is appropriate, and it doesn't seem to be in this instance. I'm with Jeff, I'd examine what you're filtering or joining on to understand if you have the right index in place.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ScottPletcher SSC Guru Points: 100946 More actions · Answer 13

Since the table has 1.5B rows, the ~860K rows accessed is only ~0.06%, so I can see why SQL chose to do (a lot of) SEEKs rather than a scan. But, since the rows are narrow (small), perhaps performance still might be better off with a scan(?).

Would a filtered index perhaps be available here?

Have you run the query with SET STATISTICS IO ON to verify overall IO usage. Btw, just curious, is SQL using a work table / spool in conjunction with that seek?

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.