March 18, 2011 at 8:25 am
I have an SP that runs in the neighborhood of 200ms on both our dev and staging servers but runs between 4000 and 6000ms on the production server. The production server is newer and much more robust than the dev and staging servers. What should I be looking for? That is, what should the smart people here be looking for? I am a competent query writer but when it comes to the environment, I haven't a clue.
March 18, 2011 at 8:31 am
Often teh difference between environments such as dev of production is simply traffic. On the test box your query likely has to contend with very few other reuests. While on production you are likely sharing server time with a large group of people. I would take a look at the production box and see what other activity may be going on.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 18, 2011 at 8:47 am
We have actually had this running on a smaller scale for over a year in production, just now when it is being used in multiple places is there a noticable lag. You are right in that there is a lot of traffic on the server, but this is a problem at all hours of day and night even when little else is going on. Is there some sort of locking or something we can employ that will tell the rest of the traffic to just shut up while this runs? Is 200ms too much to ask?
March 18, 2011 at 1:50 pm
Some things to look at:
networking: is prod on a different network than dev? What's the configuration and usage for each
server setup: how do the servers differ on the physical and windows side? Are they physical/vm? What other apps are running on the servers? What's their CPU?RAM?etc.
compare sp_configure for both (with advanced options on) compare Windows privileges for both
disk space/type/setup/maintenance: how do they differ?
compare the *actual* query plans on both servers. Are there different statistics, etc.?
March 18, 2011 at 3:53 pm
I agree with Pam, first place to look is the actual execution plans. Are they different, if so, what are the differences?
Another option to check, what are the ANSI connection settings on the two servers. These can lead to differences in execution plans.
"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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply