Same SQL statement executed with Different duration

Question

Same SQL statement executed with Different duration

Mayur-1130810

Old Hand

Points: 396
More actions
November 16, 2009 at 2:38 am

#137273

Hi,
I have one stand alone application which is using 'SQL Server 2005' as database.
I have some tables containing records around 50,000. Those tables are executed using Store procedures and Views.
In Database, Every time same store procedure takes different duration to execute (I have tracked time using SQL server profiler.). Several times same store procedure executed within 1 second or some time it takes 2 to 3 second.
It is impacting application performance.
Can anyone tell me what could be reason behind different execution time each time for same store procedure? How could I improve performance of database?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

You haven't really given us much information to work with, so I'll guess:

In broad terms, SQL Server stores a query plan for the whole stored procedure. That plan will be tuned according to the values passed to the procedure as parameters the first time it is executed. If those particular parameter values are not typical, the server might store a plan which doesn't work as well when executed with different parameters. Google 'SQL Server parameter sniffing' more more information.

Alternatively, post some more details to get better answers.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Mayur-1130810 Old Hand Points: 396 More actions · Answer 2

Hi Paul,

Thank you,

Actully i am facing this problem to all store procedue(Same store procedure with different parameters value), 'Select' statement and view.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Without seeing the code not much we can do to help. It is possible that you are experiencing parameter sniffing.

Please read the first two articles I reference in my signature block below. Following the instructions in those articles will help you get much better help.

Mayur-1130810 Old Hand Points: 396 More actions · Answer 4

Mayur-1130810

Old Hand

Points: 396

November 17, 2009 at 1:44 am

#1080089

Thanks Lynn

Paul Swinfield SSC-Addicted Points: 488 More actions · Answer 5

Its a massive subject, places to look are the estimated and the actual query plans, use profiler to see actually whats running on SQL, use perfmon to look at the server and see if anythings slowing down - cpu, buffer io, memory etc, use sp_who2 to see if there are locks knocking about, make sure the stats and indexes are up to date (make sure the tipping point of the index isn't being compromised). If you are using 2005 use the Performance Management download from MS. It contains a whole load of reports and stuff to have a look at. And dont forget the basics, well written SQL, simply coded looking at tables with minimal complexity will always perform better and more consistently than monsters!