I've come across a situation where my database operations on many of my SQL Server database's tables have become very slow as more records have been added (5s for single insert to a table with 1 million records).
I've estimated that this may be due to fragmented indexes on tables in my database, because I have many tables that use (and need to use) a uniqueidentifier type for Primary Key clustered indexes.
How can I evaluate whether this is the case or not, and how can I resolve the fragmentation issues (perhaps once per deployment) if there are any fragmentation issues?
I would like a solution that works in SQL Server 2005 and higher (I am specifically working with SQL Server in an Azure database (12.0.2000.8)).
Here is a SQL query solution that works in SQL Server 2005 and up, that will let you
1) first find all the indexes that need to be rebuilt or reorganized to reduce fragmentation, and then
2) with a single copy-paste of the first five columns of the results to a new query window (removing the column header line), execute all the statements (rebuild/reorganize of indexes) that will resolve the majority of the current fragmentation issues in all tables in your database.
Note: If you run into permission errors, you may need to make sure you are positioned in the master schema and that your user has appropriate permissions for the database.
I named this query: GetFragmentationOfIndexesAndFirst5ColumnsExecutedResolveFragmentation.sql
SELECT
'alter index' as 'reindex_part1',
'[' + dbindexes.[name] + ']' as 'Index',
'on' as 'reindex_part2',
'[' + dbtables.[name] + ']' as 'Table',
CASE WHEN indexstats.avg_fragmentation_in_percent > 30
THEN 'rebuild with (FILLFACTOR = 80)' ELSE 'reorganize' END as 'reindex_part3',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count,
indexstats.alloc_unit_type_desc,
dbschemas.[name] as 'Schema'
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()
AND indexstats.avg_fragmentation_in_percent > 5
ORDER BY indexstats.avg_fragmentation_in_percent desc
I have to credit two places I used in understanding and ultimately coming to this solution:
Initial way to find fragmentation within a database: https://myadventuresincoding.wordpress.com/2013/05/27/sql-server-check-index-fragmentation-on-all-indexes-in-a-database/
How to resolve fragmentation within a database (and guideline of 5%-30% fragmentation should be resolved with reorganize of index, and 30%+ fragmentation should be resolved with rebuild of index): http://www.passionforsql.com/how-to-check-index-fragmentation-in-sql-server/
EDIT: I've included the with (FILLFACTOR = 80) part in the query above because in my case, the majority of the fragmented indexes were on uniqueidentifier columns, which should not be indexed with the default FILLFACTOR of 0 (100%) because having them that way will inevitably cause fragmentation quickly again because inserts will always need to be put between other rows due to the non-ordered creation of uniqueidentifiers. You can certainly change your pasted values to remove or change the parameters as is appropriate for your tables/indexes.
I've also found that you'll want to execute EXEC sp_updatestats after rebuilding and reorganizing indexes so that the statistics can catch up with the index changes rather than having to do so incrementally during future queries.