1

I've been reading about SQL Server 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.

4

If you're new to the DBA role then go to this site:

OLA

What you're trying to do has already been done.

Any DBA should have this site on their list of favourites.

| improve this answer | |
  • Ostensibly, this advice applies even if you're not new to the DBA role. Ola Hallengren's scripts are indispensable. – Max Vernon Jan 3 '17 at 17:58

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.