query runs fine on dev but very slow on production

Question

query runs fine on dev but very slow on production

iceheartjade

Say Hey Kid

Points: 677
More actions
May 14, 2008 at 2:40 pm

#95933

Hi,
One of my store proc takes 15 min to run on dev, but when I moved to production server (better and faster machine), it takes 11 hours to finish. Can anyone think of anything (server setting/configuration....) might lead to this? I do have cursor in the store proc, but since it only takes 15 min to run I didn't bother to change that.
Thanks
ice

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jack Corbett SSC Guru Points: 184388 More actions · Answer 1

Well, if there is a cursor I would always try to remove it if there is a set-based solution which there usually is. As far as the why there is a difference in DEV and production there could be several things here are a few things to check:

1. Does DEV have as much and the same data as Production?

2. What else is happening on Production when this processes is running? On DEV machines many times you are the only process running.

3. What are the Execution plans on the 2 machines?

4. Are the indexes the same on both machines?

5. When were the statistics last updated on the Production machine?

6. How much memory on the production machine? Could be the data is in the cache in DEV, but not in production?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

iceheartjade Say Hey Kid Points: 677 More actions · Answer 2

Jack Corbett (5/14/2008)
Well, if there is a cursor I would always try to remove it if there is a set-based solution which there usually is. As far as the why there is a difference in DEV and production there could be several things here are a few things to check:
1. Does DEV have as much and the same data as Production?
2. What else is happening on Production when this processes is running? On DEV machines many times you are the only process running.
3. What are the Execution plans on the 2 machines?
4. Are the indexes the same on both machines?
5. When were the statistics last updated on the Production machine?
6. How much memory on the production machine? Could be the data is in the cache in DEV, but not in production?

1. yes, the dev and production has same data

2. nothing else is happening on production since it's a new server and this is the first job that port over

3. should be the same. I backup the dev db and restore on production so all the objects should be the same. plus the store procedure is dropping the table and recreate every time, so should be fresh table every time

4. indexes are same

5. tables are dropped and recreated

6. production server have more memory than dev. if the table is recreated, is there going to be data in cache?

Thanks

ice

Jack Corbett SSC Guru Points: 184388 More actions · Answer 3

Even with the backup and restore the execution plans could be different as the plans are cached in memory not in the database, so you should compare execution plans.

Are the drives the same?

Can you post the code?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jared Hunt Mr or Mrs. 500 Points: 507 More actions · Answer 4

Maybe the test DB is set to Simple recovery and production is set to Full. I doubt this would cause a 10.75 hour difference in execution though. It sounds like a configuration problem, but can't think of anything that would make THAT big of a difference. The only other thing may be disk contention if there is a lot of worktable activity in tempdb.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

Simple vs. Full recovery will not make a difference. Both actually log transactions, Simple just truncates the log at every checkpoint.

You need to look at the execution plans. "Should" be the same does not mean much. A minor difference in the hardware can make a huge difference to SQL. You should look at the plans.

Also look at the parallelism happening on each machine. Parallel processing can slow things down if your query is written wrong. I once saw a query work fine on a single processor machine and end up horribly slow on a multi-processor machine because the dope that wrote it re-used an alias name in a correlated sub-query.

Avishkar Meshram SSC-Addicted Points: 414 More actions · Answer 6

1. Remove Cursor and use Table variable or Temporary tables if you can.

2. Check Server settings. e.g Disable Hypertreading.

http://technet.microsoft.com/en-us/magazine/cc137784.aspx

3. Monitor Windows parameters .

http://www.windowsnetworking.com/articles_tutorials/Key-Performance-Monitor-Counters.html

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

Does the production machine use VM by any chance? Does it have auto-shrink turned on, especially on TempDB by any chance?

--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)
Intro to Tally Tables and Functions