clearing DMV stats. SQLPERF does not work.

Question

clearing DMV stats. SQLPERF does not work.

winston Smith

SSCoach

Points: 19500
More actions
October 11, 2010 at 2:33 am

#387973

I want to clear the following DMV/DMF's:
sys.dm_db_index_usage_stats
sys.dm_db_index_operational_stats
using SQLPERF does not work for either one. Outside of restarting my sql service when i need to clear the DMV/DMF's, how can i clear them?

Viewing 7 posts - 1 through 6 (of 6 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I don't think it's possible.

You can clear sys.dm_os_wait_stats and sys.dm_os_latch_stats with DBCC PERF, I'm not aware of other DMVs that can be cleared without a restart.

-- Gianluca Sartori

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

Take the database offline, bring it back online. It'll wipe those DMVs and a whole lot more (including data cache and procedure cache). Not recommended on a busy production server.

Why do you need them cleared?

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

winston Smith SSCoach Points: 19500 More actions · Answer 3

Im writing a job to gather index usage stats.

Lets say indexA has 10 scans on monday, and no scans on tuesday, wednesday or thrusday.

If i run my job once per day, grab the number of scans in the dmv and add it to a column in a logging table, i will see the number of scans as 30 for the 3 days, and not the correct value of 10.

I know i can look at the problem from different angle, and use it as a snapshot view of the indexes on that particular day, which allows accurate usage trends to be viewed so thats how il go. just would have been easier if i could flush those DMV's.

Thanks.

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

winston Smith (10/11/2010)
I know i can look at the problem from different angle, and use it as a snapshot view of the indexes on that particular day, which allows accurate usage trends to be viewed so thats how il go.

That's a far, far better approach to the problem.

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Why don't you use Performance Data Collector?

From the description of your problem, there's the risk of re-inventing the wheel.

-- Gianluca Sartori

winston Smith SSCoach Points: 19500 More actions · Answer 6

that would be the ideal approach, however its a production system and our department are currently in Anal mode regarding adding anything new to production.

I know its not new, and wont cause harm, my opinion is not listened to here.