Post reply

How to check the index fragmentation on a schema binding view

  • April 10, 2013 at 6:50 am

    #292134

    How to pull the index usage stats from a schema binding view ?

  • April 10, 2013 at 8:20 am

    #1605249

    Same as any other index:

    SELECT DB_NAME(database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name],

    i.name AS [Index Name], ps.index_id, index_type_desc,

    avg_fragmentation_in_percent, fragment_count, page_count

    FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,N'LIMITED') AS ps

    INNER JOIN sys.indexes AS i WITH (NOLOCK)

    ON ps.[object_id] = i.[object_id]

    AND ps.index_id = i.index_id

    WHERE database_id = DB_ID()

    AND page_count > 1500

    ORDER BY avg_fragmentation_in_percent DESC OPTION (RECOMPILE);

    Are you sure it's indexed and not just schemabound?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 2 posts - 1 through 1 (of 1 total)

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