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”
Dummies Guide to SQL for THIS dummy 