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!

0

2 Answers 2

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).

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?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.