Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

"sys.dm_db_index_usage_stats" has missing information Expand / Collapse
Author
Message
Posted Tuesday, July 8, 2014 5:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963

As far as i know sys.dm_db_index_usage_stats should have one record per table and index but in one of my server this count is mismTHIS IS CLEARLY SPAMans this DMV doesn't have records for every index/table.

there was issue in sql 2005 which can be resolved by trace flag 2330 but i think this has been fixed in sql 2008

i am using sql 2008 but still i can see same issue.


Another question is , if i do dbcc traceon (2330,-1) , does it "STOP" to gather the information OR "START" to gather the information ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1590309
Posted Tuesday, July 8, 2014 5:59 AM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 39,872, Visits: 36,217
As far as i know sys.dm_db_index_usage_stats should have one record per table and index


No. It has one row for each index which has been used since SQL last started. If an index has not been used in any way since SQL last started, it will have no row in that DMV.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1590314
Posted Tuesday, July 8, 2014 6:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
thanks a lot Gail

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1590315
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse