SP Perfomance Issue

  • Hi experts

    I have two machines running SQL Server 2008R2 Developer Edition. I have an sp that takes 26 seconds to run on one machine, and nearly 10 minutes on the other.

    DBs are identical (one is a copy of the other via restoring of a backup). Indexes are the same and stats updated on both.

    Similar set up in terms of default SQL Server installation options, disk config, placement of system DBs and user DBs. Essentially, the two machines look the same with the exception of one having 8GB ram and the other 4GB ram (no memory pressure though)

    Using SET STATISTICS IO ON shows me that on one machine (taking 10mins) there is nearly double the amount of logical reads, and interestingly on that machine there is nothing in the proc cache for that sp.

    When I run profiler I can see that, down to the sp:stmt level there are literally more reads for the exact same stmts.

    I don't understand this behaviour and am running out of options to explore. Any help would be grately appreciated.

    Thanks in advance...

  • Statements and execution plans?

    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
  • Agree with Gail.

    Look to the default ANSI settings on both servers, max degree of parallelism and the cost threshold for parallelism. Deviations in these settings 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

  • Thanks for the response....it turns out the devil was in the detail and the sql server versions were not exactly the same. One was RTM and the other SP1. I've updated the RTM version and now executions of the said SP are performing much better. Although im still baffled by the difference in reads and lack of plan caching which was happening but is now fixed.

    Thanks again 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

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