Índice de mantenimiento de SQL Server Optimizar el rendimiento de tablas grandes

No es nada realmente nuevo que los índices tienen su derecho a existir en SQL Server. También es nada nuevo, que estos índices deben mantenerse de vez en cuando, aunque esto podría ser sorprendente para la gente que se arrullado por el mantenimiento de otro modo bajo para SQL Server. Pero si lo piensas detenidamente, te darás cuenta, que el mantenimiento de índices es necesario y que tenga sentido, que SQL Server no se está haciendo esta tarea de forma automática a ciegas de acuerdo con el libro.
Los diversos ambientes en los que SQL Server se utiliza simplemente son demasiado diferentes a agrupar a todos juntos en relación con el mantenimiento de índices. ¿Qué podría tener efectos significativos en el rendimiento en un entorno podría ser aceptable en otro ambiente, donde las consultas se ejecutan completamente diferente, por lo que tal vez un sub óptima mantenido índice sigue siendo "bastante buena".
Pero incluso la palabra "óptimo" en la frase anterior puede ser objeto de gran controversia. ¿Qué es "óptima"? Microsoft recomienda la reorganización de un índice con una fragmentación entre el 5% y 30%, y la reconstrucción de un índice con una fragmentación de más del 30%. Usted puede leer esto aquí . Hasta ahora, todo bien. Pero como con todas las recomendaciones hay que preguntarles, si todavía tiene sentido en su propio ambiente o no. Esto es especialmente cierto, cuando usted lee lo que la ciencia de cohetes que vuelan alto, mientras que se aplicó el cálculo de estos números: hacer los libros de índices en línea vienen los umbrales de la fragmentación de ¿Dónde :-)
En nuestro medio tenemos una tabla, en la que se inserta entre el 10 y 20 millones de filas todos los días. Cada noche se corre un procedimiento de mantenimiento que elimina varios millones de filas que tienen más de x días. Así, el incremento neto de esta tabla es entre 3 - 5 millones de filas todos los días. Actualmente, esta tabla contiene 975 millones filas.
La determinación de la fragmentación se ha vuelto muy fácil, ya que SQL Server 2005. Usted puede simplemente utilizar la función de administración dinámica sys.dm_db_index_physical_stats . Aquí está una parte de la secuencia de comandos con los que comenzó un día:

SELECCIONAR OBJECT_SCHEMA_NAME (FRAG. [object_id]) + '.' + OBJECT_NAME (FRAG. [object_id]), SEIS [nombre], FRAG.avg_fragmentation_in_percent, FRAG.page_count sys.dm_db_index_physical_stats DE (DB_ID (), -. Uso de la base de datos conectada actualmente 0, - parámetros de object_id DEFAULT. - -parámetro para index_id 0, -. parámetro para partition_number DEFECTO -.... modo de escaneo por defecto a "LIMITADA", que es lo suficientemente bueno) FRAG SE UNEN EN SEIS sys.indexes FRAG [object_id] = SEIS [object_id] Y FRAG . index_id = SIX.index_id DONDE - no te moleste con montones, si tenemos estas tablas puesta en escena de todos modos fuera. "Pila" FRAG.index_type_desc <> Y (- Cualquiera de considerar sólo los índices que necesitan tratamiento (FRAG.page_count> @ IPageCnt Y FRAG.avg_fragmentation_in_percent> @ IMinFragmentation) O - o hacer todo cuando se MaintenanceDay @ IsMaintenanceDay = 1) ORDER BY DESC FRAG.avg_fragmentation_in_percent;

Las filas que se devuelven se insertan en una tabla temporal y se utilizan uno después del otro. Después de cada ejecución se determina si se puede iniciar otra operación en la ventana de nuestro mantenimiento diario o no.
En la cláusula WHERE filtramos por varios criterios:

• @ IPageCnt: El valor por defecto es 128. Cualquier cosa por debajo de este umbral se ignoran. Los beneficios aquí no justifican los esfuerzos.
• @ IMinFragmentation: Por defecto es 10%. Cualquier cosa por debajo del 10% se ignora. No hemos podido observar que ninguna de rendimiento significativo éxito con los niveles de fragmentación <10% dado nuestro volumen de trabajo.
• @ IsMaintenanceDay: Una vez a la semana tenemos una ventana de mantenimiento, en los que podemos mantener todos los índices. Por lo menos pensamos así, cuando empezamos ...

Dependiendo del nivel de fragmentación que fue devuelto, ya sea reorganizado o reconstruido los índices, como se sugirió por la recomendación anterior de MS. Esto fue todo bien hasta que llegamos a la oficina un lunes por la mañana, sólo para descubrir que nuestro trabajo índice corrió para más de 5 horas y que el trabajo de copia de seguridad programada no le hizo gracia realmente acerca de ello en absoluto.