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.
Best Answer
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.