Eventos
31 mar, 23 - 2 abr, 23
Evento de aprendizaje de SQL, Fabric y Power BI más grande. 31 de marzo – 2 de abril. Use el código FABINSIDER para ahorrar $400.
Regístrate hoyEste explorador ya no se admite.
Actualice a Microsoft Edge para aprovechar las características y actualizaciones de seguridad más recientes, y disponer de soporte técnico.
Se aplica a: SQL Server
Base de datos de Azure SQL
Azure SQL Managed Instance
Sistema de plataforma de análisis (PDW)
Base de datos SQL de Microsoft Fabric
Con este artículo podrá decidir cuándo y cómo realizar el mantenimiento de índices. Abarca conceptos como la fragmentación de índices y la densidad de páginas, y su impacto en el rendimiento de las consultas y el consumo de recursos. Describe los métodos de mantenimiento, organización y regeneración de índices, y sugiere una estrategia de mantenimiento de índices que equilibra las posibles mejoras de rendimiento con el consumo de recursos necesario para el mantenimiento.
Nota
Este artículo no se aplica a un grupo de SQL dedicado en Azure Synapse Analytics. Para obtener más información sobre el mantenimiento de la indexación de un grupo de SQL dedicado de Azure Synapse Analytics, consulte Índices en tablas de grupo de SQL dedicadas en Azure Synapse Analytics.
Qué es la fragmentación de índices y cómo afecta al rendimiento:
En los índices de árbol B (almacén de filas), la fragmentación se produce cuando los índices tienen páginas en las que la ordenación lógica dentro del índice, basada en su valor de clave, no coincide con la ordenación física dentro de las páginas de índice.
Nota
La documentación utiliza el término árbol B generalmente en referencia a los índices. En los índices del almacén de filas, el motor de la base de datos implementa un árbol B+. Esto no se aplica a los índices de almacén de columnas ni a los índices de tablas optimizadas para memoria. Para obtener más información, consulte la guía de diseño y arquitectura de índices de SQL Server y Azure SQL.
El motor de base de datos modifica los índices de forma automática cada vez que se realizan operaciones de inserción, actualización o eliminación en los datos subyacentes. Por ejemplo, la adición de filas en una tabla puede hacer que las páginas existentes en los índices de almacén de filas se dividan para dejar espacio para la inserción de nuevas filas. Con el tiempo, estas modificaciones pueden hacer que la información del índice se disperse (se fragmente) por la base de datos.
En el caso de las consultas que leen muchas páginas mediante análisis de índices completos o de intervalo, los índices muy fragmentados pueden degradar el rendimiento de las consultas cuando se requieren E/S adicional para leer los datos. En lugar de un pequeño número de solicitudes de entrada/salida grandes, la consulta requeriría un mayor número de solicitudes de entrada/salida pequeñas para leer la misma cantidad de datos.
Cuando el subsistema de almacenamiento proporciona un mejor rendimiento de entrada/salida secuencial que de entrada/salida aleatoria, la fragmentación del índice puede degradar el rendimiento porque se requiere más entrada/salida aleatoria para leer los índices fragmentados.
Qué es las densidad de página (también conocida como integridad de página) y cómo afecta al rendimiento:
Sugerencia
En muchas cargas de trabajo, el aumento de la densidad de página tiene un mayor impacto positivo en el rendimiento que la reducción de la fragmentación.
Para evitar reducir innecesariamente la densidad de página, Microsoft no recomienda establecer el factor de relleno en valores distintos de 100 o de 0, excepto en algunos casos para los índices que experimentan un gran número de divisiones de página, por ejemplo, índices modificados con frecuencia con columnas iniciales que contienen valores GUID no secuenciales.
Tanto la fragmentación como la densidad de página se encuentran entre los factores que se deben tener en cuenta a la hora de decidir si se debe realizar el mantenimiento de índices y qué método de mantenimiento se debe usar.
La fragmentación se define de forma diferente para los índices de almacén de filas y de almacén de columnas. Para los índices de almacén de filas, sys.dm_db_index_physical_stats() le permite determinar la fragmentación y la densidad de página de un índice específico, de todos los índices de una tabla o de una vista indexada, de todos los índices de una base de datos o de todos los índices de todas las bases de datos. Para los índices con particiones, sys.dm_db_index_physical_stats()
también proporciona información de la fragmentación para cada partición.
El conjunto de resultados siguiente muestra las columnas devueltas por sys.dm_db_index_physical_stats
:
Columna | Descripción |
---|---|
avg_fragmentation_in_percent |
Fragmentación lógica (páginas de un índice que están correctamente ordenadas). |
avg_page_space_used_in_percent |
Densidad media de la página. |
En el caso de los grupos de filas comprimidos en índices de almacén de columnas, la fragmentación se define como la proporción de filas eliminadas con el total de filas, expresada como porcentaje. sys.dm_db_column_store_row_group_physical_stats permite determinar el número de filas totales y eliminadas por grupo de filas en un índice específico, todos los índices de una tabla o todos los índices de una base de datos.
El conjunto de resultados siguiente muestra las columnas devueltas por sys.dm_db_column_store_row_group_physical_stats
:
Columna | Descripción |
---|---|
total_rows |
Número de filas almacenadas físicamente en el grupo de filas. En el caso de los grupos de filas comprimidos, incluye las filas marcadas como eliminadas. |
deleted_rows |
Número de filas almacenadas físicamente en un grupo de filas comprimido que se han marcado para su eliminación. Es 0 en el caso de los grupos de filas que se encuentran en el almacén delta. |
La fragmentación de grupos de filas comprimidos en un índice de almacén de columnas se puede calcular mediante esta fórmula:
100.0*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)
Sugerencia
Para los índices de almacén de filas y de almacén de columnas, revise la fragmentación de índices o montones y la densidad de página después de eliminar o actualizar un gran número de filas. En el caso de los montones, si hay actualizaciones frecuentes, revise periódicamente la fragmentación para evitar la proliferación de registros de reenvío. Para más información sobre los montones, vea Montones (tablas sin índices agrupados).
Consulte Ejemplos de consultas de ejemplo para determinar la fragmentación y la densidad de páginas.
Puede reducir la fragmentación de índices y aumentar la densidad de página mediante uno de los métodos siguientes:
Nota
Para los índices con particiones, puede usar cualquiera de los métodos siguientes en todas las particiones o en una sola partición de un índice.
La reorganización de un índice consume menos recursos que volver a generarlo. Por ese motivo, debe ser el método de mantenimiento de índice preferido, a menos que haya una razón específica para usar la regeneración de índices. La reorganización siempre es una operación en línea. Esto significa que los bloqueos de nivel de objeto a largo plazo no se mantienen y que las consultas o actualizaciones en la tabla subyacente pueden continuar durante la operación ALTER INDEX ... REORGANIZE
.
Nota
A partir de SQL Server 2019 (15.x), Azure SQL Database y Azure SQL Managed Instance, el motor de tuplas cuenta con la ayuda de una tarea de combinación en segundo plano que comprime automáticamente los grupos de filas delta abiertos más pequeños que han existido durante algún tiempo, según lo determinado por un umbral interno, o combina los grupos de filas comprimidos desde donde se ha eliminado un gran número de filas. De este modo, se mejora la calidad del índice de almacén de columnas a lo largo del tiempo. En la mayoría de los casos, esto elimina la necesidad de emitir comandos ALTER INDEX ... REORGANIZE
.
Sugerencia
Si cancela una operación de reorganización, o si se interrumpe de otro modo, el progreso realizado hasta ese momento se conserva en la base de datos. Para reorganizar índices grandes, la operación se puede iniciar y detener varias veces hasta que se complete.
El proceso de volver a crear un índice quita y vuelve a crear el índice. En función del tipo de índice y de la versión del motor de base de datos, una operación de regeneración puede realizarse en línea o sin conexión. Normalmente, una regeneración de índices sin conexión tarda menos tiempo que una regeneración en línea, pero contiene bloqueos de nivel de objeto mientras dura la operación de regeneración, lo que impide que las consultas accedan a la tabla o vista.
Una regeneración de índices en línea no requiere bloqueos de nivel de objeto hasta el final de la operación, cuando se debe mantener un bloqueo durante un breve período de tiempo para completar la regeneración. Dependiendo de la versión del motor de base de datos, se puede iniciar una regeneración de índices en línea como una operación reanudable. Se puede pausar una regeneración de índices que se puede reanudar, manteniendo el progreso realizado hasta ese momento. Una operación de regeneración se puede reanudar después de haberse pausado o interrumpido, o anularse si no es necesario completar la regeneración.
Para ver la sintaxis de T-SQL, consulte ALTER INDEX REBUILD. Para más información sobre las regeneraciones de índices en línea, consulte Realizar operaciones de índice en línea.
Nota
Mientras se vuelve a generar un índice en línea, cada modificación de los datos de las columnas indexadas debe actualizar una copia adicional del índice. Esto puede provocar una degradación del rendimiento menor de las instrucciones de modificación de datos durante la regeneración en línea.
Si se pausa una operación de índice que se puede reanudar en línea, este impacto en el rendimiento persiste hasta que la operación que se puede reanudar se complete o se anule. Si no piensa completar una operación de índice que se puede reanudar, anúlela en lugar de pausarla.
Sugerencia
En función de los recursos disponibles y los patrones de carga de trabajo, la especificación de un valor superior al valor MAXDOP
predeterminado en la instrucción ALTER INDEX REBUILD puede reducir la duración de la regeneración a costa de un mayor uso de la CPU.
En el caso de los índices de almacén de filas, al volver a generar se quita la fragmentación en todos los niveles del índice y se compactan las páginas en función del factor de relleno especificado o actual. Cuando se especifica ALL
, todos los índices de la tabla se quitan y se vuelven a generar en una única operación. Cuando se vuelven a generar índices con 128 extensiones o más, el motor de base de datos fracciona las desasignaciones de página y sus bloqueos asociados hasta después de que se complete la regeneración. Para obtener ejemplos de sintaxis, consulte Ejemplos: Regeneración del almacén de filas.
En el caso de los índices de almacén de columnas, al volver a generar se quita la fragmentación, se mueven las filas del almacén delta al almacén de columnas y se eliminan físicamente las filas marcadas para su eliminación. Para obtener ejemplos de sintaxis, consulte Ejemplos: Regeneración del almacén de columnas.
Sugerencia
A partir de SQL Server 2016 (13.x), no es necesario normalmente volver a generar el índice de almacén de columnas, ya que REORGANIZE
realiza las operaciones básicas de una regeneración como una operación en línea.
En versiones anteriores de SQL Server 2008 (10.0x), a veces se podía volver a generar un índice no agrupado de almacén de filas para corregir incoherencias provocadas por datos dañados en el índice.
Aún puede reparar estas incoherencias en el índice no agrupado al volver a generar un índice no agrupado sin conexión. Sin embargo, no es posible reparar las incoherencias de índices no agrupados mediante la regeneración del índice en línea, ya que el mecanismo de regeneración en línea usa el índice no agrupado existente como base para la regeneración y, por tanto, la incoherencia persiste. Volver a generar el índice sin conexión a veces puede forzar un examen del índice agrupado (o montón) y, por tanto, reemplazar los datos incoherentes del índice no agrupado por los datos del índice agrupado o montón.
Para asegurarse de que el índice agrupado o el montón se usan como origen de datos, coloque y vuelva a crear el índice no agrupado en lugar de volver a generarlo. Al igual que con versiones anteriores, puede recuperarse de incoherencias restaurando los datos afectados desde una copia de seguridad. Sin embargo, es posible que puedan reparar incoherencias de índice no agrupadas mediante la regeneración sin conexión o la recreación. Para obtener más información, vea DBCC CHECKDB (Transact-SQL).
Aproveche soluciones como la desfragmentación de índice adaptable para administrar automáticamente las actualizaciones de estadísticas y la desfragmentación de índices para una o varias bases de datos. Este procedimiento elige automáticamente si se debe volver a generar o reorganizar un índice según su nivel de fragmentación, entre otros parámetros y actualiza las estadísticas con un umbral lineal.
Los siguientes escenarios hacen que se vuelvan a generar automáticamente todos los índices no agrupados del almacén de filas de una tabla:
CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON)
Los escenarios siguientes no vuelven a generar automáticamente todos los índices no agrupados del almacén de filas en la misma tabla:
Importante
No es posible volver a organizar o volver a generar un índice si el grupo de archivos en el que se encuentra está sin conexión o es de solo lectura. Cuando se especifica la palabra clave ALL y hay uno o más índices en un grupo de archivos sin conexión o de solo lectura, se produce un error en la instrucción.
Mientras se vuelve a generar un índice, el medio físico debe tener espacio suficiente para almacenar dos copias del índice. Cuando finaliza la recompilación, el motor de base de datos elimina el índice original.
Cuando se especifica ALL
con la instrucción ALTER INDEX ... REORGANIZE
, se reorganizan los índices agrupados, no agrupados y XML en la tabla.
Con frecuencia, cuando se vuelven a generar o se reorganizan índices pequeños de almacén de filas no se reduce la fragmentación. Hasta, e incluso, SQL Server 2014 (12.x), el motor de base de datos de SQL Server asigna espacio mediante extensiones mixtas. Por lo tanto, las páginas de índices pequeños a veces se almacenan en extensiones mixtas, lo que hace que estos índices se fragmenten implícitamente. Las extensiones mixtas pueden estar compartidas por hasta ocho objetos, de modo que es posible que no se pueda reducir la fragmentación en un índice pequeño después de reorganizar o volver a generar dicho índice.
Cuando se vuelve a generar un índice de almacén de columnas, el motor de base de datos lee todos los datos del índice de almacén de columnas original, incluido el almacén delta. Combina datos en nuevos grupos de filas y comprime todos los grupos de filas en el almacén de columnas. El motor de base de datos desfragmenta el almacén de columnas mediante la eliminación física de las filas que se han marcado como eliminadas.
Nota
A partir de SQL Server 2019 (15.x), el motor de tuplas cuenta con la ayuda de una tarea de combinación en segundo plano que comprime automáticamente los grupos de filas de almacenamiento delta abiertos que han existido durante algún tiempo, según lo determinado por un umbral interno, o combina los grupos de filas comprimidos desde donde se ha eliminado un gran número de filas. De este modo, se mejora la calidad del índice de almacén de columnas a lo largo del tiempo. Para obtener más información sobre los términos y conceptos de almacén de columnas, vea Índices de almacén de columnas: información general.
Volver a generar la tabla completa tarda mucho si el índice es grande y requiere el espacio en disco suficiente para almacenar una copia adicional del índice durante la regeneración.
En el caso de las tablas con particiones, no es necesario volver a generar todo el índice de almacén de columnas si la fragmentación solo está presente en algunas particiones, por ejemplo en particiones donde las instrucciones UPDATE
, DELETE
o MERGE
han afectado a un gran número de filas.
La regeneración de una partición después de cargar o modificar los datos garantiza que todos los datos se almacenen en grupos de filas comprimidos en el almacén de columnas. Cuando el proceso de carga de datos inserta datos en una partición mediante lotes menores de 102 400 filas, la partición puede terminar con varios grupos de filas abiertos en el almacén delta. La regeneración mueve todas las filas del almacén delta a grupos de filas comprimidos del almacén de columnas.
Al reorganizar un índice de almacén de columnas, el motor de base de datos comprime cada grupo de filas cerrado del almacén delta en el almacén de columnas como un grupo de filas comprimido. A partir de SQL Server 2016 (13.x) y en Azure SQL Database, el comando REORGANIZE
realiza estas otras optimizaciones de desfragmentación en línea:
Después de realizar cargas de datos, puede haber varios grupos de filas pequeños en el almacén delta. Puede usar ALTER INDEX REORGANIZE
para forzar estos grupos de filas en el almacén de columnas y, a continuación, combinar grupos de filas comprimidos más pequeños en grupos de filas comprimidos más grandes. La operación de reorganización también quitará las filas que se hayan marcado como eliminadas del almacén de columnas.
Nota
La reorganización de un índice de almacén de columnas mediante Management Studio combinará grupos de filas comprimidas, pero no obligará a que todos los grupos de filas se compriman en el almacén de columnas. Se comprimirán los grupos de filas cerrados, mientras que los grupos de filas abiertos no se comprimirán en el almacén de columnas.
Para formar la compresión de todos los grupos de filas, use el ejemplo de Transact-SQL que incluye COMPRESS_ALL_ROW_GROUPS = ON
.
El mantenimiento de índices, realizado mediante la reorganización o regeneración de un índice, consume muchos recursos. Provoca un aumento significativo en el uso de CPU, la memoria usada y la entrada/salida de almacenamiento. Sin embargo, dependiendo de la carga de trabajo de la base de datos y de otros factores, las ventajas que aporta van desde las de vital importancia hasta las minúsculas.
Para evitar el uso innecesario de recursos, no realice el mantenimiento de índices indiscriminadamente. En su lugar, las ventajas de rendimiento del mantenimiento de índices deben determinarse empíricamente para cada carga de trabajo mediante la estrategia recomendada y sopesar los costos de recursos y el impacto en la carga de trabajo necesarios para lograr estas ventajas.
La probabilidad de ver las ventajas de rendimiento de reorganizar o volver a generar un índice es mayor cuando el índice está muy fragmentado o cuando su densidad de página es baja. Sin embargo, estos no son los únicos aspectos que se deben tener en cuenta. Factores como los patrones de consulta (procesamiento de transacciones frente a análisis e informes), el comportamiento del subsistema de almacenamiento, la memoria disponible y las mejoras del motor de base de datos a lo largo del tiempo desempeñan un papel fundamental.
Importante
Las decisiones de mantenimiento de índices deben tomarse después de considerar varios factores en el contexto específico de cada carga de trabajo, incluido el costo de mantenimiento de los recursos. No deben basarse solo en umbrales fijos de fragmentación o densidad de página.
A menudo, los clientes observan mejoras de rendimiento después de la regeneración de índices. Sin embargo, en muchos casos estas mejoras no están relacionadas con la reducción de la fragmentación o el aumento de la densidad de página.
Una regeneración de índices tiene una ventaja importante: actualiza las estadísticas de las columnas clave del índice mediante el examen de todas las filas del índice. Esto equivale a ejecutar UPDATE STATISTICS ... WITH FULLSCAN
, lo que hace que las estadísticas se actualicen y, a veces, mejoren su calidad en comparación con la actualización de las estadísticas muestreadas predeterminadas. Cuando se actualizan las estadísticas, se vuelven a generar los planes de consulta que hacen referencia a ellas. Si el plan anterior para una consulta no era óptimo debido a estadísticas obsoletas, una proporción de muestreo de estadísticas insuficiente o por otros motivos, el plan que se vuelve a generar es a menudo mejor.
A menudo, los clientes atribuyen incorrectamente esta mejora a la propia regeneración del índice, lo que la convierte en el resultado de una fragmentación reducida y una mayor densidad de página. En realidad, a menudo, la misma ventaja se puede lograr a un costo de recursos mucho más económico mediante la actualización de estadísticas en lugar de volver a generar los índices.
Sugerencia
El costo de recursos de actualizar las estadísticas es menor en comparación con la regeneración de índices y la operación suele completarse en minutos. Las regeneraciones de índices pueden tardar horas.
Microsoft recomienda que los clientes consideren y adopten la siguiente estrategia de mantenimiento de índices:
WITH SAMPLE ... PERCENT
o WITH FULLSCAN
(esto no es habitual).Además de las consideraciones y estrategias anteriores, en Azure SQL Database y Azure SQL Managed Instance es especialmente importante tener en cuenta los costos y las ventajas del mantenimiento de índices. Los clientes solo deben realizarlo cuando haya una necesidad demostrada y teniendo en cuenta los siguientes puntos.
Hay escenarios específicos pero poco habituales en los que se puede necesitar un mantenimiento de índice único o periódico en Azure SQL Database y Azure SQL Managed Instance:
Sugerencia
Si ha determinado que el mantenimiento de índices es necesario para las cargas de trabajo Azure SQL Database y Azure SQL Managed Instance, debe reorganizar los índices o usar la regeneración de índices en línea. Esto permite que las cargas de trabajo de consulta accedan a las tablas mientras se vuelven a generar los índices.
Además, hacer que la operación se reanude le permite evitar reiniciarla desde el principio si se interrumpe por una conmutación por error de la base de datos planeada o no planeada. El uso de operaciones de índice que se pueden reanudar es especialmente importante cuando los índices son grandes.
Sugerencia
Las operaciones de índice sin conexión normalmente se completan más rápido que las operaciones en línea. Se deben usar cuando las consultas no tengan acceso a las tablas durante la operación, por ejemplo, después de cargar datos en tablas de almacenamiento provisional como parte de un proceso ETL secuencial.
Los índices de almacén de filas que tienen más de 128 extensiones se vuelven a generar en dos fases independientes: lógica y física. En la fase lógica, las unidades de asignación existentes que utiliza el índice están señaladas para cancelación de asignación las filas de datos se copian y ordenan y luego se mueven a las nuevas unidades de asignación creadas para almacenar el índice recompilado. En la fase física, las unidades de asignación previamente señaladas para cancelación de asignación se quitan físicamente de las transacciones breves que se realizan en segundo plano y no requieren demasiados bloqueos. Para más información sobre las unidades de asignación, consulte Guía de arquitectura de páginas y extensiones.
La instrucción ALTER INDEX REORGANIZE
requiere que el archivo de datos que contiene el índice tenga espacio disponible, ya que la operación solo puede asignar páginas de trabajo temporales en el mismo archivo, no en otro del mismo grupo de archivos. Aunque el grupo de archivos tenga espacio disponible, el usuario todavía puede encontrar el error 1105: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup
durante la operación de reorganización si un archivo de datos se queda sin espacio.
No es posible reorganizar un índice cuando ALLOW_PAGE_LOCKS
está establecido en OFF.
Hasta SQL Server 2017 (14.x), la regeneración de un índice de almacén de columnas agrupado es una operación sin conexión. El motor de base de datos tiene que adquirir un bloqueo exclusivo en la tabla o la partición mientras se produce la regeneración. Los datos están sin conexión y no se encuentran disponibles incluso si se usa NOLOCK
, el aislamiento de instantánea de lectura confirmada (RCSI) o el aislamiento de instantánea. A partir de SQL Server 2019 (15.x), se puede volver a generar un índice de almacén de columnas agrupado mediante la opción ONLINE = ON
.
Advertencia
La creación y regeneración de índices no alineados en una tabla con más de 1.000 particiones es posible, pero no se admite. Si se hace, se puede degradar el rendimiento o consumir excesiva memoria durante estas operaciones. Microsoft recomienda usar solo índices alineados cuando el número de particiones sea superior a 1000.
FULLSCAN
en CREATE STATISTICS
o UPDATE STATISTICS
. Sin embargo, a partir de SQL Server 2012 (11.x), cuando se crea o se vuelve a generar un índice con particiones, las estadísticas no se crean ni actualizan al examinar todas las filas de la tabla. Por el contrario, se utiliza la proporción de muestreo predeterminada. Para crear o actualizar estadísticas sobre índices con particiones mediante el examen de todas las filas de la tabla, use CREATE STATISTICS o UPDATE STATISTICS con la cláusula FULLSCAN
.PERSIST_SAMPLE_PERCENT
establecida en ON
, las operaciones de índice que se pueden reanudar usan la proporción de muestreo persistente para crear o actualizar estadísticas.En el ejemplo siguiente se determina la fragmentación media y la densidad de página de todos los índices de almacén de filas de la base de datos actual. Usa el modo SAMPLED
para devolver resultados que se pueden usar rápidamente. Para obtener resultados más precisos, use el modo DETAILED
. Esto requiere examinar todas las páginas de índice y puede tardar mucho tiempo.
SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
OBJECT_NAME(ips.object_id) AS object_name,
i.name AS index_name,
i.type_desc AS index_type,
ips.avg_fragmentation_in_percent,
ips.avg_page_space_used_in_percent,
ips.page_count,
ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
AND
ips.index_id = i.index_id
ORDER BY page_count DESC;
La instrucción anterior devuelve un conjunto de resultados similar al siguiente.
schema_name object_name index_name index_type avg_fragmentation_in_percent avg_page_space_used_in_percent page_count alloc_unit_type_desc
------------ --------------------- ---------------------------------------- ------------- ---------------------------- ------------------------------ ----------- --------------------
dbo FactProductInventory PK_FactProductInventory CLUSTERED 0.390015600624025 99.7244625648629 3846 IN_ROW_DATA
dbo DimProduct PK_DimProduct_ProductKey CLUSTERED 0 89.6839757845318 497 LOB_DATA
dbo DimProduct PK_DimProduct_ProductKey CLUSTERED 0 80.7132814430442 251 IN_ROW_DATA
dbo FactFinance NULL HEAP 0 99.7982456140351 239 IN_ROW_DATA
dbo ProspectiveBuyer PK_ProspectiveBuyer_ProspectiveBuyerKey CLUSTERED 0 98.1086236718557 79 IN_ROW_DATA
dbo DimCustomer IX_DimCustomer_CustomerAlternateKey NONCLUSTERED 0 99.5197553743514 78 IN_ROW_DATA
Para más información, consulte sys.dm_db_index_physical_stats.
En el ejemplo siguiente se determina la fragmentación media de todos los índices de almacén de columnas con grupos de filas comprimidos en la base de datos actual.
SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
OBJECT_NAME(i.object_id) AS object_name,
i.name AS index_name,
i.type_desc AS index_type,
100.0 * (ISNULL(SUM(rgs.deleted_rows), 0)) / NULLIF(SUM(rgs.total_rows), 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS rgs
ON i.object_id = rgs.object_id
AND
i.index_id = rgs.index_id
WHERE rgs.state_desc = 'COMPRESSED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc
ORDER BY schema_name, object_name, index_name, index_type;
La instrucción anterior devuelve un conjunto de resultados similar al siguiente.
schema_name object_name index_name index_type avg_fragmentation_in_percent
------------ ---------------------- ------------------------------------ ------------------------- ----------------------------
Sales InvoiceLines NCCX_Sales_InvoiceLines NONCLUSTERED COLUMNSTORE 0.000000000000000
Sales OrderLines NCCX_Sales_OrderLines NONCLUSTERED COLUMNSTORE 0.000000000000000
Warehouse StockItemTransactions CCX_Warehouse_StockItemTransactions CLUSTERED COLUMNSTORE 4.225346161484279
Nota
Para obtener más ejemplos sobre el uso de Transact-SQL para volver a generar o reorganizar los índices, consulte Ejemplos de ALTER INDEX: Índices de almacén de filas y Ejemplos de ALTER INDEX: Índices de almacén de columnas.
En el siguiente ejemplo se reorganiza el índice IX_Employee_OrganizationalLevel_OrganizationalNode
en la tabla HumanResources.Employee
de la base de datos AdventureWorks2022
.
ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
ON HumanResources.Employee
REORGANIZE;
En el siguiente ejemplo se reorganiza el índice de almacén de columnas IndFactResellerSalesXL_CCI
en la tabla dbo.FactResellerSalesXL_CCI
de la base de datos AdventureWorksDW2022
. Este comando fuerza todos los grupos de filas con estado CLOSED y OPEN hacia el almacén de columnas.
-- This command forces all closed and open row groups into columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
ON FactResellerSalesXL_CCI
REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);
En el siguiente ejemplo se reorganizan todos los índices en la tabla HumanResources.Employee
de la base de datos AdventureWorks2022
.
ALTER INDEX ALL ON HumanResources.Employee
REORGANIZE;
En el siguiente ejemplo se regenera un único índice en la tabla Employee
de la base de datos AdventureWorks2022
.
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;
En el ejemplo siguiente se vuelven a generar todos los índices asociados con la tabla de la base de datos de AdventureWorks2022
mediante la palabra clave ALL
. Se especifican tres opciones.
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
;
Para más información, consulte ALTER INDEX.
Eventos
31 mar, 23 - 2 abr, 23
Evento de aprendizaje de SQL, Fabric y Power BI más grande. 31 de marzo – 2 de abril. Use el código FABINSIDER para ahorrar $400.
Regístrate hoyCursos
Módulo
Configuración de bases de datos para el rendimiento óptimo - Training
Configuración de bases de datos para el rendimiento óptimo
Documentación
ALTER INDEX (Transact-SQL) - SQL Server
Modifica un índice existente de una tabla o una vista (almacén de filas, almacén de columnas o XML) mediante su deshabilitación, regeneración o reorganización, o mediante el establecimiento de sus opciones.
sys.dm_db_index_physical_stats (Transact-SQL) - SQL Server
Devuelve información de tamaño y fragmentación de los datos e índices de la tabla o vista especificadas en el Motor de base de datos de SQL Server.
Tarea Volver a generar índice (Plan de mantenimiento) - SQL Server
Obtenga información sobre cómo volver a crear los índices de las tablas de la base de datos de SQL Server con un nuevo factor de relleno mediante la tarea Recompilar índice.