script to verify index fragmentation

Question

script to verify index fragmentation

river1

SSC-Insane

Points: 24309
More actions
October 25, 2017 at 7:19 am

#324225

Dear all,
Can someone help me with t-sql code on how can I see the fragmentation of my indexes (relevant ones), probably all that have 1000 pages or more?
Thank you,

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I typed the name of this topic into my favourite search engine, and got all sorts of interesting results. You could have done the same, and had an answer in a fraction of the time you've waited so far.

John

river1 SSC-Insane Points: 24309 More actions · Answer 2

The one that I found is this:

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent DESC

But this seems to analyse all and I just want to see the relevant ones. As I criteria I was thinking indexes with more that 1000 pages

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 3

river1 - Wednesday, October 25, 2017 7:25 AM
The one that I found is this:
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent DESC
But this seems to analyse all and I just want to see the relevant ones. As I criteria I was thinking indexes with more that 1000 pages

Use the page_count from sys.dm_db_index_physical_stats

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

You only found one?! My search yielded about 1,500,000 results.

Have you looked at the page_count column of dm_db_index_physical_stats?

John

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 5

IF you believe that index fragmentation is actually a problem then, without question, one of the best there is was written by and is maintained by a fellow by the name of Ola Hallengren... and it's FREE. Here's the link to his index maintenance page.
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

That being said, on today's machines (Read/Write head movement is rampant due to many users but is optimized by the SAN head-end) and considering the general nature of front-end code (mostly single row which is NOT affected by fragmentation), fragmentation shouldn't be a concern at all. To wit, I've not rebuilt any indexes on my production boxes since the 17th of January, 2016 (almost 2 years at the time of this posting) and performance actually improved in the first 3 month and has NOT degraded over time thanks to what I call "natural fill factors". These aren't static databases either. We're constantly loading large volumes of new data and generating audit table rows as if it were our main goal.

What DOES matter is the percent of page used because it not only affects space on the disk, it affects backups, restores, how much memory is used, and performance because the smallest unit that SQL Server can actually read is an 8,192 byte page and the pages in the B-Tree that it had to traverse to get there. If you do index maintenance by fragmentation level, you may end up in a never ending circle UNLESS you plan the FILL FACTOR for every bloody index. If you don't, you'll end up with 99.9% fragmentation the very next day on any index that suffers even relatively small amounts of inserts or updates.

If you feel compelled to rebuild/reorganize indexes, then do it base on the average percent of page fullness rather than on fragmentation. Me? I don't feel so compelled because I've proven to myself that it mostly doesn't matter. Split pages will be used and filled as if you had planned a fill factor especially when it comes to non-clustered indexes that aren't based on an ever increasing key. As with anything else, there are some exceptions... I have one table that is now (after two years of no reindexing) only 52% page full due to the way it's used and abused that I'm considering reindexing the Clustered Index for.

The exception to all of this is if you ever shrink a database... then you really need to rebuild all indexes whether they're fragmented or totally page full or not because shrinking a file makes a real mess in other ways that can and will severely affect performance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

river1 SSC-Insane Points: 24309 More actions · Answer 6

I added the word indexstats.page_count as below but I am not sure if this is what see if the index has more that 1000 pages. Can someone help?

Also, why it is considered that only indexes with more that 1000 pages are relevant? probably because the other ones are two small what do you consider (in terms of page count ) a big index?

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,

ind.name AS IndexName, indexstats.index_type_desc AS IndexType,

indexstats.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats

INNER JOIN sys.indexes ind

ON ind.object_id = indexstats.object_id

AND ind.index_id = indexstats.index_id

WHERE indexstats.avg_fragmentation_in_percent > 30 and indexstats.page_count > 1000

ORDER BY indexstats.avg_fragmentation_in_percent DESC

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 7

river1 - Wednesday, October 25, 2017 8:12 AM
I added the word indexstats.page_count as below but I am not sure if this is what see if the index has more that 1000 pages. Can someone help?
Also, why it is considered that only indexes with more that 1000 pages are relevant? probably because the other ones are two small what do you consider (in terms of page count ) a big index?
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30 and indexstats.page_count > 1000
ORDER BY indexstats.avg_fragmentation_in_percent DESC

You DON'T need to look at sys.indexes to get the page count for an index. It's a totally unnecessary complication. As John Mitchell implied, you need to read the documentation on the system objects that you're using. With the idea of teaching someone to fish, please do a search for "sys.dm_db_index_physical_stats" and see for yourself.

Also, if you're not checking the FILL FACTOR and you have a lot of indexes that are 0 or 100 (both mean 100% filled pages), you are dooming yourself and your indexes to a never ending cycle of rebuilds and potentially massive blocking due to page splits after the index maintenance that you think is helping but has actually made things worse. An index can instantly go back to 99.9 % fragmented with less than a 1% insert or modification especially if the first column of the index is not based on ever increasing values.

I'll also say again that defragging based on % of fragmentation is foolhardy especially if you're not taking FILL FACTOR into account. If you feel compelled to do defrags because of some visceral fear or because you think it's a "Best Practice" because of the overwhelming number of people that think it is, then do it by % of page full rather than by % of fragmentation.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

river1 SSC-Insane Points: 24309 More actions · Answer 8

I am thinking in using OLA scripts. Where he stated that if index is 5÷ or less then it is not needed. If between 5 and 30, properly try a reorg and if more then 30, them rebuild indexes.

river1 SSC-Insane Points: 24309 More actions · Answer 9

We pass parameters into the procedure build by OLA, with this percentage and the job calculates what is low, medium or high. Default being 5% 30 % and above for low, medium and high. Probably, behind the scenes he is taking in consideration the fill factor and the pages. What do you think?