Reset SQL Server Index usage

Question

I use below query to analyze usage of index in SQL Server.

SELECT * 
FROM sys.dm_db_index_usage_stats A
WHERE A.database_id = DB_ID()

How can reset all data from this system table?

Solution 1

What do you mean by reset .. do you want to reset the index usage statistics in the table?

Taken from Here

Usage statistics: These are found in sys.dm_db_index_usage_stats. Index usage statistics keep track of things like seeks and scans from SELECT queries. They are not persisted and get reset on restart of sql server. These statistics also get reset if the underlying index is rebuilt "ALTER INDEX ... REBUILD", but not with "ALTER INDEX ... REORG"

As said, you can't reset it manually. Take a look at this post which certainly says the same

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/08eb7b79-64a3-4475-bfc3-69715aec8381/resetting-dmdbindexusagestats-without-restarting-or-detaching-a-database

Solution 2

Like mentioned, you cannot truly reset it without restarting the SQL Server.

BUT

Why do you want to reset it? Probably because you have made changes to your indexes and simply want to see how the usage has changed, am I right?

In this case you can hardcode the existing values into your query and subtract it to get new stats from this point.

By "hardcoding" I mean joining with a VALUES pseudo-table, something like this

--your SELECT goes here
--your FROM goes here
--add this JOIN
    JOIN ( VALUES('IX_index1',  2412727),
    ('IX_index2',   1630517),
    ('IX_index3',   514129)) o(name, seeks) ON o.name=indexes.name
-- rest of your query

Now you can add this to your SELECT to get the difference:

SELECT dm_db_index_usage_stats.user_seeks - o.seeks AS newseeks

So in a nutshell:

1. SELECT the existing usage stats from dm_db_index_usage_stats
2. do some copy-pasting magic to get the existing stats and hardcode into your query
3. see the changes