Post reply

How to cheat DBCC DROPCLEANBUFFERS for Performance testing

  • July 9, 2014 at 12:48 pm

    #287797

    Hi,

    I'm tuning big query so I'm in need to check clean execution time, but I don't have authority to run DBCC DROPCLEANBUFFERS on shared server..

    What to do?

    Can I just insert some dummy fields to make new plan generated? or any modification to query will do it?

    Or some kind of other command exists to take care of it on lower level.

    Thanks

    mario

  • July 9, 2014 at 2:42 pm

    #1728218

    DropCleanBuffers has nothing to do with the plan. It clears the data cache. You don't need to run it for performance testing, just run queries twice or more and ignore the first one.

    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

  • July 9, 2014 at 5:53 pm

    #1728242

    Thansk GilaMonster,

    I meant that when you use Cache is running faster, and it will continue to use it even after repeatable run.

    I just want to clock the time on my SSMS.

    M

  • July 10, 2014 at 12:58 am

    #1728278

    Yes, but that's fine. That's why I said ignore the execution times of the first run (the one that loads the data into memory). After that, the times are consistent and you can do your comparisons

    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

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

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