Index_usage _stats not showing results for all indexes

Question

Hi all,

I am using below query to find the usage of indexes in my database.

When i am running this on production database its showing me results for each and every index in there but when i am running this on my Dev database its not showing me result for all indexes(means they are not even there in the result window) . Could anyone suggest me whats is wrong with it or what i might not be doing to extract the full results?

declare @dbid int

 --To get Datbase ID

 set @dbid = db_id()

select

 db_name(d.database_id) database_name

 ,object_name(d.object_id) object_name

 ,s.name index_name,

 c.index_columns

 ,d.*

 from sys.dm_db_index_usage_stats d

 inner join sys.indexes s

 on d.object_id = s.object_id

 and d.index_id = s.index_id

 left outer join

 (select distinct object_id, index_id,

 stuff((SELECT ','+col_name(object_id,column_id ) as 'data()' FROM sys.index_columns t2

 where t1.object_id = t2.object_id

 and t1.index_id = t2.index_id FOR XML PATH ('')),1,1,'')as 'index_columns' FROM sys.index_columns t1 ) c on

 c.index_id = s.index_id and c.object_id = s.object_id

 where database_id = @dbid

 --and s.type_desc = 'NONCLUSTERED'

 and objectproperty(d.object_id, 'IsIndexable') = 1

 order by

 object_name

Thanks,

---

Dinkar Chalotra

Answer 1

When i am running this on production database its showing me results for each and every index in there but when i am running this on my Dev database its not showing me result for all indexes(means they are not even there in the result window) . Could anyone suggest me whats is wrong with it or what i might not be doing to extract the full results?

I don't think anything is wrong, it's just that rows are inserted/updated only when the index is used.  Unlike the prod server, I suspect the dev server activity is such that not all indexes are used.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

I agree, i can see one index has come back with results because i just ran a job to use it.

Question is: Doesnt backup and restore bring stats across? if not, then what stats come across and based on what terms?

---

Dinkar Chalotra

Answer 3

Question is: Doesnt backup and restore bring stats across? if not, then what stats come across and based on what terms?

sys.dm_db_index_usage_stats is initialized upon SQL Server restart and maintained as indexes are used.  See http://msdn.microsoft.com/en-us/library/ms188755.aspx.  Like many other DMVs, the data is transient and not necessarily persisted.  Consequently, the datais not included in a backup/restore.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 4

Thanks Dan,

Question i always ask to myself: You have read this http://msdn.microsoft.com/en-us/library/ms188755.aspx before then why didnt you already know about it?

Reply myself: Thats where exprience matters, so have patience. :)

Once again much appreciated Dan.

P.S. Please remove little dot at the end of your link, it doesnt work with it.

---

Dinkar Chalotra

Answer 5

Question i always ask to myself: You have read this http://msdn.microsoft.com/en-us/library/ms188755.aspx before then why didnt you already know about it?

Reply myself: Thats where exprience matters, so have patience. :)

:-)

I spend a good share of time reading the Books Online and blogs while helping in these forums, not to mention my day job.  Like you, I wish I could everything I read.  I'll warn you that it doesn't get any easier over time :-)

Thanks for pointing out the problem with the link in my response.  I corrected it.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 6

My coworker asked me the same thing the other day when I didn't quite understand the issues with having QUOTED_IDENTIFIERS set to OFF. (The problem was that we had created a computed column and some legacy code couldn't insert data due to quoted identifiers being turned off.)  If you took the time to know everything you wouldn't have time to sleep, eat, or make a living :)

---

Louis

Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

Answer 7

 

So true Louis.

---

Dinkar Chalotra