Statistics IO - Physical Reads in Prod vs Dev

  • 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.

  • Is the dev environment reflecting the prod environment?

    Meens, do you testing on dev with the same number of records?

  • Yes, dev databases were restored from prod backups.

  • 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.

  • 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?

  • 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

  • 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 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • query is running right now, when it finishes tonight, i will post the graphical exec plan.

  • 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