Different Execution time for Same SQL Query

Question

2.60/5 (2 votes)

See more:

SQL-Server-2008

Hello,

I am executing a stored procedure on my machine in SQL Management studio and database is located on another machine(just ans ordinary machine which has 3GB RAM, not a server)
Stored procedure works properly, but every time it takes different time to execute even if all parameters are the same. Execution time varies from 1 minutes to 8 minute. I am not able to find reason for this and i am not using any DBCC command in between.

Though i understand that memory/CPU availability of server plays a role here, i am still not able understand the reason.

Please share your thoughts on this

Thanks

Posted 6-Jun-13 19:53pm

Amol_B

Add a Solution

Comments

Sergey Alexandrovich Kryukov 7-Jun-13 1:58am

This is not a real-time system to guarantee some maximum latency. Results depends on current operating environment status, a combination of several pseudo-random factors. And the network is also random and not real-time, by its very design.
—SA

Amol_B 7-Jun-13 2:05am

Thanks Sergey.
I tried to keep CPU Usage and other things similar.
I understand that plan cache reuse plays important part here but still there is lot of variation in execution time

Sergey Alexandrovich Kryukov 7-Jun-13 10:00am

Good. Anyway, I wish you a good optimization of your solution throughput.
—SA

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-06-07T06:01:00

I do not know the exact answer, but there are several ways to check the performance of query(ies) and to optimize it:
T-SQL Tuning: How to Measure Performance Improvements (with Video)[^]
7 performance tips for faster SQL queries[^]
How To: Optimize SQL Queries[^]
SQL Tuning or SQL Optimization[^]
and my best: The Seven Sins against TSQL Performance[^]
Please, read very interesting article from CodeProject Knowledge Base:
Top 10 steps to optimize data access in SQL Server: Part I (use indexing)[^]
Top 10 steps to optimize data access in SQL Server: Part II (Re-factor TSQL and apply best practices)[^]
Top 10 steps to optimize data access in SQL Server: Part III (Apply advanced indexing and denormalization)[^]

My conclusion: use DBCC tools/commands ;)