How to Reset SQL Server Usage for Performance Testing

performanceperformance-testingsql server

In my development environment, I am playing around with queries. For that I need to reset SQL Server memory, plans, etc. things which can ensure me that my query is the only stress/working process on server with all values reset

Few of the things which I am doing

  • Firing checkpoint
  • DBCC DROPCLEANBUFFERS;
  • DBCC FREEPROCCACHE;
  • DBCC FLUSHPROCINDB
  • DBCC FREESESSIONCACHE
  • DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

Am I doing it correctly? or do I need more things to clear or see fresh statistics?

Best Answer

Short of restarting the SQL Server service or the box this is enough. If you want to be absolutely fresh you might want to drop user created statistics from your last run.

SELECT stats.name, objects.name, schemas.name,objects.create_date
FROM sys.stats
JOIN sys.objects ON stats.OBJECT_ID = objects.OBJECT_ID
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
WHERE stats.stats_id > 0
  AND stats.stats_id < 255
  AND objects.is_ms_shipped = 0
  AND auto_created = 1
  order by objects.create_date desc
Related Question