2,187
edits
Difference between revisions of "MS SQL"
Jump to navigation
Jump to search
→Commands and Maintenance: Added Fragmentation
(Re-ordered sections) |
(→Commands and Maintenance: Added Fragmentation) |
||
| Line 145: | Line 145: | ||
</source> | </source> | ||
=== Fragmentation === | |||
List index fragmentation within a particular database... | |||
<source lang="sql"> | |||
USE database_name; | |||
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, | |||
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, | |||
indexstats.avg_fragmentation_in_percent | |||
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats | |||
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 | |||
ORDER BY indexstats.avg_fragmentation_in_percent DESC; | |||
</source> | |||
Indexes identified as being fragmented can defrag'ed by using either of the following command... | |||
<source lang="sql"> | |||
ALTER INDEX IndexName ON TableName REORGANIZE; /* Online */ | |||
ALTER INDEX IndexName ON TableName REBUILD; /* Offline */ | |||
</source> | |||
Alternatively, if you've got lots of indexes to defrag, you can create the REORGANIZE statements... | |||
<source lang="sql"> | |||
SELECT 'ALTER INDEX ' + ind.name + ' ON ' + OBJECT_NAME(ind.OBJECT_ID) + ' REORGANIZE;' | |||
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats | |||
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; | |||
</source> | |||
...or the REBUILD statements... | |||
<source lang="sql"> | |||
SELECT 'ALTER INDEX ' + ind.name + ' ON ' + OBJECT_NAME(ind.OBJECT_ID) + ' REBUILD;' | |||
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats | |||
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; | |||
</source> | |||
[[Category:Applications]] | [[Category:Applications]] | ||
[[Category:Microsoft]] | [[Category:Microsoft]] | ||