0

I posted this in StackExchange and it was mentioned that it should be moved to this exchange. So here it is:

I've been reading about SQL Index Fragmentation and how to rebuild/reorganize. I'm curious on how to write a script about it. I have a script that will generate the table with the table index and display the fragmentation in percent:

SELECT 
    dbschemas.[name] as 'Schema',
    dbtables.[name] as 'Table',
    dbindexes.[name] as 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN 
    sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN 
    sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN 
    sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
                             AND indexstats.index_id = dbindexes.index_id
WHERE 
    indexstats.database_id = DB_ID()
ORDER BY 
    indexstats.avg_fragmentation_in_percent desc

Can I add ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REBUILD to this script or do I have to write a new script.

I found http://sqlfool.com/2011/06/index-defrag-script-v4-1/ but am not sure on how I use it properly.

I'm new to database administration and am trying to setup a maintenance plan that will rebuild indexes when they hit a certain threshold.

Thank you in advance...Cheers!

2

That page count number is really not a thumb rule, it's more an arbitrary one. I am not able to recall Paul Randal's blog where he has explained that this is a made up number to set up a threshold.

For bigger tables yes you can have the Ola's job run as per the maintenance schedule you have. For smaller ones having page count <1000 should not be a concern unless you see some major performance blips because this operation comes with a extra cost in terms of resource utilization.

Also as far as I can recall rebuilding small indexes might not help in lowering the fragmentation due to allocation of mixed extents, unless you have the trace flag for that enabled (trace flag 1118).

| improve this answer | |
1

After more research I was able to find that the number of pages were too small. I adjusted the script provided through Ola Hallengren and was able to get it to run properly. In the script the default value for @PageCountLevel is 1000. I simply changed this to a lower number to allow for it to run. I also set the script to run on a weekly basis per request from my direct supervisor.

Should a script like this be run on a weekly basis?

| improve this answer | |

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.