[SQL]Index Rebuild And Reorg
整理一下之前找的資料
-- http://stackoverflow.com/questions/7579/reorganise-index-vs-rebuild-index-in-sql-server-maintenance-plan
-- Reorganize and Rebuild Indexes
-- http://msdn.microsoft.com/en-us/library/ms189858.aspx
-- Microsoft SQL Server 2000 Index Defragmentation Best Practices
-- http://technet.microsoft.com/en-us/library/cc966523.aspx
-- [SQL]查看Table Index的Fragmentation
-- 如果fragmentation在5~30之間的話，可使用Reorganize!
-- 如果fragmentation大於30的話，那建議Rebuild Index!
-- http://www.dotblogs.com.tw/rainmaker/archive/2012/06/13/72766.aspx
-- SQL數據優化(下)
-- http://www.cnblogs.com/Wendy_Yu/archive/2012/06/17/SQL%e6%80%a7%e8%83%bd_%e5%a4%a7%e6%95%b0%e6%8d%ae%e4%bc%98%e5%8c%96_%e5%88%86%e5%8c%ba%e8%a1%a8.html
-- 查詢資料庫的索引資訊
USE master
GO
DECLARE @dbname nvarchar(50)
SET @dbname = N'master';
CREATE TABLE #INDEXFRAGCAL (
dbname sysname,
tablename sysname,
index_id sysname,
name sysname null,
type_desc sysname,
avg_fragmentation_in_percent sysname
)
DECLARE @SqlString nvarchar(3000);
SELECT @SqlString = 'insert into #INDEXFRAGCAL SELECT ''' + @dbname + ''' as dbname,''?'' as tablename, a.index_id, name,b.type_desc, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N''' + @dbname + '''), OBJECT_ID(N''?''), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; ';
EXEC sys.sp_MSforeachtable @SqlString
SELECT *
FROM
#INDEXFRAGCAL
WHERE
type_desc <> 'HEAP'
ORDER BY
tablename
DROP TABLE #INDEXFRAGCAL
-- 在指令碼中使用 sys.dm_db_index_physical_stats 來重建或重新組織索引
-- 下列範例會自動重新組織或重建資料庫中所有具備 10% 以上平均片段的資料分割。
-- 執行這項查詢需要 VIEW DATABASE STATE 權限。 這個範例會指定第一個參數為 DB_ID，
-- 而不指定資料庫名稱。 如果目前資料庫的相容性層級是 80 或更低，將會產生錯誤。
-- 若要解決此錯誤，請使用有效的資料庫名稱來取代 DB_ID()。
-- 如需有關資料庫相容性層級的詳細資訊，請參閱＜ALTER DATABASE 相容性層級 (Transact-SQL)＞。
-- http://msdn.microsoft.com/zh-tw/library/ms188917.aspx
USE master
GO
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT object_id AS objectid
, index_id AS indexid
, partition_number AS partitionnum
, avg_fragmentation_in_percent AS frag
INTO
#work_to_do
FROM
sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'LIMITED')
WHERE
avg_fragmentation_in_percent > 10.0
AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
SELECT *
FROM
#work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = quotename(o.name)
, @schemaname = quotename(s.name)
FROM
sys.objects AS o
JOIN sys.schemas AS s
ON s.schema_id = o.schema_id
WHERE
o.object_id = @objectid;
SELECT @indexname = quotename(name)
FROM
sys.indexes
WHERE
object_id = @objectid
AND index_id = @indexid;
SELECT @partitioncount = count(*)
FROM
sys.partitions
WHERE
object_id = @objectid
AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + cast(@partitionnum AS NVARCHAR(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
-------------------------------------------------------------------------------------------------
-- 查詢索引碎裂(index fragmentation)
DBCC SHOWCONTIG
-- 將所有表格的index rebuild
USE master
GO
EXEC sp_MSforeachtable @command1 = "print '?' DBCC DBREINDEX ('?',' ',90)"
GO
EXEC sp_updatestats
GO
-- 更新統計值
EXEC sp_MsForeachdb
-- http://www.codeproject.com/Articles/398876/SQL-Server-and-Duplicate-Indexes
USE master
GO
SELECT sch.name + '.' + t.name AS [Table Name]
, i.name AS [Index Name]
, i.type_desc
, isnull(user_updates, 0) AS [Total Writes]
, isnull(user_seeks + user_scans + user_lookups, 0) AS [Total Reads]
, s.last_user_seek
, s.last_user_scan
, s.last_user_lookup
, isnull(user_updates, 0) - isnull((user_seeks + user_scans + user_lookups), 0) AS [Difference]
, p.reserved_page_count * 8.0 / 1024 AS SpaceInMB
FROM
sys.indexes AS i WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
ON s.object_id = i.object_id AND i.index_id = s.index_id AND s.database_id = db_id() AND objectproperty(s.object_id, 'IsUserTable') = 1
INNER JOIN sys.tables AS t WITH (NOLOCK)
ON i.object_id = t.object_id
INNER JOIN sys.schemas AS sch WITH (NOLOCK)
ON t.schema_id = sch.schema_id
LEFT OUTER JOIN sys.dm_db_partition_stats AS p WITH (NOLOCK)
ON i.index_id = p.index_id AND i.object_id = p.object_id
WHERE
(1 = 1)
--AND ISNULL(user_updates,0) >= ISNULL((user_seeks + user_scans + user_lookups),0) --shows all indexes including those that have not been used
--AND ISNULL(user_updates,0) - ISNULL((user_seeks + user_scans + user_lookups),0)>0 --only shows those indexes which have been used
--AND i.index_id > 1 -- Only non-first indexes (I.E. non-primary key)
--AND i.is_primary_key<>1 -- Only those that are not defined as a Primary Key)
--AND i.is_unique_constraint<>1 -- Only those that are not classed as "UniqueConstraints".
ORDER BY
[Table Name]
, [Index Name]
/* This script will generate 3 reports that give an overall or high level
view of the indexes in a particular database. The sections are as follows:
1. Lists ALL indexes and constraints along with the key details of each
(列出全部index資訊)
2. Lists any tables with potential Redundant indexes
(列出重複的index資訊)
3. Lists any tables with potential Reverse indexes
(列出反向的index資訊)
*/
-- Create a table variable to hold the core index info
DECLARE @AllIndexes TABLE (
[Table ID] [int] NOT NULL,
[Schema] [sysname] NOT NULL,
[Table Name] [sysname] NOT NULL,
[Index ID] [int] NULL,
[Index Name] [nvarchar](128) NULL,
[Index Type] [varchar](12) NOT NULL,
[Constraint Type] [varchar](11) NOT NULL,
[Object Type] [varchar](10) NOT NULL,
[AllColName] [nvarchar](2078) NULL,
[ColName1] [nvarchar](128) NULL,
[ColName2] [nvarchar](128) NULL,
[ColName3] [nvarchar](128) NULL,
[ColName4] [nvarchar](128) NULL,
[ColName5] [nvarchar](128) NULL,
[ColName6] [nvarchar](128) NULL,
[ColName7] [nvarchar](128) NULL,
[ColName8] [nvarchar](128) NULL,
[ColName9] [nvarchar](128) NULL,
[ColName10] [nvarchar](128) NULL
)
-- Load up the table variable with the index information to be used in follow on queries
INSERT INTO @AllIndexes ([Table ID]
, [Schema]
, [Table Name]
, [Index ID]
, [Index Name]
, [Index Type]
, [Constraint Type]
, [Object Type]
, [AllColName]
, [ColName1]
, [ColName2]
, [ColName3]
, [ColName4]
, [ColName5]
, [ColName6]
, [ColName7]
, [ColName8]
, [ColName9]
, [ColName10])
SELECT o.[object_id] AS [Table ID]
, u.[name] AS [Schema]
, o.[name] AS [Table Name]
, i.[index_id] AS [Index ID]
, CASE i.[name]
WHEN o.[name] THEN
'** Same as Table Name **'
ELSE
i.[name]
END AS [Index Name]
, CASE i.[type]
WHEN 1 THEN
'CLUSTERED'
WHEN 0 THEN
'HEAP'
WHEN 2 THEN
'NONCLUSTERED'
WHEN 3 THEN
'XML'
ELSE
'UNKNOWN'
END AS [Index Type]
, CASE
WHEN (i.[is_primary_key]) = 1 THEN
'PRIMARY KEY'
WHEN (i.[is_unique]) = 1 THEN
'UNIQUE'
ELSE
''
END AS [Constraint Type]
, CASE
WHEN (i.[is_unique_constraint]) = 1 OR (i.[is_primary_key]) = 1 THEN
'CONSTRAINT'
WHEN i.[type] = 0 THEN
'HEAP'
WHEN i.[type] = 3 THEN
'XML INDEX'
ELSE
'INDEX'
END AS [Object Type]
, (SELECT coalesce(c1.[name], '')
FROM
[sys].[columns] AS c1
INNER JOIN [sys].[index_columns] AS ic1
ON c1.[object_id] = ic1.[object_id] AND c1.[column_id] = ic1.[column_id] AND ic1.[key_ordinal] = 1
WHERE
ic1.[object_id] = i.[object_id]
AND ic1.[index_id] = i.[index_id]) + CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 2) IS NULL THEN
''
ELSE
', ' + index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 2)
END + CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 3) IS NULL THEN
''
ELSE
', ' + index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 3)
END + CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 4) IS NULL THEN
''
ELSE
', ' + index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 4)
END + CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 5) IS NULL THEN
''
ELSE
', ' + index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 5)
END + CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 6) IS NULL THEN
''
ELSE
', ' + index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 6)
END + CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 7) IS NULL THEN
''
ELSE
', ' + index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 7)
END + CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 8) IS NULL THEN
''
ELSE
', ' + index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 8)
END + CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 9) IS NULL THEN
''
ELSE
', ' + index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 9)
END + CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 10) IS NULL THEN
''
ELSE
', ' + index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 10)
END AS [AllColName]
, (SELECT coalesce(c1.[name], '')
FROM
[sys].[columns] AS c1
INNER JOIN [sys].[index_columns] AS ic1
ON c1.[object_id] = ic1.[object_id] AND c1.[column_id] = ic1.[column_id] AND ic1.[key_ordinal] = 1
WHERE
ic1.[object_id] = i.[object_id]
AND ic1.[index_id] = i.[index_id]) AS [ColName1]
, CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 2) IS NULL THEN
''
ELSE
index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 2)
END AS [ColName2]
, CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 3) IS NULL THEN
''
ELSE
index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 3)
END AS [ColName3]
, CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 4) IS NULL THEN
''
ELSE
index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 4)
END AS [ColName4]
, CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 5) IS NULL THEN
''
ELSE
index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 5)
END AS [ColName5]
, CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 6) IS NULL THEN
''
ELSE
index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 6)
END AS [ColName6]
, CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 7) IS NULL THEN
''
ELSE
index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 7)
END AS [ColName7]
, CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 8) IS NULL THEN
''
ELSE
index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 8)
END AS [ColName8]
, CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 9) IS NULL THEN
''
ELSE
index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 9)
END AS [ColName9]
, CASE
WHEN index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 10) IS NULL THEN
''
ELSE
index_col('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 10)
END AS [ColName10]
FROM
[sys].[objects] AS o WITH (NOLOCK)
LEFT OUTER JOIN [sys].[indexes] AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
JOIN [sys].[schemas] AS u WITH (NOLOCK)
ON o.[schema_id] = u.[schema_id]
WHERE
o.[type] = 'U' --AND i.[index_id] < 255
AND o.[name] NOT IN ('dtproperties')
AND i.[name] NOT LIKE '_WA_Sys_%'
-----------
SELECT 'Listing All Indexes' AS [Comments]
SELECT I.*
FROM
@AllIndexes AS I
ORDER BY
[Table Name]
-----------
SELECT 'Listing Possible Redundant Index keys' AS [Comments]
SELECT DISTINCT I.[Table Name]
, I.[Index Name]
, I.[Index Type]
, I.[Constraint Type]
, I.[AllColName]
FROM
@AllIndexes AS I
JOIN @AllIndexes AS I2
ON I.[Table ID] = I2.[Table ID] AND I.[ColName1] = I2.[ColName1] AND I.[Index Name] <> I2.[Index Name] AND I.[Index Type] <> 'XML'
ORDER BY
I.[Table Name]
, I.[AllColName]
----------
SELECT 'Listing Possible Reverse Index keys' AS [Comments]
SELECT DISTINCT I.[Table Name]
, I.[Index Name]
, I.[Index Type]
, I.[Constraint Type]
, I.[AllColName]
FROM
@AllIndexes AS I
JOIN @AllIndexes AS I2
ON I.[Table ID] = I2.[Table ID] AND I.[ColName1] = I2.[ColName2] AND I.[ColName2] = I2.[ColName1] AND I.[Index Name] <> I2.[Index Name] AND I.[Index Type] <> 'XML'