Post reply

RCSI

  • November 26, 2007 at 1:45 pm

    #754242

    you should take a HARD look at the index usage of your queries.

    STATS UPDATES with FULLSCAN is mandatory (it has been my experience)


    * Noel

  • November 26, 2007 at 1:49 pm

    #754245

    I will definitely try to run UPDATE STATISTICS With Fullscan. I had a quick question, this is the second time somebody mentioned to run the following 2 commands.

    1. DBCC DROPCLEANBUFFERS

    2. DBCC FREEPROCCACHE

    Does the buffers and cache refresh when the database gets recycled?

  • November 26, 2007 at 2:00 pm

    #754253

    When SQL gets restarted? None of the caches are persisted to disk so all are empty when SQL starts up.

    I wouldn't recommend doing either on a production server, certainly not on a regular basis. Especially for the data cache, the cost of repopulating it is not low.

    For the proc cache, it's usually better to remove single non-optimal plans using sp_recompile. Emptying the procedure cache usually results in higher CPU usage for some time after as all the queries get recompiled before running.

    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

  • November 26, 2007 at 2:09 pm

    #754258

    The recompilation can be triggered by a ton of factors one of them is updating statistics. If you update stats the plans depending on the updated table will be recompiled.


    * Noel

  • November 26, 2007 at 2:40 pm

    #754275

    We did inplace upgrade to sql server 2005 and some how execution plans are corrupted in memory. That's reason i ran the foolowing commands

    1. DBCC DROPCLEANBUFFERS

    2. DBCC FREEPROCCACHE

    After upgrade you should run UPDATE STATISTICS With Fullscan

  • December 5, 2007 at 7:24 am

    #757326

    Steven Kwong,

    your issue resolved?

  • December 5, 2007 at 9:22 am

    #757375

    Thanks for everyone's responses. What I failed to include in my original post is that we are running SQL Server 2005 on an Egenera Bladeframe environment which is suppose to maximize throughput. Unfortunately, the bladeframe has issues handling TCP\IP calls. I cut over the SQL Servers to strictly use Name Pipes. Everything is running 200% better! Very strange. The Peoplesoft team here did not feel comfortable clearing cache and buffers and the update stats on all tables with fullscan took 1 day in our testing environment.

    Thanks everyone for the responses. It was tremendously helpful.

Viewing 7 posts - 16 through 21 (of 21 total)

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