Post reply

DROP CLEANBUFFERS and Performance improvement

  • April 15, 2009 at 6:50 am

    #384359

    Hi All,

    My Environment

    SQL server 2005 Enterprise Edition with Service Pack 3

    4 CPU , 16 GB RAM .SQL Server Max memory setting at 12 GB

    Other Information

    SNAPSHOT_ISOLATION is ON

    READ_COMMITTED_SNAPSHOT is ON

    Issue :

    We are running a Performance test and the performance of the transaction is good until 5 hours into the run .After 5 hours of run the performance gradually creep up to an unacceptable state. But if I clean the Buffer cache using DROP CLEANBUFFERS initially the performance degrades ( undetectable because no cached information on the memory) ,but after few minutes into the test the performance is back to an acceptable state.

    Anyone can explain why the DROP CLEABUFFERS did the trick , and any other solution to this problem ?

    Regards

    YeePee

  • April 16, 2009 at 8:12 am

    #978876

    Sounds like pressure on the memory. Were you capturing performance counters. Do you have Page Life Expectancy? That's a good measure of what's happening with SQL Server memory management.

    "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

  • April 16, 2009 at 1:04 pm

    #979139

    I think I know what the Problem is . It is the TokenAndPermUserStore cache store growth problem. There is a fix from Microsoft but it says it is being fixed on the latest Service pack . We do have SP3 Installed ,but till we have the issue.

    I may open a PSS ticket for this . But if I run DBCC FREESYSTEMCACHE on a Schedule ( Currently at 15 min ) we do not have the Memory issue.

    If you suspect you have the issue ,run the following SQL

    SELECT SUM(single_pages_kb + multi_pages_kb) AS

    "CurrentSizeOfTokenCache(kb)"

    FROM sys.dm_os_memory_clerks

    WHERE name = 'TokenAndPermUserStore'

    If the reseult set Keeps growing you can suspect this the cause.The number is in KB so something to look into if you have memory issue.

    Here is the MSOFT article

    http://support.microsoft.com/default.aspx/kb/927396

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

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