2,187
edits
Difference between revisions of "MS SQL"
Jump to navigation
Jump to search
→Fragmentation: Added "Rebuild All Indexes"
(→Commands and Maintenance: Added "Memory Usage") |
(→Fragmentation: Added "Rebuild All Indexes") |
||
| Line 217: | Line 217: | ||
ALTER INDEX IndexName ON TableName REORGANIZE; /* Online */ | ALTER INDEX IndexName ON TableName REORGANIZE; /* Online */ | ||
ALTER INDEX IndexName ON TableName REBUILD; /* Offline */ | ALTER INDEX IndexName ON TableName REBUILD; /* Offline */ | ||
ALTER INDEX IndexName ON TableName REBUILD WITH (ONLINE = ON); | ALTER INDEX IndexName ON TableName REBUILD WITH (ONLINE = ON); /* Online - 2008 Ent and above only */ | ||
</source> | </source> | ||
| Line 233: | Line 233: | ||
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id | INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id | ||
WHERE indexstats.avg_fragmentation_in_percent > 50; | WHERE indexstats.avg_fragmentation_in_percent > 50; | ||
</source> | |||
==== Rebuild All Indexes ==== | |||
Unless you are running SQL Enterprise, this needs to be run when your database is '''offline''' | |||
<source lang="sql"> | |||
USE DatabaseName /* Change to database name! */ | |||
DECLARE @TableName varchar(255) | |||
DECLARE TableCursor CURSOR FOR | |||
SELECT table_name FROM information_schema.tables | |||
WHERE table_type = 'base table' | |||
OPEN TableCursor | |||
FETCH NEXT FROM TableCursor INTO @TableName | |||
WHILE @@FETCH_STATUS = 0 | |||
BEGIN | |||
DBCC DBREINDEX(@TableName,' ',0) | |||
FETCH NEXT FROM TableCursor INTO @TableName | |||
END | |||
CLOSE TableCursor | |||
DEALLOCATE TableCursor | |||
</source> | </source> | ||
[[Category:Applications]] | [[Category:Applications]] | ||
[[Category:Microsoft]] | [[Category:Microsoft]] | ||