Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

procedure started taking longer time to retrieve data in last two days Expand / Collapse
Author
Message
Posted Thursday, August 2, 2012 8:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 3:35 PM
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
Post #1339226
Posted Thursday, August 2, 2012 8:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:20 AM
Points: 5,231, Visits: 5,106
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
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

Post #1339234
Posted Thursday, August 2, 2012 8:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 12,917, Visits: 32,083
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1339236
Posted Thursday, August 2, 2012 9:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 3:35 PM
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
Post #1339295
Posted Thursday, August 2, 2012 9:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 3:35 PM
Points: 73, Visits: 395
Ooops sorry, forgot to attach the file....

  Post Attachments 
execPlan0802.sqlplan (31 views, 5.76 MB)
Post #1339298
Posted Thursday, August 2, 2012 9:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 3:35 PM
Points: 73, Visits: 395
I am also attaching the index fragmentation percentage on the db

  Post Attachments 
gearsIndexFragmentation.xlsx (1 view, 16.37 KB)
Post #1339312
Posted Thursday, August 2, 2012 9:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 12,917, Visits: 32,083
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1339326
Posted Thursday, August 2, 2012 10:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 3:35 PM
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


  Post Attachments 
garsProc.docx (21 views, 55.41 KB)
Post #1339339
Posted Thursday, August 2, 2012 10:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 12,917, Visits: 32,083
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1339356
Posted Thursday, August 2, 2012 10:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 3:35 PM
Points: 73, Visits: 395
Thanks for trying at least Lowell
Post #1339358
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse