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


Same SQL statement executed with Different duration


Same SQL statement executed with Different duration

Author
Message
Mayur-1130810
Mayur-1130810
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 24
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?
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
Mayur-1130810
Mayur-1130810
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 24
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
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24185 Visits: 37953
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Mayur-1130810
Mayur-1130810
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 24
Thanks Lynn
Paul Swinfield
Paul Swinfield
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 178
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!
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