framentation and index performance

Question

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

Answer 1

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,

Answer 2

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!

Answer 3

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.