SQL Server Performance

After SQL Server Restart - SP Performance Slow

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Vivek Hari, May 29, 2009.

  1. Vivek Hari New Member

    We are facing a problem in a SP. We converted a dynamic SP into a static SP. Static SP is taking long time to stabilize after SQL Server restart. Let’s say 10-12 hours to give consistent performance. After stablization, the SP is performing really good atleast 40% better performance than the old but during the stabilization period the response time spiking 50% more than the old SP. No.s of hits : ~4000/ 4 hrs. Data Volume: Ranges from 5 to 2.5 Lakh records depending on the call
    Static Vs Dynamic SP - Key changes
    1. In Dynamic SP, all the record fetches are done in a single query using correlated subquery concept. In new SP, 2 Temp Tables & 4 Table Variables are used for loading the intermediate results with indexes and those data are used for the final query fetch
    2. To avoid re-compilations due to the temporary table, Query hint (KEEP PLAN & KEEPFIXED PLAN) are used

    Actions Taken (High Level)
    1. We suspected cache building is the factor which is taking time to stabilize. So we cleared all the cache(procedure & data) and ran the queries. Even after cache clear, SPs are performing better. The problem exists only after the SQL Server restart.
    2. TempDB also watched on periodic basis during run and it didn’t show any significant changes
    3. Perfmon counters : Physical Read/Writes, Buffer/Cache hit ratio all are watched and it looks same for Old & New SP
    Did anyone faced similar kind of issue and do you have any suggestion to approach this problem.
  2. Adriaan New Member

    No point in replacing correlated subqueries with temp tables - creating the temp tables may well be costly.
    Why not use dynamic SQL, but then properly - sp_ExecuteSQL with parameters. Make sure you use owner.objectname format, and aliases, and prefix each reference to a column with the correct alias. This improves your chances of execution plans getting re-used.
  3. moh_hassan20 New Member

    Compare the execution plan for both solution ,use set statistics IO /Time ON to find the difference
    I agree with Adrian sp_executesql with parameters is better.
  4. satya Moderator

    How often you are restarting SQL Server services?
    In general to build up the plan in cache it will take sometime after the restart of services, in this case the same formula applied every time. In this case run sys.dm_os_wait_stats dynamic management view, and notice the values of the wait_type column.
    Also do you have REPORTING SERVICES in this environment?
    Because such problem occurs the loading of assemblies for .NET after the startup of SSRS & SQL services.

Share This Page