How to reset DMV counters

Question

Post reply

How to reset DMV counters

M&M

SSC-Insane

Points: 21700
More actions
April 8, 2010 at 11:34 am

#146407

Can you please let me know if we can reset the values in DMVs without restarting the instance.
M&M

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

There are two DMV's that can be reset without a restart of the instance.

sys.dm_os_latch_stats

sys.dm_os_wait_stats

Source

Otherwise you will need to restart the instance.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

M&M SSC-Insane Points: 21700 More actions · Answer 2

Thanks Jason,

I am working on generating the Performance dashboard reports (PDR) on my server and we are looking for reseting the counters without restarting the instance.

Would clearing these counters

[ "sys.dm_os_latch_stats" , CLEAR ]

[ "sys.dm_os_wait_stats" , CLEAR ]

help on getting totally new reports from the PDR's.

I guess PDR's would be using a whole lot of other DMV's also right?

M&M

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

mohammed moinudheen (4/8/2010)
Thanks Jason,
I am working on generating the Performance dashboard reports (PDR) on my server and we are looking for reseting the counters without restarting the instance.
Would clearing these counters
[ "sys.dm_os_latch_stats" , CLEAR ]
[ "sys.dm_os_wait_stats" , CLEAR ]
help on getting totally new reports from the PDR's.
I guess PDR's would be using a whole lot of other DMV's also right?

Those reports should be querying numerous DMVs. The real reset for those counters and reports would only come after an Instance restart.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

M&M SSC-Insane Points: 21700 More actions · Answer 4

Thanks Jason, for your quick response 🙂

Really appreciate it.

M&M

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

You are welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ssismaddy SSCarpal Tunnel Points: 4850 More actions · Answer 6

but you can use

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR); for only those 2 DMV's. it should reset those counters.

But not for all the DMV's

Robert Frasca SSCertifiable Points: 6026 More actions · Answer 7

I have a server used in performance testing and the test database is RESTORE'd before every test. Why does it appear that all of the DMV's have been reset if they can only be reset by restarting the instance?

Hopefully, someone is still watching this thread. 🙂

"Beliefs" get in the way of learning.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Some DMVs can only be reset by restarting the instance. Some are partially or fully cleared by restarting the database (offline/online, close/open, restore). Depends on the DMV.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

joe.arias SSC Journeyman Points: 80 More actions · Answer 9

joe.arias

SSC Journeyman

Points: 80

August 25, 2015 at 4:31 pm

#1822347

DBCC FreeProcCache

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

joe.arias (8/25/2015)
DBCC FreeProcCache

While that works to clear the plan cache, it does nothing for the sys.dm_os_latch_stats DMV. Additionally, it's not really recommended to do on a production server, especially since it won't clear the stats in question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

joe.arias SSC Journeyman Points: 80 More actions · Answer 11

I agree that running

DBCC FreeProcCache

is not recommended in a production server.

The original question was:

Can you please let me know if we can reset the values in DMVs without restarting the instance.
M&M

DBCC FreeProcCache does clear sys.dm_exec_query_stats

If you are testing and tuning for performance, this can be invaluable way to save time.

I know this was an old thread, the only reason I replied was for the benefit of the community.

😀

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

joe.arias (8/25/2015)
DBCC FreeProcCache does clear sys.dm_exec_query_stats
If you are testing and tuning for performance, this can be invaluable way to save time.

It does, but it doesn't clear any other DMVs (other than the related sys.dm_exec_procedure/function_stats).

To be honest, unless I'm testing the effects of parameter sniffing, I've never seen a need or use for clearing the entire procedure cache. Recompiling the query/procedure I'm working on, yes maybe, but an ALTER PROCEDURE does that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass