rebuild indexes

We wanted tor rebuild the indexes on a database on one of our clustered db servers.  The options were to use a management plan or manually use the SSMS GUI to rebuild or run it from the query window.

To determine how many indexes we had, this query was run

SELECT * FROM sys.dm_db_index_physical_stats (6, NULL, NULL, NULL, NULL);
GO

where 6 was the db_id for the database.  That query  returned 264 items, like thhis:

database_id
object_id
index_id
partition_number
index_type_desc
alloc_unit_type_desc
index_depth
index_level
avg_fragmentation_in_percent
fragment_count
avg_fragment_size_in_pages
page_count
avg_page_space_used_in_percent
record_count
ghost_record_count
version_ghost_record_count
min_record_size_in_bytes
max_record_size_in_bytes 
avg_record_size_in_bytes 
forwarded_record_count 
compressed_page_count 6 5575058 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 90 10 1 10 NULL NULL NULL NULL NULL NULL NULL NULL NULL
6 30623152 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 3.92156862745098 9 5.66666666666667 51 NULL NULL NULL NULL NULL NULL NULL NULL NULL

A smaller query on a different database is this and returns the following 

SELECT database_id, object_id, index_type_desc, avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats (5, NULL, NULL, NULL, NULL);
GO database_id object_id index_type_desc avg_fragmentation_in_percent
5 21575115 HEAP 0
5 53575229 CLUSTERED INDEX 99.8394863563403
5 53575229 NONCLUSTERED INDEX 99.6
5 85575343 CLUSTERED INDEX 0
5 85575343 NONCLUSTERED INDEX 0
5 117575457 CLUSTERED INDEX 0
5 2073058421 CLUSTERED INDEX 0
5 2105058535 CLUSTERED INDEX 99.1930948480329

Fine.  We got data to compare afterwards

To rebuild the indices, run this t-sql statement

use MY_DB
EXEC sp_MSForEachTable ‘Print ”Rebuild index on: ?”; ALTER INDEX ALL ON ? REBUILD;’

This does a foreach over each table in the database MY_DB and executes ALTER INDEX ALL on TABLENAME REBUILD

On the target database, the query was started.  The server was enterprise so the query could run on-line.  However after 30 minutes, the application owner complained that the application (sharePoint) was effectively unreachable/very sluggish, so i stopped the query.  It took nearly one hour to rollback the query – which had gotten through about 90 or so tables. Note that many tables had multiple indexes.  

The CPU never got much above 5% but the locking of the tables and the rebuilding of the indexes pretty much made everything not work.  

This query

USE myq1_site_pair
GO
SELECT so.name AS TableName, si.name AS IndexName, si.type_desc AS IndexType
FROM sys.indexes si
JOIN sys.objects so ON si.[object_id] = so.[object_id]
WHERE so.type = 'U' --Only get indexes for User Created Tables
AND si.name IS NOT NULL
ORDER BY so.name, si.type

returned 229 entries that looked like this:

TableName IndexName IndexType
AllDocs AllDocs_ParentId CLUSTERED
AllDocs Docs_IdLevelUnique NONCLUSTERED
AllDocs AllDocs_Url NONCLUSTERED
AllDocStreams AllDocStreams_CI CLUSTERED
AllDocStreams AllDocStreams_RbsId NONCLUSTERED

from this, we can do the following in a script

USE MyQ1_Site_Pair;
GO
ALTER INDEX WorkflowAssociation ON dbo.WorkflowAssociation_Parent
REBUILD;
GO

 The typical statement is to rebuild indexes over 30% fragmentation and if more than 5% (less than 30%) reorganize it.  The syntax is identical, the different in the alter statement is “reorganize” and not “rebuild”

Advertisements