When was my indexes in the database fragmented?

SQLLover21 201 Reputation points
2021-03-15T15:40:29.527+00:00

I am trying to create a script that finds the index fragmentation, have it log to a table that I can query each time it runs. I need to fins out specifically what time my indexes in a table get fragmented. Below is the script I have so far to find the index fragmentation. What can I add to:

  1. find the time indexes are fragmented
  2. have it log to a table

Thanks in advance.

SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 30
and DDIPS.page_count > 1000
ORDER BY DDIPS.avg_fragmentation_in_percent desc
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,323 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Shashank Singh 6,251 Reputation points
    2021-03-15T15:53:17.213+00:00

    You are trying to find out thing which has very little relevance. Index gets fragmented when DML operations happen. Are you going to log all DML operations in a table. Can you let me know what you are trying to achieve.


  2. Erland Sommarskog 106.5K Reputation points
    2021-03-15T22:51:31.227+00:00

    I hope that these are tables of any size. Don't care about fragmentation on tables of less than 1000 pages. (And for that matter, it can be disputed whether you should care at all, when the data is on SSD.)

    You would have to run that script every fifteen minutes or so to detect the changes, and possibly combine it with a trace.

    But I am not sure this is worth spending time on .

    0 comments No comments

  3. CathyJi-MSFT 21,126 Reputation points Microsoft Vendor
    2021-03-16T07:34:42.007+00:00

    Hi @SQLLover21 ,

    By using sys.dm_db_index_physical_stats, you can detect fragmentation in index. Indexes with fragmentation of less than 5 percent do not need to be defragmented because the benefit from removing such a small amount of fragmentation is almost always vastly outweighed by the CPU cost incurred to reorganize or rebuild the index. Also, rebuilding or reorganizing small rowstore indexes generally does not reduce actual fragmentation.


    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

    0 comments No comments