반응형
데이터베이스의 모든 색인 재구성
매우 큰 SQL Server 2008 R2 데이터베이스를 가지고 있다(1.5).TB)와 같은 테이블 내의 열에서 열로 일부 데이터를 복사합니다.스키마에 많은 인덱스가 있다고 들었는데 모든 인덱스를 재구축할 기본 쿼리나 스크립트가 있는지 궁금합니다.통계를 동시에 업데이트하라는 조언도 받았습니까?
30개의 테이블 각각에 클러스터된 인덱스가 1개 있고 비클러스터된 인덱스가 13개 있습니다.
감사해요.
다음 스크립트를 사용해 보십시오.
Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'
GO
또한.
다음 스크립트를 사용하는 것이 좋습니다(긴 검색 후). 각 리프 레벨 페이지의 빈 영역이 데이터로 채워지는 비율을 결정합니다.
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))+'.' + QUOTENAME(name) AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
자세한 내용은 다음 링크를 참조하십시오.
데이터베이스의 색인에서 색인 조각화를 검사하려면 다음 스크립트를 사용하십시오.
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() AND dbtables.[name] like '%%'
ORDER BY indexstats.avg_fragmentation_in_percent desc
자세한 내용은 다음 링크를 참조하십시오.
http://www.schneider-electric.com/en/faqs/FA234246/
아래 쿼리에서 "YOUR DATABASE NAME"을 바꿉니다.
DECLARE @Database NVARCHAR(255)
DECLARE @Table NVARCHAR(255)
DECLARE @cmd NVARCHAR(1000)
DECLARE DatabaseCursor CURSOR READ_ONLY FOR
SELECT name FROM master.sys.databases
WHERE name IN ('YOUR DATABASE NAME') -- databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'
PRINT @cmd -- uncomment if you want to see commands
EXEC (@cmd)
END TRY
BEGIN CATCH
PRINT '---'
PRINT @cmd
PRINT ERROR_MESSAGE()
PRINT '---'
END CATCH
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
목록에 추가할 다른 스크립트가 있습니다.다른 것들과의 주요 차이점은 30% 이상의 플래그멘테이션과 5% 이상의 재구성 시 재구축에 대한 일반적인 권장 임계값을 적용한다는 것입니다.아무것도 끝나지 않으면 아무 일도 일어나지 않는다.따라서 원하는 만큼 이 스크립트를 실행할 수 있습니다.
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
DECLARE @TableName varchar(255);
DECLARE @IndexName varchar(255);
DECLARE @Fragmentation FLOAT;
DECLARE @IndexScript varchar(255);
SELECT
dbtables.[name],
dbindexes.[name],
indexstats.avg_fragmentation_in_percent,
indexstats.page_count [pages]
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()
AND indexstats.avg_fragmentation_in_percent >= 5.0
AND indexstats.page_count > 10
ORDER BY
indexstats.page_count ASC,
indexstats.avg_fragmentation_in_percent ASC
DECLARE TableCursor CURSOR FOR
SELECT
dbtables.[name],
dbindexes.[name],
indexstats.avg_fragmentation_in_percent
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()
AND indexstats.avg_fragmentation_in_percent >= 5.0
AND indexstats.page_count > 10
ORDER BY
indexstats.page_count ASC,
indexstats.avg_fragmentation_in_percent ASC;
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO
@TableName,
@IndexName,
@Fragmentation
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@Fragmentation >= 30.0)
SET @IndexScript = 'ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD';
ELSE IF (@Fragmentation >= 5.0)
SET @IndexScript = 'ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REORGANIZE';
ELSE
SET @IndexScript = NULL;
IF (@IndexScript IS NOT NULL)
BEGIN
RAISERROR (@IndexScript, 10, 0) WITH NOWAIT
WAITFOR DELAY '00:00:01';
EXEC(@IndexScript);
END
FETCH NEXT FROM TableCursor INTO
@TableName,
@IndexName,
@Fragmentation;
END
CLOSE TableCursor;
DEALLOCATE TableCursor;
몇 년째 쓰고 있어요.이 스크립트는 테이블과 메시지를 출력합니다.즉, 시간이 너무 오래 걸리는 것 같으면 SQL Management Studio에서 실행 중인 작업을 반실시간으로 볼 수 있습니다.
DECLARE @Database NVARCHAR(255)
DECLARE @Table NVARCHAR(255)
DECLARE @cmd NVARCHAR(1000)
DECLARE DatabaseCursor CURSOR READ_ONLY FOR
SELECT name FROM master.sys.databases
WHERE name NOT IN ('master','msdb','tempdb','model','distribution') -- databases to exclude
--WHERE name IN ('DB1', 'DB2') -- use this to select specific databases and comment out line above
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'
--PRINT @cmd -- uncomment if you want to see commands
EXEC (@cmd)
END TRY
BEGIN CATCH
PRINT '---'
PRINT @cmd
PRINT ERROR_MESSAGE()
PRINT '---'
END CATCH
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
좋은 스크립트입니다.노트북의 메모리는 부족하지만, 이것은 매우 큰 테이블 위에 있었습니다.
https://basitaalishan.com/2014/02/23/rebuild-all-indexes-on-all-tables-in-the-sql-server-database/
USE [<mydatabasename>]
Go
--/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--Arguments Data Type Description
-------------- ------------ ------------
--@FillFactor [int] Specifies a percentage that indicates how full the Database Engine should make the leaf level
-- of each index page during index creation or alteration. The valid inputs for this parameter
-- must be an integer value from 1 to 100 The default is 0.
-- For more information, see http://technet.microsoft.com/en-us/library/ms177459.aspx.
--@PadIndex [varchar](3) Specifies index padding. The PAD_INDEX option is useful only when FILLFACTOR is specified,
-- because PAD_INDEX uses the percentage specified by FILLFACTOR. If the percentage specified
-- for FILLFACTOR is not large enough to allow for one row, the Database Engine internally
-- overrides the percentage to allow for the minimum. The number of rows on an intermediate
-- index page is never less than two, regardless of how low the value of fillfactor. The valid
-- inputs for this parameter are ON or OFF. The default is OFF.
-- For more information, see http://technet.microsoft.com/en-us/library/ms188783.aspx.
--@SortInTempDB [varchar](3) Specifies whether to store temporary sort results in tempdb. The valid inputs for this
-- parameter are ON or OFF. The default is OFF.
-- For more information, see http://technet.microsoft.com/en-us/library/ms188281.aspx.
--@OnlineRebuild [varchar](3) Specifies whether underlying tables and associated indexes are available for queries and data
-- modification during the index operation. The valid inputs for this parameter are ON or OFF.
-- The default is OFF.
-- Note: Online index operations are only available in Enterprise edition of Microsoft
-- SQL Server 2005 and above.
-- For more information, see http://technet.microsoft.com/en-us/library/ms191261.aspx.
--@DataCompression [varchar](4) Specifies the data compression option for the specified index, partition number, or range of
-- partitions. The options for this parameter are as follows:
-- > NONE - Index or specified partitions are not compressed.
-- > ROW - Index or specified partitions are compressed by using row compression.
-- > PAGE - Index or specified partitions are compressed by using page compression.
-- The default is NONE.
-- Note: Data compression feature is only available in Enterprise edition of Microsoft
-- SQL Server 2005 and above.
-- For more information about compression, see http://technet.microsoft.com/en-us/library/cc280449.aspx.
--@MaxDOP [int] Overrides the max degree of parallelism configuration option for the duration of the index
-- operation. The valid input for this parameter can be between 0 and 64, but should not exceed
-- number of processors available to SQL Server.
-- For more information, see http://technet.microsoft.com/en-us/library/ms189094.aspx.
--- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*/
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @Version [numeric] (18, 10)
,@SQLStatementID [int]
,@CurrentTSQLToExecute [nvarchar](max)
,@FillFactor [int] = 100 -- Change if needed
,@PadIndex [varchar](3) = N'OFF' -- Change if needed
,@SortInTempDB [varchar](3) = N'OFF' -- Change if needed
,@OnlineRebuild [varchar](3) = N'OFF' -- Change if needed
,@LOBCompaction [varchar](3) = N'ON' -- Change if needed
,@DataCompression [varchar](4) = N'NONE' -- Change if needed
,@MaxDOP [int] = NULL -- Change if needed
,@IncludeDataCompressionArgument [char](1);
IF OBJECT_ID(N'TempDb.dbo.#Work_To_Do') IS NOT NULL
DROP TABLE #Work_To_Do
CREATE TABLE #Work_To_Do
(
[sql_id] [int] IDENTITY(1, 1)
PRIMARY KEY ,
[tsql_text] [varchar](1024) ,
[completed] [bit]
)
SET @Version = CAST(LEFT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)), CHARINDEX('.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128))) - 1) + N'.' + REPLACE(RIGHT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)), LEN(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128))) - CHARINDEX('.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)))), N'.', N'') AS [numeric](18, 10))
IF @DataCompression IN (N'PAGE', N'ROW', N'NONE')
AND (
@Version >= 10.0
AND SERVERPROPERTY(N'EngineEdition') = 3
)
BEGIN
SET @IncludeDataCompressionArgument = N'Y'
END
IF @IncludeDataCompressionArgument IS NULL
BEGIN
SET @IncludeDataCompressionArgument = N'N'
END
INSERT INTO #Work_To_Do ([tsql_text], [completed])
SELECT 'ALTER INDEX [' + i.[name] + '] ON' + SPACE(1) + QUOTENAME(t2.[TABLE_CATALOG]) + '.' + QUOTENAME(t2.[TABLE_SCHEMA]) + '.' + QUOTENAME(t2.[TABLE_NAME]) + SPACE(1) + 'REBUILD WITH (' + SPACE(1) + + CASE
WHEN @PadIndex IS NULL
THEN 'PAD_INDEX =' + SPACE(1) + CASE i.[is_padded]
WHEN 1
THEN 'ON'
WHEN 0
THEN 'OFF'
END
ELSE 'PAD_INDEX =' + SPACE(1) + @PadIndex
END + CASE
WHEN @FillFactor IS NULL
THEN ', FILLFACTOR =' + SPACE(1) + CONVERT([varchar](3), REPLACE(i.[fill_factor], 0, 100))
ELSE ', FILLFACTOR =' + SPACE(1) + CONVERT([varchar](3), @FillFactor)
END + CASE
WHEN @SortInTempDB IS NULL
THEN ''
ELSE ', SORT_IN_TEMPDB =' + SPACE(1) + @SortInTempDB
END + CASE
WHEN @OnlineRebuild IS NULL
THEN ''
ELSE ', ONLINE =' + SPACE(1) + @OnlineRebuild
END + ', STATISTICS_NORECOMPUTE =' + SPACE(1) + CASE st.[no_recompute]
WHEN 0
THEN 'OFF'
WHEN 1
THEN 'ON'
END + ', ALLOW_ROW_LOCKS =' + SPACE(1) + CASE i.[allow_row_locks]
WHEN 0
THEN 'OFF'
WHEN 1
THEN 'ON'
END + ', ALLOW_PAGE_LOCKS =' + SPACE(1) + CASE i.[allow_page_locks]
WHEN 0
THEN 'OFF'
WHEN 1
THEN 'ON'
END + CASE
WHEN @IncludeDataCompressionArgument = N'Y'
THEN CASE
WHEN @DataCompression IS NULL
THEN ''
ELSE ', DATA_COMPRESSION =' + SPACE(1) + @DataCompression
END
ELSE ''
END + CASE
WHEN @MaxDop IS NULL
THEN ''
ELSE ', MAXDOP =' + SPACE(1) + CONVERT([varchar](2), @MaxDOP)
END + SPACE(1) + ')'
,0
FROM [sys].[tables] t1
INNER JOIN [sys].[indexes] i ON t1.[object_id] = i.[object_id]
AND i.[index_id] > 0
AND i.[type] IN (1, 2)
INNER JOIN [INFORMATION_SCHEMA].[TABLES] t2 ON t1.[name] = t2.[TABLE_NAME]
AND t2.[TABLE_TYPE] = 'BASE TABLE'
INNER JOIN [sys].[stats] AS st WITH (NOLOCK) ON st.[object_id] = t1.[object_id]
AND st.[name] = i.[name]
SELECT @SQLStatementID = MIN([sql_id])
FROM #Work_To_Do
WHERE [completed] = 0
WHILE @SQLStatementID IS NOT NULL
BEGIN
SELECT @CurrentTSQLToExecute = [tsql_text]
FROM #Work_To_Do
WHERE [sql_id] = @SQLStatementID
PRINT @CurrentTSQLToExecute
EXEC [sys].[sp_executesql] @CurrentTSQLToExecute
UPDATE #Work_To_Do
SET [completed] = 1
WHERE [sql_id] = @SQLStatementID
SELECT @SQLStatementID = MIN([sql_id])
FROM #Work_To_Do
WHERE [completed] = 0
END
DECLARE @String NVARCHAR(MAX);
USE Databse Name;
SELECT @String
=
(
SELECT 'ALTER INDEX [' + dbindexes.[name] + '] ON [' + db.name + '].[' + dbschemas.[name] + '].[' + dbtables.[name]
+ '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' + CHAR(10) AS [text()]
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
INNER JOIN sys.databases AS db
ON db.database_id = indexstats.database_id
WHERE dbindexes.name IS NOT NULL
AND indexstats.database_id = DB_ID()
AND indexstats.avg_fragmentation_in_percent >= 10
ORDER BY indexstats.page_count DESC
FOR XML PATH('')
);
EXEC (@String);
Daniel의 스크립트는 모든 것을 망라하는 훌륭한 솔루션인 것처럼 보이지만, 그조차도 노트북의 메모리가 부족하다는 것을 인정했습니다.여기 제가 생각해낸 옵션이 있습니다.테크넷에 올린 모하마드 니자무딘의 글을 토대로 절차를 밟았어요모든 데이터베이스 이름을 임시 테이블로 가져온 후 이를 사용하여 각 데이터베이스에서 모든 기본 테이블 이름을 가져오는 첫 번째 커서 루프를 추가했습니다.
선택적으로 원하는 채우기 비율을 전달하고 모든 데이터베이스를 다시 색인화하지 않으려면 대상 데이터베이스를 지정할 수 있습니다.
--===============================================================
-- Name: sp_RebuildAllIndexes
-- Arguments: [Fill Factor], [Target Database name]
-- Purpose: Loop through all the databases on a server and
-- compile a list of all the table within them.
-- This list is then used to rebuild indexes for
-- all the tables in all the database. Optionally,
-- you may pass a specific database name if you only
-- want to reindex that target database.
--================================================================
CREATE PROCEDURE sp_RebuildAllIndexes(
@FillFactor INT = 90,
@TargetDatabase NVARCHAR(100) = NULL)
AS
BEGIN
DECLARE @TablesToReIndex TABLE (
TableName VARCHAR(200)
);
DECLARE @DbName VARCHAR(50);
DECLARE @TableSelect VARCHAR(MAX);
DECLARE @DatabasesToIndex CURSOR;
IF ISNULL( @TargetDatabase, '' ) = ''
SET @DatabasesToIndex = CURSOR
FOR SELECT NAME
FROM master..sysdatabases
ELSE
SET @DatabasesToIndex = CURSOR
FOR SELECT NAME
FROM master..sysdatabases
WHERE NAME = @TargetDatabase
OPEN DatabasesToIndex
FETCH NEXT FROM DatabasesToIndex INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TableSelect = 'INSERT INTO @TablesToReIndex SELECT CONCAT(TABLE_CATALOG, ''.'', TABLE_SCHEMA, ''.'', TABLE_NAME) AS TableName FROM '
+ @DbName
+ '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''base table''';
EXEC sp_executesql
@TableSelect;
FETCH NEXT FROM DatabasesToIndex INTO @DbName
END
CLOSE DatabasesToIndex
DEALLOCATE DatabasesToIndex
DECLARE @TableName VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT TableName
FROM @TablesToReIndex
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName, ' ', @FillFactor)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
END
이런, 모든 데이터베이스/테이블에 인덱스를 모두 재구축하는 것만으로 인덱스가 얼마나 단편화되어 있는지 알 수는 없습니다.당신은 큰 성과를 거두지 못하고 있습니다.인덱스별 플래그멘테이션 양을 확인하고 그에 따라 조치해야 합니다.일반적으로 15%를 초과하는 것은 재구성을 선택하는 30%까지 재구성할 가치가 있습니다.또한 페이지 수, 색인 크기 및 테이블의 활동에 따라 달라집니다.일부 테이블에는 항상 높은 플래그멘테이션이 존재하며, 이러한 테이블에서 성능 문제가 발생하지 않는 한 항상 추가/삭제만 수행해도 전혀 손댈 가치가 없을 수 있습니다.
언급URL : https://stackoverflow.com/questions/32505775/rebuild-all-indexes-in-a-database
반응형
'codememo' 카테고리의 다른 글
| 엔티티 프레임워크에서의 ICollection과 리스트 (0) | 2023.04.23 |
|---|---|
| Git에 적합한 (무료) 시각적 병합 도구는 무엇입니까? (Windows에서) (0) | 2023.04.23 |
| Xcode에서 프로비저닝프로파일을 삭제하는 방법 (0) | 2023.04.23 |
| Bash의 3진 연산자(?:) (0) | 2023.04.23 |
| 명령줄에서 Linux에서 xlsx를 csv로 변환 (0) | 2023.04.23 |