Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


procedure started taking longer time to retrieve data in last two days


procedure started taking longer time to retrieve data in last two days

Author
Message
Ajdba
Ajdba
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 395
Hello,
I was running a procedure to retrieve data which usually took 14-20 seconds but in last two days it started taking more than a minute which is not acceptable timing. I have checked all the indexes and network and nothing has changed to my knowledge. Is there anything else as far as the database wise that I need to look into such as rebuild indexes and so forth. I would appreciate any recommendations.

Thanks
Aj
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6100 Visits: 6078
Could you post the execution plan for the procedure?

Have you checked index fragmentation and statistics to ensure they are defragmented and updated.

If stuck please follow this link http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14958 Visits: 38972
without the details my friend anthony is asking for, i'd WAG and say to update statistics on the underlying tables the procedure uses.

out of date statistics are associated witht eh symptom you describe: performance of an existing proc slowly degrades.

parameter sniffing could also be an issue...more details might be needed, as anthony suggested.


UPDATE STATISTICS dbo.[Table1] WITH FULLSCAN ;
UPDATE STATISTICS dbo.[Table2] WITH FULLSCAN ;




Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ajdba
Ajdba
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 395
Even after I did reorganized index and updated statistics on the table that is heavily used by the view , I don't see any performance improvement. I am attaching my execution plan.
Please let me know if you see anything else that I could do.

Thanks
Ajdba
Ajdba
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 395
Ooops sorry, forgot to attach the file....
Attachments
execPlan0802.sqlplan (31 views, 5.00 MB)
Ajdba
Ajdba
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 395
I am also attaching the index fragmentation percentage on the db
Attachments
gearsIndexFragmentation.xlsx (1 view, 16.00 KB)
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14958 Visits: 38972
well there you go!
i see a couple of things;


the plan shows two cursors doing stuff to at least 580 rows;
those cursors can be eliminated and replaced witha set based operation to do the same work in a single step.

want to post the procedure body for some peer review?

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ajdba
Ajdba
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 395
Attached the proc for your review; please let me know how I can replace the cursors to set based operations.

Thnks
Attachments
garsProc.docx (21 views, 55.00 KB)
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14958 Visits: 38972
aww,at 1000+ lines of code, without the underlying base tables and linked server(EDWGEARS),
that one is a little bigger scope than i can help with as a volunteer;

it would require substantial testing, i think.

the key, however is to remove the openquery stuff, query the linked server with direct commands, as a set based operation, instead of all those cursors.

you might get rid of the temp tables and replace them with teh direct CTE's as well;

this would be beyond the scope of how i volunteer here, sorry.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ajdba
Ajdba
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 395
Thanks for trying at least Lowell
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search