{"id":497,"date":"2012-04-26T07:32:00","date_gmt":"2012-04-26T07:32:00","guid":{"rendered":"\/blogs\/joe\/post\/Exceptionse28093what-sysdm_db_index_usage_stats-doesnt-tell-you-(Part-II).aspx"},"modified":"2013-01-02T20:31:57","modified_gmt":"2013-01-03T04:31:57","slug":"exceptions-what-sys-dm_db_index_usage_stats-doesnt-tell-you-part-ii","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/exceptions-what-sys-dm_db_index_usage_stats-doesnt-tell-you-part-ii\/","title":{"rendered":"Exceptions\u2013what sys.dm_db_index_usage_stats doesn’t tell you (Part II)"},"content":{"rendered":"

\nLast November I blogged about how index usage stats don’t get updated when the associated index statistics (but not index) are used<\/a>.\n<\/p>\n

\nThis post will describe another scenario that you should be aware of (the topic came up today in class while Kimberly was teaching – as we were trying to recall tricks to clearing stats for sys.dm_db_index_usage_stats)…\n<\/p>\n

\nImagine that I’ve queried a specific table as follows:\n<\/p>\n

\n

\n\tSELECT member_no, lastname, firstname, middleinitial, street, city, state_prov, country
\n\tFROM dbo.member
\n\tWHERE member_no = 1;\n\t<\/p>\n<\/blockquote>\n

\nIf I check sys.dm_db_index_usage_stats for any reference to the member table, I’ll see the following:\n<\/p>\n

\n

\n\tSELECT i.index_id, i.name, u.user_seeks, u.user_lookups, u.user_scans
\n\tFROM sys.dm_db_index_usage_stats u
\n\tINNER JOIN sys.indexes i ON
\n\t     u.object_id = i.object_id AND
\n\t     u.index_id = i.index_id
\n\tWHERE u.object_id=object_id('dbo.member')\n\t<\/p>\n<\/blockquote>\n

\nThis returns:\n<\/p>\n

\n\"image\"<\/a>\n<\/p>\n

\nNow let’s say that you have a weekly rebuild of specific indexes (for example):\n<\/p>\n

\n

\n\tALTER INDEX member_ident
\n\tON dbo.member REBUILD\n\t<\/p>\n<\/blockquote>\n

\nIf I check for usage stats after rebuilding the query (and before anyone has accessed the member table specifically) – the stats have been cleared out for that table. \n<\/p>\n

\n\"image\"<\/a>\n<\/p>\n

\nWhy does this matter? \n<\/p>\n

\nIf you’re using the sys.dm_db_index_usage_stats to determine which indexes should be removed, you’re running the risk of making decisions based on recently cleared out statistics.  This is similar to the case where a SQL Server instance has been recently restarted.  You should not be dropping indexes without knowing whether the accumulated statistics represent the full set of critical workloads. \n<\/p>\n

\nFor tables with frequent index rebuilds, be sure to capture data from sys.dm_db_index_usage_stats before these jobs run.  This DMV is definitely a useful tool, but if you’re not careful, you could be dropping indexes based on missing information.\n<\/p>\n

\nA few other noteworthy items:\n<\/p>\n