Chia Sẽ Kinh Nghiệm Về IT



Tìm Kiếm Với Google
-


Gởi Ðề Tài Mới  Gửi trả lời
 
Công Cụ Xếp Bài
Tuổi 21-10-2015, 02:51 PM   #1
hoctinhoc
Guest
 
Trả Lời: n/a
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
  Trả lời ngay kèm theo trích dẫn này
Gửi trả lời


Công Cụ
Xếp Bài

Quyền Hạn Của Bạn
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is Mở
Hình Cảm xúc đang Mở
[IMG] đang Mở
Mã HTML đang Tắt




Bây giờ là 04:53 PM. Giờ GMT +7



Diễn đàn tin học QuantriNet
quantrinet.com | quantrimang.co.cc
Founded by Trương Văn Phương | Developed by QuantriNet's members.
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.