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

SQL 2005 - Long duration Stored Procedure Expand / Collapse
Author
Message
Posted Friday, March 19, 2010 11:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:30 AM
Points: 403, Visits: 982
I use a Stored Procedure (with parameter) which uses 3/4 joins to retrieve some fields.i.e., a select from 3/4 tables. The problem is whenever the SP is executed for a particular input for the first time, it takes 5 to 6 mins to retrieve records(1 to 100). But the same SP for the same parameter after that completes in a shorter duration. If the parameter is changed, then it takes similarly long time, but the second execution for the same parameter in shorter duration.

Execution plan does not have any table scan.
There are indexes present in all the tables.
recompiling SP, Freeing cache does not have desired effect.
SQL 2005

Any recommendations?
Post #886593
Posted Friday, March 19, 2010 12:01 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:59 AM
Points: 17,822, Visits: 15,747
balasach82 (3/19/2010)
I use a Stored Procedure (with parameter) which uses 3/4 joins to retrieve some fields.i.e., a select from 3/4 tables. The problem is whenever the SP is executed for a particular input for the first time, it takes 5 to 6 mins to retrieve records(1 to 100). But the same SP for the same parameter after that completes in a shorter duration. If the parameter is changed, then it takes similarly long time, but the second execution for the same parameter in shorter duration.

Execution plan does not have any table scan.
There are indexes present in all the tables.
recompiling SP, Freeing cache does not have desired effect.
SQL 2005

Any recommendations?


Freeing cache will make it slower on the next run as well.

Do you have appropriate indexes?

Have you checked for parameter sniffing?

how do the statistics appear for your tables?

How is the index fragmentation level?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #886626
Posted Friday, March 19, 2010 2:51 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices--Parameter-Sniffing.htm

MJ
Post #886731
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse