Quản trị net diễn đàn chia sẻ thông tin các thủ thuật mạng, internet bảo mật thông tin dành cho giới IT VIệt hy vọng là nơi bổ ích cho cộng đồng - View Single Post - Lên kế hoạch kiểm tra Fragmentation và Rebuild Index Table trong database

21-10-2015, 02:51 PM

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