Can the DMV views get reset without kicking eveyon

问题:

I have seen posts that show three ways to reset the DMV views:

1. Reset the SQL Service
2. Detatch the database
3. Close the database

All of these methods seem to require taking the system off-line for a few moments. Is there a way to reset the statistics on demand without interrupting use of the database? When we have odd performance issues come up in production it is useful to limit the data in the dmv views to data collected during the duration of the performance issue to help describe the state of the system during the performance issue.

回答1:

You can reset exactly 2 DMVs only (BOL link)

sys.dm_os_latch_stats
sys.dm_os_wait_stats

回答2:

As far as I know, these views will only be updated when the instance is restarted.

However, for some views you could take a copy of the data before you start measuring and doing a diff afterwards.

回答3:

We ended up snap shotting the DMV views to pull delta's in the data. We are looking in to the SQL Server Performance Data warehouse which we assume does the same thing.