DB_DB_INDEX_PHYSLCAL_STATS returning wrong indexes and table functions?

  • If I run this query

    SELECT *

    FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, N'LIMITED')

    WHERE [sys].[dm_db_index_physical_stats].[page_count] > 100 -- Smallest tables are ignored by page size

    AND [sys].[dm_db_index_physical_stats].[avg_fragmentation_in_percent] > 10 -- maximum fragmentation that we allow

    AND [sys].[dm_db_index_physical_stats].[index_id] > 0 -- We don't want to do anything to heaps

    then look at the object and index it returns, I am getting back indexes that don't exist if I query the sys.index table, and I am also getting back non-existant indexes that claim they tie back to table functions based on the object id, schema and database...

    any idea what would cause this?

  • You're passing NULL as the first parameter so the query is checking all indexes on all tables in all databases on the instance. Look at the database ID that the function returns and look for those objects in their databases.

    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
  • yes, I know, I am trying to get all the fragmented indexes back in all databases.

    When I looked up the object ID I did look it up by database ID also, and it still returns bad data... for instance... this query returns to me a row

    database_id = 9

    object_id = 933578364

    index_id = 5

    partition_number = 1

    when I look up that object and index on that database, the object only has 4 total indexes, not 5. So the highest Index_ID for that table was 4, but it was telling me that index_id 5 was fragmented...

  • How are you checking the index defs in database 5? What query?

    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
  • just going against the sys.indexes table

  • As in, from the database you're in, querying FROM sys.indexes where object_id = 933578364?

    Is the database you're currently using the one with an ID of 5? The sys.indexes view is database-specific, sys.dm_db_index_physical_stats is instance-wide.

    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
  • yes, I am querying the sys.indexes table from the database that the database ID matches

  • Then all I can suggest is double check that you are looking at the correct server, correct database and correct query. Most common reason I've seen for problems like this is accidentally looking at the wrong server (incredibly easy to do) or the wrong database on the server.

    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
  • nope, still same result...

    even closed down SSMS and started from scratch again just to make sure I wasn't mistyping something... still showing a 5th index as the result on DB_ID 9, I am connected and using DBID 9 on the same server... I'm lost...

  • SELECT database_id, object_id, index_id, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(9,933578364,5,null, 'limited')

    SELECT DB_NAME(9)

    SELECT object_name(933578364,9)

    What do those return?

    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
  • database_id object_id index_id avg_fragmentation_in_percent

    ----------- ----------- ----------- ----------------------------

    9 933578364 5 30

    (1 row(s) affected)

    --------------------------------------------------------------------------------------------------------------------------------

    Db_LiveSys

    (1 row(s) affected)

    and

    USE [Db_LiveSys]

    GO

    SELECT * FROM [sys].[indexes] WHERE [sys].[indexes].[object_id] = 933578364

    GO

    returns

    object_id name index_id type type_desc is_unique data_space_id ignore_dup_key is_primary_key is_unique_constraint fill_factor is_padded is_disabled is_hypothetical allow_row_locks allow_page_locks has_filter filter_definition

    ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ---- ------------------------------------------------------------ --------- ------------- -------------- -------------- -------------------- ----------- --------- ----------- --------------- --------------- ---------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    933578364 PK_RPatternsForAccount 1 1 CLUSTERED 1 3 0 1 0 90 0 0 0 1 1 0 NULL

    933578364 IX_RPatternsJunAccounts_1 2 2 NONCLUSTERED 0 3 0 0 0 0 0 0 0 1 1 0 NULL

    933578364 IX_RPatternsJunAccounts_2 3 2 NONCLUSTERED 0 3 0 0 0 0 0 0 0 1 1 0 NULL

    933578364 IX_RPatternsForAccount 4 2 NONCLUSTERED 0 3 0 0 0 0 0 0 0 1 1 0 NULL

    (4 row(s) affected)

Viewing 11 posts - 1 through 10 (of 10 total)

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