Te doy la bienvenida a nuestro sitio, aquí hallarás la solucíon a lo que estabas buscando.
Solución:
'DETAILED' implica un análisis completo de cada página del índice (o montón). Haga esto para cada tabla y cada índice secundario, el resultado significa que está haciendo un escaneo completo de la base de datos, de extremo a extremo, y no muy eficiente (es decir, no tan rápido como lo leería la copia de seguridad, por ejemplo). El tiempo lo impulsa:
- que tan grande es tu base de datos
- qué tan rápido es su subsistema IO para leer toda la base de datos
- Carga concurrente adicional que compite por el rendimiento de E / S
Básicamente, si todo lo que tiene es una pajita (su rendimiento de IO), se necesitan 30 minutos para beber un cubo (el tamaño de su base de datos). Compre E / S más rápido, reduzca el tamaño de sus datos o utilice SAMPLED exploraciones.
Dicho esto … 20 Gb es bastante pequeño. 30 minutos para leer 20 Gb es un lote de tiempo. ¿Es su subsistema IO? ese ¿lento? ¿Implementó en unidades de consumo de 1TB a 7200 RPM?
Además de la recomendación de @Remus de utilizar un SAMPLED escanear, no sé si esta consulta no puede comenzar hasta que comience su ventana de mantenimiento. ¿Por qué no rellenar previamente una tabla con los resultados? Si inicia esta consulta (digamos que un escaneo de muestra tarda 10 minutos) entre 15 y 20 minutos antes de su ventana de mantenimiento, y guarde los resultados en una tabla, los datos estarán listos para usar tan pronto como comience la ventana de mantenimiento, y los datos subyacentes no habrán cambiado mucho mientras tanto. Si evita ordenar y filtrar la consulta original, también debería completarse más rápido, p. Ej.
CREATE TABLE dbo.IndexStats
(
TableName SYSNAME,
IndexName SYSNAME,
Frag DECIMAL(5,2)
);
CREATE INDEX x ON dbo.IndexStats(Frag);
Luego, en su primer trabajo nocturno (que comienza antes de su ventana de mantenimiento):
TRUNCATE TABLE dbo.IndexStats;
INSERT dbo.IndexStats
SELECT
OBJECT_NAME(i.[object_id]),
i.name,
s.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS s
INNER JOIN sys.indexes AS i
ON i.[object_id] = s.[object_id]
AND i.index_id = s.index_id;
DELETE dbo.IndexStats WHERE Frag < 20
OR TableName IS NULL;
Ahora su script de desfragmentación real ya tiene toda la información que necesita para proceder de inmediato. (Incluso puede encadenar los trabajos o forzar lo anterior para que luego espere la hora de inicio de la ventana de mantenimiento usando WAITFOR TIME.)
También puede considerar jugar con LIMITED y vea cómo le va.
Descargo de responsabilidad: estos scripts se han probado en SQL Server 2005/2008. Sin embargo, este código y la información se proporcionan "TAL CUAL" sin garantía de ningún tipo, ya sea expresa o implícita, incluidas, entre otras, las garantías implícitas o la comerciabilidad y / o idoneidad para un propósito particular. Como siempre, pruebe esto en su entorno de prueba antes de intentar implementarlo en su entorno de producción. Ahora que eso está fuera del camino ...
Uno de los problemas con los que me encuentro al tratar con los DMV de índice es que no pueden correlacionarse. Es decir, no puede usar la APLICACIÓN CRUZADA / EXTERIOR contra ellos, para limitar los índices contra los que está realizando escaneos. Para evitar esto, implemento una función de contenedor, para DMV de índices físicos y operativos, en mi base de datos maestra:
Físico:
ALTER FUNCTION [dbo].[tfn_IndexPhysicalStats_select]
(
@DatabaseID SMALLINT = 0,
@ObjectID INT = 0,
@IndexID INT = -1,
@PartitionNumber INT = 0,
@Mode NVARCHAR(20) = NULL
)
RETURNS @IndexPhysicalStats TABLE
(
database_id SMALLINT NOT NULL,
object_id INT NOT NULL,
index_id INT NOT NULL,
partition_number INT NOT NULL,
index_type_desc NVARCHAR(60) NULL,
alloc_unit_type_desc NVARCHAR(60) NULL,
index_depth TINYINT NOT NULL,
index_level TINYINT NOT NULL,
avg_fragmentation_in_percent FLOAT NULL,
fragment_count BIGINT NULL,
avg_fragment_size_in_pages FLOAT NULL,
page_count BIGINT NOT NULL,
avg_page_space_used_in_percent FLOAT NULL,
record_count BIGINT NULL,
ghost_record_count BIGINT NULL,
version_ghost_record_count BIGINT NULL,
min_record_size_in_bytes INT NULL,
max_record_size_in_bytes INT NULL,
avg_record_size_in_bytes FLOAT NULL,
forwarded_record_count BIGINT NULL
)
AS
BEGIN
INSERT INTO @IndexPhysicalStats
(
database_id,
object_id,
index_id,
partition_number,
index_type_desc,
alloc_unit_type_desc,
index_depth,
index_level,
avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages,
page_count,
avg_page_space_used_in_percent,
record_count,
ghost_record_count,
version_ghost_record_count,
min_record_size_in_bytes,
max_record_size_in_bytes,
avg_record_size_in_bytes,
forwarded_record_count
)
SELECT
ddips.database_id,
ddips.object_id,
ddips.index_id,
ddips.partition_number,
ddips.index_type_desc,
ddips.alloc_unit_type_desc,
ddips.index_depth,
ddips.index_level,
ddips.avg_fragmentation_in_percent,
ddips.fragment_count,
ddips.avg_fragment_size_in_pages,
ddips.page_count,
ddips.avg_page_space_used_in_percent,
ddips.record_count,
ddips.ghost_record_count,
ddips.version_ghost_record_count,
ddips.min_record_size_in_bytes,
ddips.max_record_size_in_bytes,
ddips.avg_record_size_in_bytes,
ddips.forwarded_record_count
FROM sys.dm_db_index_physical_stats
(
@DatabaseID,
@ObjectID,
@IndexID,
@PartitionNumber,
@Mode
) AS ddips;
RETURN;
END
Operacional:
ALTER FUNCTION [dbo].[tfn_IndexOperationalStats_select]
(
@DatabaseID SMALLINT = 0,
@TableID INT = 0,
@IndexID INT = -1,
@PartitionNumber INT = 0
)
RETURNS @IndexOperationalStats TABLE
(
database_id SMALLINT NOT NULL,
object_id INT NOT NULL,
index_id INT NOT NULL,
partition_number INT NOT NULL,
leaf_insert_count BIGINT NULL,
leaf_delete_count BIGINT NULL,
leaf_update_count BIGINT NULL,
leaf_ghost_count BIGINT NULL,
nonleaf_insert_count BIGINT NULL,
nonleaf_delete_count BIGINT NULL,
nonleaf_update_count BIGINT NULL,
leaf_allocation_count BIGINT NULL,
nonleaf_allocation_count BIGINT NULL,
leaf_page_merge_count BIGINT NULL,
nonleaf_page_merge_count BIGINT NULL,
range_scan_count BIGINT NULL,
singleton_lookup_count BIGINT NULL,
forwarded_fetch_count BIGINT NULL,
lob_fetch_in_pages BIGINT NULL,
lob_fetch_in_bytes BIGINT NULL,
lob_orphan_create_count BIGINT NULL,
lob_orphan_insert_count BIGINT NULL,
row_overflow_fetch_in_pages BIGINT NULL,
row_overflow_fetch_in_bytes BIGINT NULL,
column_value_push_off_row_count BIGINT NULL,
column_value_pull_in_row_count BIGINT NULL,
row_lock_count BIGINT NULL,
row_lock_wait_count BIGINT NULL,
row_lock_wait_in_ms BIGINT NULL,
page_lock_count BIGINT NULL,
page_lock_wait_count BIGINT NULL,
page_lock_wait_in_ms BIGINT NULL,
index_lock_promotion_attempt_count BIGINT NULL,
index_lock_promotion_count BIGINT NULL,
page_latch_wait_count BIGINT NULL,
page_latch_wait_in_ms BIGINT NULL,
page_io_latch_wait_count BIGINT NULL,
page_io_latch_wait_in_ms BIGINT NULL
PRIMARY KEY CLUSTERED
(
database_id ASC,
object_id ASC,
index_id ASC,
partition_number ASC
)
)
AS
BEGIN
INSERT INTO @IndexOperationalStats
(
database_id,
object_id,
index_id,
partition_number,
leaf_insert_count,
leaf_delete_count,
leaf_update_count,
leaf_ghost_count,
nonleaf_insert_count,
nonleaf_delete_count,
nonleaf_update_count,
leaf_allocation_count,
nonleaf_allocation_count,
leaf_page_merge_count,
nonleaf_page_merge_count,
range_scan_count,
singleton_lookup_count,
forwarded_fetch_count,
lob_fetch_in_pages,
lob_fetch_in_bytes,
lob_orphan_create_count,
lob_orphan_insert_count,
row_overflow_fetch_in_pages,
row_overflow_fetch_in_bytes,
column_value_push_off_row_count,
column_value_pull_in_row_count,
row_lock_count,
row_lock_wait_count,
row_lock_wait_in_ms,
page_lock_count,
page_lock_wait_count,
page_lock_wait_in_ms,
index_lock_promotion_attempt_count,
index_lock_promotion_count,
page_latch_wait_count,
page_latch_wait_in_ms,
page_io_latch_wait_count,
page_io_latch_wait_in_ms
)
SELECT
ddios.database_id,
ddios.object_id,
ddios.index_id,
ddios.partition_number,
ddios.leaf_insert_count,
ddios.leaf_delete_count,
ddios.leaf_update_count,
ddios.leaf_ghost_count,
ddios.nonleaf_insert_count,
ddios.nonleaf_delete_count,
ddios.nonleaf_update_count,
ddios.leaf_allocation_count,
ddios.nonleaf_allocation_count,
ddios.leaf_page_merge_count,
ddios.nonleaf_page_merge_count,
ddios.range_scan_count,
ddios.singleton_lookup_count,
ddios.forwarded_fetch_count,
ddios.lob_fetch_in_pages,
ddios.lob_fetch_in_bytes,
ddios.lob_orphan_create_count,
ddios.lob_orphan_insert_count,
ddios.row_overflow_fetch_in_pages,
ddios.row_overflow_fetch_in_bytes,
ddios.column_value_push_off_row_count,
ddios.column_value_pull_in_row_count,
ddios.row_lock_count,
ddios.row_lock_wait_count,
ddios.row_lock_wait_in_ms,
ddios.page_lock_count,
ddios.page_lock_wait_count,
ddios.page_lock_wait_in_ms,
ddios.index_lock_promotion_attempt_count,
ddios.index_lock_promotion_count,
ddios.page_latch_wait_count,
ddios.page_latch_wait_in_ms,
ddios.page_io_latch_wait_count,
ddios.page_io_latch_wait_in_ms
FROM sys.dm_db_index_operational_stats
(
@DatabaseID,
@TableID,
@IndexID,
@PartitionNumber
) AS ddios;
RETURN;
END
Luego hago referencia a esta función en mis trabajos de mantenimiento de índices de la siguiente manera:
DECLARE
@DDL NVARCHAR(MAX);
DECLARE ddl_cursor CURSOR
FOR
SELECT
CONVERT(NVARCHAR(MAX), DDL.DDL) AS DDL
FROM
(
SELECT
MasterIndexes.SchemaName,
MasterIndexes.TableName,
MasterIndexes.IndexName,
MasterIndexes.DatabaseID,
MasterIndexes.ObjectID,
MasterIndexes.IndexID,
MasterIndexes.PartitionNumber,
MasterIndexes.type_desc,
MasterIndexes.is_unique,
MasterIndexes.is_primary_key,
MasterIndexes.is_unique_constraint,
MasterIndexes.fill_factor,
MasterIndexes.allow_row_locks,
MasterIndexes.allow_page_locks,
MasterIndexes.UpdateStatisticsIndicator,
1 AS SortInTempDB,
CASE
WHEN CONVERT(VARCHAR(100), SERVERPROPERTY('edition')) LIKE 'Enterprise Edition%' THEN 1
ELSE 0
END AS OnlineIndicator,
CASE
WHEN
ips.avg_fragmentation_in_percent BETWEEN CONVERT(FLOAT, 10) AND CONVERT(FLOAT, 30)
AND ips.page_count >= 100
THEN
1
ELSE
0
END AS ReorganizationIndicator,
CASE
WHEN
(
ips.avg_fragmentation_in_percent >= 30
AND ips.page_count >= 100
)
OR
(
ips.avg_fragmentation_in_percent BETWEEN CONVERT(FLOAT, 10) AND CONVERT(FLOAT, 30)
AND ips.page_count < 100
)
THEN
1
ELSE
0
END AS RebuildIndicator
FROM
(
SELECT
s.name AS SchemaName,
t.name AS TableName,
ix.name AS IndexName,
DB_ID() AS DatabaseID,
ddps.object_id AS ObjectID,
ddps.index_id AS IndexID,
ddps.partition_number AS PartitionNumber,
ix.type_desc,
ix.is_unique,
ix.is_primary_key,
ix.is_unique_constraint,
ix.fill_factor,
ix.allow_row_locks,
ix.allow_page_locks,
1 AS UpdateStatisticsIndicator
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.schema_id = t.schema_id
INNER JOIN sys.indexes AS ix
ON t.object_id = ix.object_id
INNER JOIN sys.dm_db_partition_stats AS ddps
ON ix.object_id = ddps.object_id
AND ix.index_id = ddps.index_id
CROSS APPLY master.dbo.tfn_IndexOperationalStats_select
(
DB_ID(),
t.object_id,
ix.index_id,
NULL
) AS ios
WHERE
CASE
WHEN ddps.row_count = 0 THEN 0
ELSE
(
(
CONVERT
(
FLOAT,
(
ios.nonleaf_insert_count +
ios.nonleaf_update_count +
ios.leaf_insert_count +
ios.leaf_update_count
)
) /
CONVERT
(
FLOAT,
ddps.row_count
)
) * 100.0
)
END >= 10.0
AND t.is_ms_shipped = 0
AND t.name NOT LIKE 'MSmerge%'
AND ix.index_id > 0
) AS MasterIndexes
CROSS APPLY master.dbo.tfn_IndexPhysicalStats_select
(
MasterIndexes.DatabaseID,
MasterIndexes.ObjectID,
MasterIndexes.IndexID,
MasterIndexes.PartitionNumber,
'SAMPLED'
) AS ips
) AS MasterIndexList
CROSS APPLY
(
SELECT
'ALTER INDEX ' +
MasterIndexList.IndexName +
' ON ' +
MasterIndexList.SchemaName + '.' + MasterIndexList.TableName +
' REBUILD WITH(' +
'FILLFACTOR = ' +
CASE
WHEN MasterIndexList.fill_factor = 0 THEN '100'
ELSE CONVERT(VARCHAR(3), MasterIndexList.fill_factor)
END + ', ' +
'SORT_IN_TEMPDB = ' +
CASE
WHEN MasterIndexList.SortInTempDB = 1 THEN 'ON'
ELSE 'OFF'
END + ', ' +
'ONLINE = ' +
CASE
WHEN MasterIndexList.OnlineIndicator = 1 THEN 'ON'
ELSE 'OFF'
END + ', ' +
'ALLOW_ROW_LOCKS = ' +
CASE
WHEN MasterIndexList.[allow_row_locks] = 1 THEN 'ON'
ELSE 'OFF'
END + ', ' +
'ALLOW_PAGE_LOCKS = ' +
CASE
WHEN MasterIndexList.[allow_page_locks] = 1 THEN 'ON'
ELSE 'OFF'
END + ');' AS [DDL],
1 AS DDLOrdinal
WHERE MasterIndexList.RebuildIndicator = 1
UNION ALL
SELECT
'ALTER INDEX ' +
MasterIndexList.IndexName +
' ON ' +
MasterIndexList.SchemaName + '.' + MasterIndexList.TableName +
' REORGANIZE;' AS [DDL],
2 AS DDLOrdinal
WHERE MasterIndexList.ReorganizationIndicator = 1
UNION ALL
SELECT
'UPDATE STATISTICS ' +
MasterIndexList.SchemaName + '.' + MasterIndexList.TableName + ' ' +
MasterIndexList.IndexName + ' ' +
'WITH FULLSCAN;' AS [DDL],
3 AS DDLOrdinal
WHERE MasterIndexList.UpdateStatisticsIndicator = 1
AND MasterIndexList.RebuildIndicator = 0
AND STATS_DATE(MasterIndexList.ObjectID, MasterIndexList.IndexID) <= DATEADD(hh, -20, GETDATE())
) AS [DDL]
ORDER BY
ObjectID ASC,
IndexID ASC,
DDLOrdinal ASC;
OPEN ddl_cursor;
FETCH NEXT FROM ddl_cursor
INTO @DDL;
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sys.sp_executesql
@stmt = @DDL;
FETCH NEXT FROM ddl_cursor
INTO @DDL;
END
CLOSE ddl_cursor;
DEALLOCATE ddl_cursor;
GO
Como siempre, su kilometraje puede variar, pero siéntase libre de usar / modificar estos scripts para satisfacer sus necesidades.
Tener una buena,
Mate