Share via

framentation and index performance

Shambhu Rai 1,411 Reputation points
2024-02-05T02:34:05.54+00:00

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.
13,361 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Zahid Butt 796 Reputation points
    2024-02-05T13:35:22.9633333+00:00

    Hi, You may check index Fragmentation through SSMS & also through query. Please visit below link to get help: https://www.sqlshack.com/how-to-identify-and-resolve-sql-server-index-fragmentation/ Due to more fragmentation query performance may be affected. Regards,

  2. Rahul Randive 9,176 Reputation points Microsoft Employee
    2024-02-05T17:55:55.0966667+00:00

    Hi @Shambhu Rai
    Please find the YouTube video https://youtu.be/palv9-_UiFU?si=6KQb_jezsn64yWFN 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
  3. Erland Sommarskog 107.2K Reputation points
    2024-02-05T22:59:10.5666667+00:00

    Here is a query to inspect the fragmentation levels:

    SELECT s.name AS "schema", o.name AS "table", i.name 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