January 27, 2011 at 2:00 pm
i have a procedure. In production it runs in 9 secs and in development completes in 2 secs. Mentioned below are my finding.Development has recent copy of production
i) Same CPU,I/O usage for the procedure. I captured using trace and data is same
ii) ran SET I/O stats...same output
iii) execution plan is also exactly the same
why is production slow then? what else do i need to look? i do not think this is indexes issues since i just got copy of prod..Please advice
January 27, 2011 at 2:48 pm
Check for contention, blocking, that would make prod run longer than dev. How about the physical machines themselves? I ran into situation once where I spent at least a day troubleshooting a situation where production was half the speed of dev. Turns out the production server's CPU's were half as fast as the dev box.
You say you're comparing execution plans, but are you comparing estimated or actual plans. The estimated can be the same and the actual can be different.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 27, 2011 at 3:01 pm
Grant Fritchey (1/27/2011)
Check for contention, blocking, that would make prod run longer than dev. How about the physical machines themselves? I ran into situation once where I spent at least a day troubleshooting a situation where production was half the speed of dev. Turns out the production server's CPU's were half as fast as the dev box.You say you're comparing execution plans, but are you comparing estimated or actual plans. The estimated can be the same and the actual can be different.
I am sure there is no blocking but i can check that. I am comparing actual exec plans. I am measuring memory usage using SET IO STATISTICS ON..please let me know if this is right..im so freaked out..dont know what to do.:hehe:
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply