Qué es mejor entre el escaneo de índice agrupado y la búsqueda de índice no agrupado?

17 Jun, 2023 Programación 0

Estoy tratando de encontrar cuál es la mejor opción a elegir. Mi requisito principal es reducir la E/S.

Tengo una tabla con 500 millones de registros, donde la consulta mencionada a continuación selecciona el escaneo del índice agrupado predeterminado en la tabla.

Intenté crear un índice no agrupado cubierto, pero todavía elige el escaneo del índice agrupado como predeterminado. Por lo tanto, lo he forzado a utilizar el índice cubierto y mis observaciones son que las lecturas lógicas bajaron de 3M a 1M, pero la CPU y la duración se aumentaron.

Estoy tratando de entender el comportamiento y qué es lo mejor aquí.

Consulta:

set statistics time, io on;
select
min(CampaignID),
max(CampaignID)
from Campaign
where datecreated < dateadd(day, -90, getutcdate())
go
CREATE NONCLUSTERED INDEX [NCIX]
ON [dbo].Campaign
INCLUDE (Campaignid)
go
select
min(CampaignID),
max(CampaignID)
from Campaign with (index = NCIX)
where datecreated < dateadd(day, -90, getutcdate())
set statistics time, io off;

Mensajes:

(1 fila afectada)
Table ‘Campaign’. Recuento de exploraciones 2, lecturas lógicas 3548070, lecturas físicas 0, lecturas del servidor de páginas 0, lecturas anticipadas 0, lecturas del servidor de páginas anticipadas 0, lecturas lógicas lob 0, lecturas físicas lob 0, lecturas del servidor de páginas lob 0, lecturas anticipadas lob 0, lecturas del servidor de páginas anticipadas lob 0.

(8 filas afectadas)

(1 fila afectada)

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 14546 ms, tiempo transcurrido = 14723 ms.
Tiempo de análisis y compilación de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 3 ms.

(1 fila afectada)
Table ‘Campaign’. Recuento de exploraciones 1, lecturas lógicas 1191017, lecturas físicas 0, lecturas del servidor de páginas 0, lecturas anticipadas 19, lecturas del servidor de páginas anticipadas 0, lecturas lógicas lob 0, lecturas físicas lob 0, lecturas del servidor de páginas lob 0, lecturas anticipadas lob 0, lecturas del servidor de páginas anticipadas lob 0.

(6 filas afectadas)

(1 fila afectada)

Tiempos de ejecución de SQL Server:
Tiempo de CPU = 163953 ms, tiempo transcurrido = 164163 ms.

Planes de ejecución:

https://i.stack.imgur.com/7fpCe.jpg

https://i.stack.imgur.com/dTUk4.jpg

Plan de ejecución completo: https://www.brentozar.com/pastetheplan/?id=S1CkkcBtt

SELECT indexname, index_type_desc, avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Campaign'), OBJECT_NAME(OBJECT_ID('Campaign')), NULL, 'DETAILED')

Answer

davy.ai

June 17, 2023 at 11:37 pm
Basándose en la consulta y las observaciones proporcionadas, parece que el escaneo del índice agrupado predeterminado no es óptimo para el rendimiento de esta consulta. Si bien la creación de un índice no agrupado que cubra las columnas de la selección y el filtrado redujo los registros lógicos, también aumentó el uso de la CPU y duración.

Una posible razón podría ser que el índice no agrupado no es lo suficientemente selectivo y, por lo tanto, SQL Server está optando por realizar un escaneo de índice agrupado. Alternativamente, podría haber un alto nivel de fragmentación en el índice no agrupado, lo que conduce a un mayor E/S de disco y utilización de la CPU.

Para comprender aún más el comportamiento y encontrar la mejor opción, se pueden seguir los siguientes pasos:
1. Verificar la selectividad del índice no agrupado utilizando la siguiente consulta:
```
SELECT DATEADD(DAY, -90, GETUTCDATE()) AS CutoffDate, COUNT(*) AS NumRecords
FROM Campaign
WHERE DateCreated < DATEADD(DAY, -90, GETUTCDATE())
```
  Si el número de registros devueltos es significativamente alto, entonces el índice no agrupado puede no ser lo suficientemente selectivo y, por lo tanto, no será elegido por SQL Server.
2. Verificar el nivel de fragmentación del índice no agrupado utilizando la siguiente consulta:
```
SELECT indexname, index_type_desc, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Campaign'), 
     OBJECT_NAME(OBJECT_ID('Campaign')), NULL, 'DETAILED')
```
  Si el nivel de fragmentación es alto (por encima del 30%), considere reconstruir el índice no agrupado o defragmentarlo utilizando ALTER INDEX REORGANIZE.
3. Considere la posibilidad de crear un índice de columnas agrupadas en esta tabla, ya que puede reducir significativamente la E/S y mejorar el rendimiento de la consulta, especialmente para tablas grandes con agregaciones como la de esta consulta. Sin embargo, esto puede no ser factible según la versión de SQL Server que se esté utilizando y el esquema de la tabla.
En general, es importante comprender el comportamiento de la consulta y la estructura de la base de datos subyacente para identificar la mejor opción para reducir la E/S y mejorar el rendimiento.

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

Qué es mejor entre el escaneo de índice agrupado y la búsqueda de índice no agrupado?

Answer

davy.ai