ansaurus

Question

SQL Server Query is faster in SSMS when including execution plan.

Answer 1

+1 A:

Sounds a bit odd to me tbh. Are you sure that the difference you're seeing is not down to caching? I would always test the performance of a sproc by NOT including the execution plan, and I would clear the cache down before each run in order to have a fair comparison (on a test/dev db server, not production).

DBCC FREEPROCCACHE -- will clear the execution plan cache
DBCC DROPCLEANBUFFERS -- will clear the data cache

AdaTheDev 2010-10-22 12:32:42

When I run DBCC DROPFREEBUFFERS I get an error -- Incorrect DBCC statement. When I google it the command DBCC DROPCLEANBUFFERS comes up.

Chris Mullins 2010-10-22 12:39:27

@Chris - sorry, typo! corrected now

AdaTheDev 2010-10-22 12:41:27

Well I tested it with DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS commands before the statement, and it is still faster with the Show Execution Plan on. I wonder if it has something to do with statistics, and if so how I can get the faster set of results when I run the query in production.

Chris Mullins 2010-10-22 12:44:02

I am going to mark your answer as correct. Although I still don't understand why it happens, it is probably a bug in the way the client statistics are counted, maybe they don't include the time for creating the execution plan when the execution plan display is on, or something like that. Your tip about clearing the cache helps me get a good baseline on the total execution time between trys so thanks for that.

Chris Mullins 2010-10-26 10:48:27

ansaurus

tags:

views:

answers:

SQL Server Query is faster in SSMS when including execution plan.

related questions