How can I go about cycling all the sql stats which are stored in a DMV? I want to see how the database is performing since changes were made to many procedures so I want a fresh start, is there any way to go about this without restarting the instance? Or is there a better way to go about this
It completely depends on the DMV in question. For example, sys.dm_exec_query_stats is completely dependent on the cache. If you wanted to, you could clear it out by issuing DBCC FREEPROCCACHE. But, before you run off and do that to the production machine, remember, that means that every query in the system will have to recompile. sys.dm_index_usage_stats is only reset when the server restarts or a database is detached.