missing_index_group_stats and index_usage_stats DMVs

If you take an index recommendation from missing_index_group_stats, it clears the data in this DMV. But then, is the statistics moved over to  index_usage_stats DMVs, or does it start from scratch.?

For example. missing_index_group_stats recommends TableA (Col1,Col2), and seek could have happened 5000 times. Now you create this index, and the entry disappears for this database in missing_index_group_stats. Now, will it say 5000 user_seeks in sys.dm_db_index_usage_stats, or will the count start from 0 for this new index?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

the best way to answer this question will be - just try and see what happens

create a table with 10000 rows and issue a select for a single row 100 times,
then create the index

i find it hard to believe it will put 5000 in the insex_usage_stats dmv, since the index was not really accessed 5000 times, so in my opinion the count will start from 0
i did the test
you can see the attached code

you get 0 index seeks in the index_stats dmv since all the 1000 queries i've made are counted as user scans

create table momi (id int identity(1,1), pad char(5000) default 'aaa')

set nocount on
declare @i int 
select @i = 1

while @i <= 20000
insert into momi default values
set @i = @i+1

select count(*) from momi

declare @j int
declare @i int
select @i = 1
while @i <= 1000 begin
  select @j= id from momi where id = @i*20
  set @i = @i + 1

select * from sys.dm_db_missing_index_groups t1
 cross apply sys.dm_db_missing_index_columns(t1.index_handle) t2

select * from sys.dm_db_missing_index_group_stats 

create index idx_momi_id on momi(id)

select * from sys.dm_db_index_usage_stats where object_id=object_id('momi')

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anushahannaAuthor Commented:
>>you get 0 index seeks in the index_stats dmv since all the 1000 queries i've made are counted as user scans

and that would be right, that they were scans the first time. if you the select again at the end of the code, they end as seeks.

thanks momi_sabag.
Mark WillsTopic AdvisorCommented:
Hi anushahanna,

Sorry I missed this one. Normally like talking about DMV's have you seen : http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Making-Sense-of-Index-DMV's-in-SQL-Server.html
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.