DB_DB_INDEX_PHYSLCAL_STATS returning wrong indexes and table functions?

  • brian.henry

    SSC Veteran

    Points: 294

    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?

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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
  • brian.henry

    SSC Veteran

    Points: 294

    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...

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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
  • brian.henry

    SSC Veteran

    Points: 294

    just going against the sys.indexes table

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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
  • brian.henry

    SSC Veteran

    Points: 294

    yes, I am querying the sys.indexes table from the database that the database ID matches

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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
  • brian.henry

    SSC Veteran

    Points: 294

    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...

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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
  • brian.henry

    SSC Veteran

    Points: 294

    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 11 (of 11 total)

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