One of your customers reported some very slow Microsoft SQL Server database access times. You restored his database at home, and were able reproduce the performance issue. But after the first execution, your super-slow query is now blazing-fast. Still you didn’t make a change… This is a courtesy from the SQL Server performance engine which cached a lot of things related to your previous query execution. How can you clear/flush this SQL Server cache before executing your performance tests ?

The first, simplest, solution is to restart your SQL Server instance. Efficient, but slow and painful, and often not acceptable in a production environment.

The second solution is to use the DBCC FREESYSTEMCACHE SQL Server command before each of your performance test :

[code lang=SQL]
DBCC FREESYSTEMCACHE (‘ALL’)
GO
[/code]

One thing to watch out for is the temporary performance hit, if you’re working on a production environment. As mentioned in the Microsoft doc, “Executing DBCC FREESYSTEMCACHE clears the plan cache for the instance of SQL Server”, so it will affect all the databases on your instance, not only the currently selected one.