Select query in a stored procedure running for long time

Question

Select query in a stored procedure running for long time

coolchaitu

SSChampion

Points: 10334
More actions
July 31, 2015 at 12:37 pm

#310518

We have a stored procedure.It has a select query and its taking long time.I have attached the image of select query,image of actual execution plan and image of index scan details.The index scan is costing 80%
Please help on what to do so that the query gets tuned.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

That doesn't help a lot. You should post DDL for the tables, views and indexes involved and actual execution plan (right click on the execution plan and Save As...).

The index scan might be the issue or it might not. It's very possible that the problem is the remote query.

One thing is sure, the NOLOCK hints are not helping with performance.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

coolchaitu SSChampion Points: 10334 More actions · Answer 2

Thanks for the reply. I had attached the execution plan in my previous post.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

coolchaitu (7/31/2015)
Thanks for the reply. I had attached the execution plan in my previous post.

No, you didn't. You attached an image of the execution plan which isn't the same thing as lots of information is lost.

Read the following article to post performance problems: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

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

Please post the query, table definitions, index definitions and the actual execution plan. Without those it's near-impossible to offer any useful advice.

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

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 5

it seems your using OPENquery in the select statement , what kind of performance does the second server have , are the two machines on the same network?

have you tried running the two parts of the query one by one , the inner table looks like its fecthing .5 M rows how long does it take on its own?

Jayanth Kurup[/url]

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 6

Piling on Luis's and Gail's posts, withholding or piecemeal'ing any relevant information is a waste of everybody's time! From the limited information supplied so far, one question arises, how are the indices and the statistics maintained? By the looks of it the statistics are stale, mind you that with out the full info this is just a wildcard guess, but seeing the estimate of 55 rows and actual number of rows roughly 1100 times greater is a hint.

😎

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Eirikur Eiriksson (8/2/2015)
Piling on Luis's and Gail's posts, withholding or piecemeal'ing any relevant information is a waste of everybody's time! From the limited information supplied so far, one question arises, how are the indices and the statistics maintained? By the looks of it the statistics are stale, mind you that with out the full info this is just a wildcard guess, but seeing the estimate of 55 rows and actual number of rows roughly 1100 times greater is a hint.
😎

Eirikur, did you confused the estimated I/o cost with the estimated rows?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 8

Luis Cazares (8/2/2015)
Eirikur Eiriksson (8/2/2015)
Piling on Luis's and Gail's posts, withholding or piecemeal'ing any relevant information is a waste of everybody's time! From the limited information supplied so far, one question arises, how are the indices and the statistics maintained? By the looks of it the statistics are stale, mind you that with out the full info this is just a wildcard guess, but seeing the estimate of 55 rows and actual number of rows roughly 1100 times greater is a hint.
😎
Eirikur, did you confused the estimated I/o cost with the estimated rows?

:blush: Spot on Luis, my bad

😎