Different performance on Test and production

Question

Different performance on Test and production

Fernando-235287

SSCrazy

Points: 2485
More actions
July 29, 2008 at 12:33 pm

#210600

Hello to all. I received a very complex query from one of the developers that needed help due to performance. I did fix the issues in test. The query was running before in 5:45. After the tuning I did it came to less than one second. So far so good
Then, we moved the application to production and the query is running in 39 seconds. All the tuning I did in test was done also in production prior to move the app.
Test box has only 1.5 of RAM and one cpu, the production box has 8 cpu's and 4 GB of memory, What could it be? I thought maybe the SAN is not configure correctly. Not the case, I place a copy of the database to a local drive and the time was the same. I moved the copy to a different production box and the same time still (39 seconds)
I am running out of ideas, any help will be very much appreciated. The code in the query is very crappy anyway, but why in test it run in 1 second or less and production in 39?????? HELP!!! :hehe:

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Less activity on the test server. Is your query having to wait for locks? Wait for IO? Wait for time on the CPU? All are possible and more likely on a busy production server than a mostly idle test server

Compare the execution plans and see if they are the same across the two servers.

Run the query on production and check sysprocesses and see if the query is waiting and if so, for what.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Fernando-235287 SSCrazy Points: 2485 More actions · Answer 2

Thanks, all done already. Production server has 8 CPU's and 4 GB of RAM, maybe add more memory and enable AWE Running sp_who2 and perform mmc, I am not getting anything that indicates the waits are a big issue. Yes, prod is more busy but not to the point of making such a difference in time to run the query. :hehe:

Jeffrey Williams SSC Guru Points: 90291 More actions · Answer 3

Do you possibly have more data in live than in the test system? Are the statistics up to date on all tables involved in this query?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Fernando-235287 SSCrazy Points: 2485 More actions · Answer 4

Thanks, I refresh test to have the most current data (difference is just from 11:30 AM to now)

The statistics were the first step to optimize the query. Indexes were also created in a couple of tables. What really bothers meis that test run in one second or less after optimization and production 39 seconds. I will say, if the difference were a few seconds between each other. I could say is fine, but such a difference? If I run the same statement in the morning with no one in the system it go to 5+ minutes!!!!! Crazy ha? I run out of ideas

Jeffrey Williams SSC Guru Points: 90291 More actions · Answer 5

Fernando (7/29/2008)
Thanks, I refresh test to have the most current data (difference is just from 11:30 AM to now)
The statistics were the first step to optimize the query. Indexes were also created in a couple of tables. What really bothers meis that test run in one second or less after optimization and production 39 seconds. I will say, if the difference were a few seconds between each other. I could say is fine, but such a difference? If I run the same statement in the morning with no one in the system it go to 5+ minutes!!!!! Crazy ha? I run out of ideas

Just for the sake of argument, try adding OPTION(MAXDOP 1) to the query in production. You might be running into a parallel plan issue for this query.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Fernando-235287 SSCrazy Points: 2485 More actions · Answer 6

Thank you. That is something I haven't check or done. Will try tomorrow. Thanks

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 7

You may want to try rebuilding the indexes which will also update the associated stats.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 8

I had this same issue a few years ago, and eventually traced it to the fact that the 2 boxes had different amounts of memory, which for some reason caused SQL Server to generate a different query plan.

Note. I couldn't believe that this was the case, so I confirmed it by running tests with different amounts of memory allocated to the SQL instance on the dev box, and at a certain point, the query plan changed.

I wanted to study the issue a bit deeper, but time constraints meant the quickest solution was to add query hints to make sure the production box used the same plan as development.

Fernando-235287 SSCrazy Points: 2485 More actions · Answer 9

Thank you to everyone for their input. Rebuild the indexes is a maintenance tasks and it runs weekly. Memory difference? That might something else. I have 1.5 of RAM in test and 4 GB in production and the settings on box servers are as recommended by Microsoft as best practices.

The one item I didn't check carefully was the level of parallelism in the statement. This one did the trick. I added OPTION (MAXDOP 1) and it worked perfectly. Thank you gain to all for their input. Each item was read carefully and researched or checked.

:hehe: :hehe:

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Fernando (7/30/2008)
Thank you to everyone for their input. Rebuild the indexes is a maintenance tasks and it runs weekly. Memory difference? That might something else. I have 1.5 of RAM in test and 4 GB in production and the settings on box servers are as recommended by Microsoft as best practices.
The one item I didn't check carefully was the level of parallelism in the statement. This one did the trick. I added OPTION (MAXDOP 1) and it worked perfectly. Thank you gain to all for their input. Each item was read carefully and researched or checked.
:hehe: :hehe:

Yes, this sounds like the 64-bit 2xQuad core bugaboo that has bedeviled many a production site. There are many posts on SQLServerCentral.com that turn out to be due to this.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]