-1

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
1
  • This information isn't collected by default. You can add a trace to poll for page splits using either the XE page_split event or sniff the transaction log using XE (the later has possibly quite hefty overhead) and then read this trace. Mar 15 at 15:49
1

you can collect avg_fragmentation_in_percent information over time (e.g., every 5 minutes) and then you can see how and when fragmentation moves through the hours...

In this way you create the table...

SELECT
S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count

CAST(getdate() as datetim2(5)) AS collection_time
INTO dob.mytable

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

and then...

INSERT INTO dbo.mytable([Schema],[Tbale],[Index],avg_fragmentation_in_percent,page_count,collection_time)

SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
CAST(getdate() as datetim2(5)) AS collection_time
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
3
  • Good point potentially easier than having a trace running. Note that collecting this isn't free either, so implement with care. Mar 15 at 17:38
  • I could do that, but I wouldnt be able to do that out of business hours, so I would need to create a job. What can I add to my query that will log the info to a table? Mar 15 at 17:58
  • You can find it into my answer. It could have some weight, so my suggestion is, after an initial review, to focus on problematic indexes.
    – MBuschi
    Mar 16 at 12:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.