sql - tabla - Cómo mejorar el rendimiento en una búsqueda de índice agrupado

optimizar consultas sql server 2008 r2

Question

Estoy tratando de mejorar el rendimiento en una consulta que se ejecuta muy lentamente. Después de pasar por el Plan de Ejecución Real ; Descubrí que una búsqueda de índice agrupada estaba ocupando el 82%. ¿Hay alguna forma de mejorar el rendimiento en una búsqueda de índice ? A continuación se muestra una imagen del problema Búsqueda de índice del plan de ejecución, así como el índice y la tabla que está utilizando.

texto alt http://img340.imageshack.us/img340/1346/seek.png

Índice:

/****** Object: Index [IX_Stu] Script Date: 12/28/2009 11:11:43 ******/
CREATE CLUSTERED INDEX [IX_Stu] ON [dbo].[stu]
(
[StuKey] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

Tabla (algunas columnas omitidas por brevedad):

CREATE TABLE [dbo].[stu](
[StuCertKey] [int] IDENTITY(1,1) NOT NULL,
[StuKey] [int] NULL
CONSTRAINT [PK_Stu] PRIMARY KEY NONCLUSTERED
(
[StuCertKey] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

¿Has probado algún mantenimiento en este índice? Como desfragmentarlo? Parece realmente extraño que cuesta tanto (120.381). La búsqueda de índice es la operación de índice más rápida, no debería tomar tanto tiempo. ¿Puedes publicar la consulta?

Answer 2

¿Qué sucede si codificas de forma rígida tus criterios de DÓNDE ?

SELECT StuCertKey, StuKey FROM stu
WHERE stuKey in (/* list 50 values of StuKey here */)

Si aún es muy lento, tienes un problema interno de algún tipo. Si es más rápido, entonces el índice no es su cuello de botella, son las UNIONES que está haciendo para crear el filtro DÓNDE .

Tenga en cuenta que SELECT * puede ser muy lento si hay muchas columnas grandes, y especialmente si hay BLOBs.

Answer 3

¿Reconstruir el índice y calcular estadísticas?

La única otra forma en que puedo pensar para acelerarla es dividir la tabla, lo que puede o no ser posible.

Answer 4

Algunos consejos generales: cuando tengo que hacer la optimización de consultas, empiezo por escribir lo que creo que debería ser el plan de ejecución.

Una vez que he decidido cuál creo que debería ser el plan de ejecución, trato de hacer que la consulta real se ajuste a este plan. Las técnicas para hacer esto son diferentes para cada DBMS, y no necesariamente se transfieren de una a otra, o incluso, a veces, entre diferentes versiones de la DBMS.

Lo que hay que tener en cuenta es que el DBMS solo puede ejecutar una combinación a la vez: comienza con dos tablas iniciales, se une a esas, y luego toma el resultado de esa operación y lo une a la siguiente tabla. El objetivo en cada paso es minimizar el número de filas en el conjunto de resultados intermedios (más correctamente, minimizar el número de bloques que deben leerse para producir los resultados intermedios, pero esto generalmente significa menos filas).

Answer 5

Compruebe el índice estático.

volver a calcular las estadísticas del índice agrupado resolverá el problema.

En mi caso, estaba buscando 30 registros en 40M grabados. el plan de ejecución dice que está pasando por el índice agrupado pero que tomó cerca de 200 ms. y el índice no fue desfragmentado. Después de recalcular sus estadísticas, ¡se está haciendo en 10 ms!

Answer 6

Estoy generalizando aquí, pero ...

Una búsqueda de índice agrupado es, en su mayor parte, el mejor escenario posible. Las únicas maneras en que puedo pensar para mejorar el rendimiento serían:

• Actualice la consulta para devolver menos filas / columnas, si es posible;
• Desfragmentar o reconstruir el índice;
• Particione el índice en varios discos / servidores.

Si solo está devolviendo 138 filas y es tan lento ... ¿quizás esté bloqueado por algún otro proceso? ¿Está probando esto de forma aislada, o hay otros usuarios / procesos en línea al mismo tiempo? O tal vez es incluso un problema de hardware, como una falla de disco.

Answer 7

Las búsquedas en índices agrupados ocurren cuando se usan índices no agrupados y no son necesariamente malos.

Considere la siguiente consulta:

SELECT s.StuKey, s.Name, s.Address, s.City, s.State FROM stu s WHERE State=''TX''

Si solo hay un índice agrupado en StuKey, entonces el servidor Sql solo tiene 1 opción, debe escanear toda la tabla en busca de filas donde Estado = "TX ''y devolver esas filas.

Si agrega un índice no agrupado en estado

CREATE INDEX IX_Stu_State on Stu (State)

Ahora el servidor SQL tiene una nueva opción. Puede elegir buscar usando el índice no agrupado, que producirá las filas donde Estado = ''TX''. Sin embargo, para que las columnas restantes regresen en SELECT, debe buscar esas columnas haciendo una búsqueda de índice agrupado para cada fila.

Si desea reducir las búsquedas de índices agrupados, puede hacer que su índice se "cubra" incluyendo columnas adicionales en él.

 CREATE INDEX IX_Stu_State2 on Stu (State) INCLUDE (name, address, city )

Este índice ahora contiene todas las columnas necesarias para responder a la consulta anterior. La consulta realizará una búsqueda de índice para devolver solo las filas donde Estado = ''TX'', y las columnas adicionales se pueden extraer del índice no agrupado, por lo que la búsqueda de índice agrupado desaparecerá.

Answer 8

Pensamientos ...

• ¿Por qué se agrupan los IX_Stu? Internamente, SQL Server agrega un "unificador" de 4 bytes a los índices agrupados no únicos. ¿Cuál es la justificación? Esto también aumenta tu PK también

• ¿Cuál es la consulta real que está ejecutando?

• Finalmente, ¿por qué FILLFACTOR 80%?

Editar:

• Un FILLFACTOR "normal" sería del 90%, pero esta es solo una regla de oro

• Una consulta de unión 11? Probablemente ese sea tu problema. ¿Cuáles son sus cláusulas JOINs, WHERE, etc.? ¿Cuál es el plan de texto completo?

Answer 9

Una búsqueda de rango de índice agrupado que devuelve 138 filas no es su problema.

Técnicamente, puede mejorar el rendimiento de búsqueda haciendo que el índice agrupado sea más estrecho:

• desaloje todas las variables en una unidad de asignación separada estableciendo "tipos de valores grandes fuera de fila " en 1 y volviendo a crear la tabla desde cero.
• habilitar compresión de página (solo SQL 2008 EE).

Ambos pueden tener un impacto dramático en el tiempo de búsqueda de rango, ya que reducen el IO y la necesidad de realizar lecturas físicas. Por supuesto, como suele suceder, el resultado variará en un gran número de otros factores, como las columnas que proyecta (el desalojo de una columna proyectada en la unidad de asignación BLOB puede tener efectos adversos en ciertas consultas). Como nota al margen, generalmente la fragmentación solo tendrá un impacto marginal en un escaneo de tan corto alcance. Una vez más, depende.

Pero como digo, dudo mucho que este sea tu verdadero problema. Solo ha publicado partes seleccionadas del plan y los resultados de su propio análisis. La verdadera causa raíz puede estar completamente en otra parte.