Bug in dm_db_index_usage_stats?

  • This works fine for all our tables & indexes apart from one, where it always reports 0 user_updates and the all last_user_seek, last_user_scan and last_user_lookups are from over a month ago. This is a reasonable large table (400 million rows, 55gb) with one index (the clustering one) and very high traffic on writes, less so on reads. We're running a SQL Server 2008 mirrored pair.

    Are there any known issues with dm_db_index_usage_stats or is there something else I need to check?

  • No thoughts on this one? 🙁

  • Please give more clarity of your question.

    Thanks

  • I don't know how I could have been any clearer?

  • Is it all indexes on a certain table? Just one index on the table?

    Are you 100% sure the table's in use, not maybe a copy of the table in a different schema?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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
  • One table and that table only has one index which is the clustering index. And yes 100% sure that table is in use! The rows in it are timestamped and you can see new rows flooding in to that table at a rapid rate during busy periods.

  • Inserts won't change the last seek, scan or lookup date. Only something that reads the table (select, update, delete) will change those.

    If you think it's a bug, file it on Connect

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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
  • According to dm_db_index_usage_stats the clustered index (the only index) has never been updated even though rows are constantly being added to it. Profiling reveals the table is being read from a fair bit and yet the last seek/scan/lookups are all from months ago. When I query the table directly in management studio the last seek/scan/lookups are never updated either. It's really weird.

  • Can you update the stats for the table and then run the index_usage?

    ----------
    Ashish

  • I know this sounds silly, but can you run the following?

    SELECT schema_name(schema_id), name as TableName, object_id FROM sys.objects where name = <name of the table in question>

    Is there only one of them?

    I've seen before a copy of a table gets created in another schema and people drive themselves crazy looking at one table when they think they're looking at a different one.

    What query are you using to view the index usage stats?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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
  • crazy4sql (4/6/2011)


    Can you update the stats for the table and then run the index_usage?

    Why?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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
  • because I beleive the table and its stats are out dated.

    What does the index fregmentation reports for the perticular index of the table?

    ----------
    Ashish

  • crazy4sql (4/6/2011)


    because I beleive the table and its stats are out dated.

    Maybe, maybe not, but that has nothing to do with and no effect whatsoever on the index usage DMV. Neither does fragmentation.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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
  • Thanks Gila... although you did not solve the problem directly, you pointed me down the path to finding the issue and now I feel a little stupid!

    sys.dm_db_index_usage_stats is of course not database-scoped... it is entire server scoped. Doh! So my wrapping sproc initially looked like this:

    ALTER PROCEDURE [dbo].[dm_IndexUsage]

    @Table NVARCHAR(255)

    AS

    BEGIN

    SET NOCOUNT ON ;

    DECLARE @DB_ID SMALLINT ;

    DECLARE @Table_ID INT ;

    SET @DB_ID = DB_ID() ;

    SET @Table_ID = OBJECT_ID(@TABLE) ;

    IF @Table_ID IS NULL

    BEGIN

    PRINT N'Invalid Table' ;

    END

    ELSE

    BEGIN

    SELECT IX.name AS [Name],

    SUBSTRING(IX.type_desc,0,5) AS [Type],

    Usage.user_seeks AS [Seeks],

    Usage.user_scans AS [Scans],

    Usage.user_lookups AS [Lookups],

    Usage.user_updates AS [Updates],

    CAST((SELECT MAX(LastHit)

    FROM (SELECT last_user_seek AS LastHit

    UNION ALL

    SELECT last_user_scan

    UNION ALL

    SELECT last_user_lookup) AS Dates) AS DATETIME2(0)) AS [Last Usage]

    FROM sys.indexes IX

    CROSS APPLY

    (SELECT TOP 1 *

    FROM sys.dm_db_index_usage_stats Usage

    WHERE Usage.object_id = IX.object_id

    AND Usage.index_id = IX.index_id) Usage

    WHERE IX.OBJECT_ID = @Table_ID

    ORDER BY

    IX.type_desc,

    IX.name

    END

    END

    and of course the cross apply condition is missing a database_id clause. It just so happened that in another database (MSDB) there was a table with the exact same object_id as the table I was looking at in my database and it was getting the stats from that one instead. double doh! So adding the clause fixed this.

    AND Usage.database_id = DB_ID()

    Thanks!

  • Alex Webber (4/6/2011)


    Thanks Gila... although you did not solve the problem directly, you pointed me down the path to finding the issue and now I feel a little stupid!

    Don't worry, we've all done that or similar once or twice (or several times)

    It just so happened that in another database (MSDB) there was a table with the exact same object_id as the table I was looking at in my database and it was getting the stats from that one instead. double doh!

    Glad it's sorted out.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply