March 12, 2009 at 8:48 pm
I am running the following before I call my store procs for testing
use [database]
go
checkpoint
go
dbcc dropcleanbuffers
go
dbcc freeproccache
go
then exec store proc
After I drop my store proc and create it and then run the above the first time is always slow then all the subsequent times are somewhat faster I want to know if my store proc and table(s) are fine tuned is the first run an accurate run?
Thanks
March 12, 2009 at 10:02 pm
If you are running those DBCC you probably know the first time it is running slow because it is creating the execution plan for the store procs. I Haven't seen need to do that; instead I would look at the execution plan your store proc generates and fine tune that.
For example if your store proc returns 50 records 20 min that is bad doesn't matter how you do it; you need to fix your store proc.
Any how that be my 2 cents ...
Mohit.
I know nothing ...
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 17, 2009 at 4:04 am
The first run is a 'worst case' run. As has already been mentioned, your proc is being compiled. Your data also has to be loaded into the buffer. Physical I/O takes time, as does compilation.
Running those DBCC commands gives you a common baseline for testing multiple solutions.
SQL guy and Houston Magician
March 17, 2009 at 7:32 am
When I'm performance testing a stored proc, the first thing I do is rebuild all indexes and update statistics. (I'll assume you can do this in a non-production environment.)
Then I use the SET STATISTICS IO command to tune the stored proc. As a general rule of thumb, the lower the number of reads, the faster your t-sql will run.
Here's a good article that discusses SET STATISTICS IO and SET STATISTICS TIME:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply