Before running a performance test / baseline for an app that uses SQL Server, I want to be able to set the instance to a "clean" state, without restarting the instance. There are steps I tend to follow, but I want to build a definitive list that is in the correct sequence, and has no redundant steps.

Does this list of steps accomplish setting SQL Server to a "clean" state?

Is the sequence logical / correct?

Are there any redundant steps?

CHECKPOINT              -- Write all dirty pages

DBCC DROPCLEANBUFFERS   -- All should be clean after checkpoint?

DBCC FREEPROCCACHE      -- Clear the plan cache


DBCC FREESESSIONCACHE   -- May not be necessary if distributed queries aren't used, but want to catch all scenarios

EXEC SP_UPDATESTATS     -- Refresh stats

  • 5
    FYI, the DROPCLEANBUFFERS is nice for testing but not always accurate. If you are referencing a high-volume table it's very likely you will almost always have pages in memory, and the IO time won't be a big factor in that query. You may be placing more weight on IO than is realistic in that case. – JNK Mar 6 '12 at 16:52
  • Are you talking about testing in a production environment or an isolated testing environment? – bopapa_1979 Mar 21 '12 at 16:17
  • Anyone who tests in a Prod environment should be fired. :) Yes, test environments. – Eric Higgins Mar 21 '12 at 18:31

First, I'd step back and ask what measurements you plan to collect during the test. If you're counting logical reads by query, for example, then you don't need to free the cache. I'm a big fan of using logical reads because it's independent of whether the data is cached or on disk - and in production, it's hard to guess whether a query's data will be cached or not (unless you cache the entire database in memory). If you tune to minimize logical reads, then the app will go faster whether the data's in cache or not.

Next, I'd question what's changing between runs. For example, by running EXEC SP_UPDATESTATS in each database as you've suggested, you're going to resample stats for tables that have been updated. However, unless you're updating statistics with fullscan, you're getting random rows from the table - that's not too repeatable, and I don't think you really want to do that. Instead, you might want to restore the databases between each run so that you're always testing exactly the same data. If your tests are doing inserts/updates/deletes, they might have different performance profiles on each run if you're not restoring the database (because they're adding/changing data, plus changing statistics on the data) - and even worse, you could have queries fail if they try to insert repeated data for the same values.

| improve this answer | |
  • Very good points, the goal is to have everything identical between runs. The measurements I'm taking in this case @ hand are run times for specific functions in an app (x seconds to return list to app, y seconds to add a queue item, etc). What is changing between tests could be pieces of app code & not SQL objects, SQL objects and not app code, or instance/DB level settings such as concurrency without changes to app code. If I were to add a restore out of the gate before each test, how do you feel about my list above @ that point? Am I missing anything, or does the sequence need some work? – Eric Higgins Mar 27 '12 at 14:19
  • Brent, are you taking in account CPU in your testing? – A-K Mar 27 '12 at 15:26
  • @EricHiggins Rather than testing multiple things at once, I'd test the pieces individually. I'd rather test queries directly and see what changes impact performance there. For example, run a SQL trace while performing specific functions in the app, and then keep replaying that trace while making index/config changes to improve performance, and watch things like logical reads and CPU metrics in traces. – Brent Ozar Mar 28 '12 at 15:15
  • @AlexKuznetsov I'm not the one doing the testing, actually - Eric is the one who asked the question. When I do this kind of work, I do look at CPU metrics at the query level as well as the server overall. – Brent Ozar Mar 28 '12 at 15:16
  • We use a 3rd party load generator (and have a full time person dedicated to development of load tests). So my tests are precise to the transaction, sequence, # of users, exact steps performed in the app...everything. So I don't necessarily need to look at SQL dashboard type metrics at all. The load test software tracks the response times for the app modules to the millisecond. So doing a DB restore is a good idea. I need to sanity check the other steps I am doing to be sure I'm accomplishing that "Clean slate" state I'm seeking before each round of testing. – Eric Higgins Mar 28 '12 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.