10

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?

5

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

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.