March 18, 2010 at 6:04 am
I have a sql stored procedure that i developed that runs 3x's longer in production than it did on the development server. The proc builds sql local temp tables, loads and manipulates data, then writes the temp tables out to permanent tables.
A couple of notes:
- The dev server and the production server are nearly identical hardware (dev has one quad-core processor, prod has two, other than that, they are identical).
- Both servers have networked drives, which are a part of the same SAN (raid 10).
- SQL configuration on both servers is the same (production is running 2005 enterprise, dev is running 2005 standard).
I've run SET STATISTICS PROFILE ON, the execution plan is the same on both servers.
The IO reads from production\dev are below.
Production has more way more physical reads that development on the local temp table (hence the tempdb).
What would cause this? Any ideas on how to fix it?
Production:
Step 2 - Table '#CustomerAdditionalFields
___________________________________________________________________________________________00000000F875'. Scan count 1, logical reads 28117420, physical reads 4902, read-ahead reads 9445, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Step 3 - Table '#CustomerAdditionalFields___________________________________________________________________________________________00000000F875'. Scan count 1, logical reads 5887804, physical reads 25245, read-ahead reads 39489, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Development:
Step 2 - Table '#CustomerAdditionalFields___________________________________________________________________________________________000000003A50'. Scan count 1, logical reads 27982634, physical reads 110, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Step 3 - Table '#CustomerAdditionalFields___________________________________________________________________________________________000000003A50'. Scan count 1, logical reads 4679022, physical reads 2083, read-ahead reads 17289, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
March 18, 2010 at 6:53 am
Is the dev environment reflecting the prod environment?
Meens, do you testing on dev with the same number of records?
March 18, 2010 at 7:06 am
Yes, dev databases were restored from prod backups.
March 18, 2010 at 9:22 am
My guess is that the production server is more heavily loaded than the test system.
The might mean that SQL server is not able to use as much memory for caching in production as it does in test.
Adding more memory to the production server might help.
March 19, 2010 at 9:09 am
Another contributing factor is that the read-ahead manager in SQL Server Enterprise is more aggressive than in Standard Edition, and can also issue large I/O - up to 1024KB (Standard is limited to 256KB).
Clearing the buffer pool on the development server only using CHECKPONT; DBCC DROPCLEANBUFFERS; will remove the effects of having some data already in cache.
Something about the general data in your post leads me to think that a more efficient implementation is possible. Difficult to be sure without full graphical actual execution plans, table and index definitions and so on.
On another tack, is the process not a good candidate for an SSIS package?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 19, 2010 at 4:34 pm
I would like to see the actual execution plans from both servers and see if they differ at all.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 19, 2010 at 9:42 pm
Jason,
Kevin Bullen (3/18/2010)
I've run SET STATISTICS PROFILE ON, the execution plan is the same on both servers.
Seems to be, but I agree it would be interesting to see the plan - not for differences necessarily, but to see if obvious improvements might be made.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 20, 2010 at 12:07 am
Paul White NZ (3/19/2010)
Jason,Kevin Bullen (3/18/2010)
I've run SET STATISTICS PROFILE ON, the execution plan is the same on both servers.Seems to be, but I agree it would be interesting to see the plan - not for differences necessarily, but to see if obvious improvements might be made.
Agreed on the improvements. It just seems that there is always a difference in the plan though when the execution is so different.
We'll see once we get the plans.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 20, 2010 at 8:19 am
Thanks for all of the replies.
To summerize responses to all of the posts:
- The production server has little activity outside of the 3AM to 7AM hours. I'm running this job outside of that timeframe. Production has an additional quad-core processor, and all of the networked drives are located on an EMC SAN with RAID 10. I'm going to rule out activity at this point.
- I ran the job after executing a DBCC FREECACHE() in dev, the job still ran in four hours.
- I've tried the CHECKPONT; DBCC DROPCLEANBUFFERS and running the job in dev, and it cost me 30 minutes (so the job ran in 4 hours 30 minutes vs 4 hours).
- In production our tempdb has one file and is set to autogrow @ 1 pct. I've tried changing dev to the same, no change, dev still ran in 4 hours. I also changed dev to 10pct autogrowth and added an addtional file (based on a Microsoft recommendation that you have up to 1 file \ processor) and no gain. Maybe because of the SAN \ raid 10 there is nothing to be gained here?
- I am running the job in production today with statistics profile & io on, so i can post the execution plan and io stats for both prod and dev later today\tomorrow. From earlier runs, the execution plans where the same, but let me get that in a format that I can post.
- Are there efficiencies to gained, yep, I've already come to that conclusion, but at this point, if I can get prod to run as fast as dev, that will buy me some time
Thanks again for your input.
Kevin
March 20, 2010 at 9:28 am
What does the transaction log for the database look like?
How big?
What is the autogrowth on it?
How many vlfs are in it?
Compare the t-log for both tempdb and the user database for this process.
For info on vlfs, check the link to the article by Kimberly Tripp in my sig.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 20, 2010 at 9:41 am
Kevin Bullen (3/20/2010)
I am running the job in production today with statistics profile & io on, so i can post the execution plan and io stats for both prod and dev later today\tomorrow.
Kevin,
Graphical plans produced from a real execution are much more useful. Just right-click the graphical plan and save it as a *.sqlplan file.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 22, 2010 at 6:32 am
Below is a link to the execution plans & io stats for both dev and prod (as well as temp table definitions and the statement that is at the root of the problem.).
http://rapidshare.com/files/366676020/WeekendRunComp.txt
From what I can tell, the execution plans appear to be identical. The only difference I can see is that there are way more physical reads in production.
Thanks,
Kevin
March 22, 2010 at 10:16 am
Kevin Bullen (3/22/2010)
Below is a link to the execution plans & io stats for both dev and prod (as well as temp table definitions and the statement that is at the root of the problem.).http://rapidshare.com/files/366676020/WeekendRunComp.txt
From what I can tell, the execution plans appear to be identical. The only difference I can see is that there are way more physical reads in production.
Thanks,
Kevin
Please attach the graphical execution plans (for the actual execution plan) to the thread.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 22, 2010 at 11:08 am
query is running right now, when it finishes tonight, i will post the graphical exec plan.
March 22, 2010 at 11:12 am
Thanks
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply