When was my indexes in the database fragmented?

Question

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

Answer 1

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.

Answer 2

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 .

Answer 3

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.