| hoctinhoc |
21-10-2015 02:51 PM |
Lên kế hoạch kiểm tra Fragmentation và Rebuild Index Table trong database
Lên kế hoạch kiểm tra Fragmentation và Rebuild Index Table trong database
1. Kiểm Tra Fragmentation
http://blogs.technet.com/b/sql_serve...-t-fix-it.aspx
http://basitaalishan.com/2012/06/18/...rver-database/
http://sqldbpool.com/2012/03/24/how-...in-sql-server/
2. Rebuild or Reorganize Index Table
http://sqldbpool.com/2012/03/24/how-...ntenance-plan/
Rebbuild all table
http://www.foliotek.com/devblog/sql-...ex-rebuilding/
--
Chạy sript sau để xác định xem Table nào cần Rebuild Index
Trích dẫn:
DECLARE @page_count_minimum smallint
DECLARE @fragmentation_minimum float
SET @page_count_minimum = 50
SET @fragmentation_minimum = 30
SELECT
sys.objects.name AS Table_Name,
sys.indexes.name AS Index_Name,
avg_fragmentation_in_percent AS frag,
page_count AS page_count,
sys.dm_db_index_physical_stats.object_id AS objectid,
sys.dm_db_index_physical_stats.index_id AS indexid,
partition_number AS partitionnum
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
inner join sys.objects
on sys.objects.object_id = sys.dm_db_index_physical_stats.object_id
inner join sys.indexes
on sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id
and sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id
WHERE avg_fragmentation_in_percent > @fragmentation_minimum
AND sys.dm_db_index_physical_stats.index_id > 0
AND page_count > @page_count_minimum
ORDER BY page_count DESC
|
SQL Server – Check Index Fragmentation on ALL Indexes in a Database
Mã:
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
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()
ORDER BY indexstats.avg_fragmentation_in_percent desc
:battay: |
