Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm attempting to improve performance of a stored procedure. When I run the SP, it finishes almost instantly, as if something were cached. I was told to use the following two lines of SQL before executing the SP in SSMS:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

When I run the SP with the two lines of code above the SP finishes in around 8 seconds. However, is this really giving me the real execution time (as in if I run this from an application)? How do I know?

share|improve this question

1 Answer 1

up vote 8 down vote accepted

Those commands do two things:

  • Clear the page cache, which stores data pages that have already been retrieved from disk (normally the biggest factor in time in a query is disk access)
  • Clear the query plan cache, which means the server needs to create a new query plan. This is normally not significant except for very high transaction volumes.

You are basically getting a time that is equivalent to the "worst case" scenario - you just rebooted the server and nothing is in memory. Subsequent runs don't need to pay the cost to pull the data from disk since those pages are already loaded into memory.

This is similar to a real world situation - your first user to run a particular query will likely have to wait longer than on subsequent runs, assuming you are checking the same data.

A good method that I like to use is running multiple times and taking an average. This is especially helpful in a shared environment since you don't have full control over shared resources like tempdb.

You can also use these commands to get more information on what is actually happening behind the scenes:

SET STATISTICS IO ON
SET STATISTICS TIME ON

These will give you detailed information about page reads from disk (per object), logical page reads, time spent compiling a plan, and time spent executing a query.

share|improve this answer
    
excellent answer as usual JNK. –  O.O Jan 20 '12 at 18:19
    
Happy to help! You just happen to be asking questions about things I have dealt with a lot. If you ask about replication or memory management I won't have much to add :) –  JNK Jan 20 '12 at 18:21
2  
I knew about SET STATISTICS IO ON, but I never knew about SET STATISTICS TIME ON. Thanks JNK. –  datagod Jan 20 '12 at 19:17
2  
Just FYI you can get a lot of this information without all the SET settings by generating actual plans using SQL Sentry Plan Explorer. And to get multiple (say, 5) runs you can simply end the batch with GO 5 just like in SSMS. It's a free download, I am not trying to sell you anything. sqlsentry.net/plan-explorer/sql-server-query-view.asp –  Aaron Bertrand Jan 20 '12 at 23:00
1  
@JNK well, sure, it's free, it saves time, and it shows information that is either impossible or very cumbersome to get out of Management Studio. And I work there. :-) –  Aaron Bertrand Jan 21 '12 at 17:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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