framentation and index performance

Shambhu Rai 1,401 Reputation points

Hi Expert, How to check fragmentation level and index performance .. please help me with performance in sql server

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,302 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Zahid Butt 551 Reputation points

    Hi, You may check index Fragmentation through SSMS & also through query. Please visit below link to get help: Due to more fragmentation query performance may be affected. Regards,

  2. Rahul Randive 6,856 Reputation points Microsoft Employee

    Hi @Shambhu Rai
    Please find the YouTube video for example and How to Repair Fragmentation of an Index. It explains how to find the fragmented index and how to perform reorganize or rebuild. Thank you!

    0 comments No comments

  3. Erland Sommarskog 99,296 Reputation points MVP

    Here is a query to inspect the fragmentation levels:

    SELECT AS "schema", AS "table", AS "index", ips.*
    FROM   sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, NULL) ips
    JOIN   sys.indexes i ON ips.object_id = i.object_id
                        AND ips.index_id  = i.index_id
    JOIN   sys.objects o ON ips.object_id = o.object_id
    JOIN   sys.schemas s ON s.schema_id = o.schema_id
    WHERE ips.page_count > 1000

    The filter on page count is there because fragmentation is not of an issue for smaller tables.

    A simple query for performance is impossible to give, because there are so many "it depends". When you do performance monitoring, you typically look at a number of metrics. Which may include queries that are critical for the system to ensure that their execution time stays with in reasonable limits.

    0 comments No comments