sql - tuning - ¿Cómo aumentar el rendimiento de una base de datos?

plan de ejecución y optimización de consultas sql server 2008 r2 (10)

He diseñado bases de datos varias veces en mi compañía. Para aumentar el rendimiento de la base de datos, busco solo Normalización e Indexación.

Si le pidieron que aumente el rendimiento de una base de datos que tiene aproximadamente 250 tablas y algunas tablas con millones de registros, ¿qué cosas diferentes buscaría?

Gracias por adelantado.

Esa es una pregunta muy vaga.

Dice que busca la indexación, pero no puede ver la indexación aisladamente. Debe observar las consultas que se están ejecutando, los planes de ejecución, los índices que se usan y cómo se usan. La herramienta Profiler puede ayudar mucho al determinar qué consultas son ineficaces.

Aparte de eso, asegúrese de que esté configurado un plan de mantenimiento. Debería actualizar estadísticas y desfragmentar / reconstruir índices al menos una vez a la semana en una base de datos transaccional pesada.

Si tiene la infraestructura, mire la configuración de su archivo y grupo de archivos. Debería intentar colocar tablas y / o índices que sean grandes y se usen con frecuencia en diferentes unidades físicas, si es posible. Si tiene tablas muy grandes, puede pensar en particionarlas.

Si todavía tiene problemas de rendimiento, la desnormalización a veces puede ayudar, pero todo depende de la situación.

Me voy a detener allí: no quiero que esta respuesta se convierta en la lista más aleatoria del mundo de sugerencias de rendimiento de SQL. Recomiendo que sea más específico sobre dónde cree que están los problemas de rendimiento y cuéntenos un poco más sobre la base de datos (tamaño, estrategia de indexación actual, frecuencia de las transacciones, cualquier informe grande que necesite generar, etc.)

Hay muchas cosas que podrías hacer, muchas de ellas ya sugeridas anteriormente. Algunos que miraría (en este orden):

Errores / registros: muchos motores db tienen herramientas de informes que señalan áreas problemáticas en una base de datos. Comience aquí para ver si hay algo en lo que pueda concentrarse de inmediato.
Retención de datos: compruebe la especificación comercial de cuánto tiempo se deben guardar los datos, asegúrese de que los datos más antiguos se trasladen a un almacén de datos para mantener el tamaño de la tabla pequeño. (¿Por qué mantener 5 años de datos si solo necesitan los últimos 3 meses?)
Busque escaneos de tabla, indexe los datos si esto ayuda (tiene que calibrar este frente a las escrituras de la tabla). Los registros de su servidor probablemente lo ayuden a encontrar escaneos de tablas.
Elementos atómicos del trabajo: ¿algunas escrituras mantienen bloqueos en diferentes tablas antes de llegar a un punto de compromiso? ¿Pueden simplificarse esos elementos de trabajo o moverse puntos de compromiso para acelerar el rendimiento? Aquí es donde necesitarás que un desarrollador mire el código.
Busque sentencias de SQL de larga ejecución, ¿puede hacerse más eficiente? A veces, las consultas mal estructuradas pueden arruinar una aplicación. Es posible que deba sugerir un cambio de codificación para mejorar el rendimiento.
dba realm: mire cómo se asignan las tablas: tamaño de página, segmentos múltiples, etc. Aquí es donde las herramientas de diagnóstico del proveedor son útiles, ya que a menudo pueden sugerir cómo puede estructurar una tabla en función del historial de uso. Un dba experimentado sería útil aquí.
busca los cuellos de botella de hardware / red. Aquí es donde necesitarías un tipo de hardware. :)

Estos son realmente de alto nivel, también echaría un vistazo a lo que el proveedor de su motor de BD sugiere como mejoras de rendimiento.

Además, calculo una lista como esta contra lo que mi jefe está dispuesto a pagar y cuánto tiempo tengo. ;)

Espero que esto ayude.

No hemos escrito sobre un bit de rendimiento:

Hardware.

Las bases de datos son intensamente impulsadas por E / S. Pasar a un disco duro más rápido debería aumentar la velocidad de las consultas a la base de datos. Dividir la base de datos entre muchos discos duros rápidos podría mejorarlo aún más.

Optimizar las consultas que se utilizan para acceder a esa base de datos es lo más importante. Solo agregando índices no garantiza que las consultas los usarán.

Para aumentar el rendimiento, primero deberá supervisar su base de datos. Puede rastrear y luego cargarlo en el perfil de servidor SQL para averiguar cuáles son las consultas más lentas. Después de eso, puedes concentrarte en ellos.

También puede usar vistas dinámicas y funciones de administración para averiguar qué índices faltan. También podrá recuperar estadísticas sobre índices existentes, como el uso del índice y los índices perdidos.

Para su conjunto de herramientas de normalización e indexación, con tablas extremadamente grandes, también puede considerar los pros y los contras de la partición de las tablas. Pero ya tienes los más importantes.

Si una consulta es extremadamente crítica, puede considerar la desnormalización para reducir el número de búsquedas de tabla por consulta. Aparte de eso, si necesita más rendimiento más allá de lo que pueden realizar la indexación y la desincronización, le recomendamos que busque el lado del programa: almacenamiento en caché, optimización de consultas / procedimientos almacenados, etc.

Optimizar el diseño lógico

El nivel lógico es sobre la estructura de la consulta y las tablas. Intenta maximizar esto primero. El objetivo es acceder a la menor cantidad posible de datos en el nivel lógico.

Tener las consultas SQL más eficientes
Diseñe un esquema lógico que respalde las necesidades de la aplicación (por ejemplo, tipo de columnas, etc.)
Diseño de compromiso para apoyar algunos casos de uso mejor que otros
Restricciones relacionales
Normalización

Optimizar el diseño físico

El nivel físico se ocupa de la consideración no lógica, como el tipo de índices, los parámetros de las tablas, etc. El objetivo es optimizar el IO, que siempre es el cuello de botella. Sintonice cada tabla para que se ajuste a sus necesidades. Se puede cargar una tabla pequeña permanentemente cargada en el caché DBMS, la tabla con baja tasa de escritura puede tener diferentes configuraciones que la tabla con alta tasa de actualización para tomar menos espacios de disco, etc. Dependiendo de las consultas, se puede usar un índice diferente, etc. datos desnormalizados de forma transparente con vistas materializadas, etc.

Tablas de parámetros (tamaño de asignación, etc.)
Índices (combinados, tipos, etc.)
Parámetros para todo el sistema (tamaño de caché, etc.)
Particionamiento
Desnormalización

Intenta primero mejorar el diseño lógico, luego el diseño físico. (El límite entre ambos es sin embargo impreciso, entonces podemos discutir sobre mi categorización).

Optimizar el mantenimiento

La base de datos debe ser operada correctamente para mantenerse lo más eficiente posible. Esto incluye algunas tomas de mantenimiento que pueden tener impacto en el rendimiento, por ejemplo

Mantenga las estadísticas actualizadas
Volver a secuenciar tablas críticas periódicamente
Mantenimiento de disco
Todas las cosas del sistema para tener un servidor que las rocas

Mi rollo en MySpace fue "Mejora de rendimiento DBA / Desarrollador". Diría que la normalización y los índices son un requisito en las bases de datos de alto rendimiento, pero realmente debe analizar las estructuras e índices de la tabla para desbloquear realmente los poderes del diseño de la base de datos.

Aquí hay algunas sugerencias que tendría para usted;

Conozca el motor DB. Un conocimiento completo de la estructura de E / S subrayada va un largo camino en el diseño de un índice o tabla adecuada. Usando PerfMon y Profiler, junto con su conocimiento de lo que son las E / S de lectura / escritura, puede poner algunos números muy específicos detrás de su teoría de lo que es una solución de tabla / índice bien formada.
Comprenda la diferencia entre índices agrupados y no agrupados y cuándo usarlos.
Use sys.dm_os_waiting_tasks y sys.dm_os_wait_stats DMVs. Le dirán dónde debe esforzarse para reducir el tiempo de espera.
Use DBCC SET STATISTICS IO / TIME ON y evalúe sus planes de ejecución para ver si una consulta reduce o aumenta el número de lecturas de página o la duración.
DBCC SHOWCONTIG le dirá si sus tablas están muy fragmentadas. Los desarrolladores y los DBA Jr. a menudo lo descuidan desde el punto de vista del rendimiento; sin embargo, esto puede tener un efecto MUY GRANDE en el número de lecturas de página que tenga. Si una tabla tiene una densidad de página de 20% de extensión, eso significa que está leyendo aproximadamente 5 veces los datos que, de otro modo, sería si la tabla y sus índices se desfragmentaran.
Evaluar lecturas sucias (nolock, leer sin compromiso). Si pudieras terminar con precisión de milisegundos en las lecturas, ¡guarda los bloqueos!
Considere sacar claves foráneas innecesarias. Son útiles en entornos Dev, no en sistemas transaccionales de alto rendimiento.
Las particiones en mesas grandes suponen una gran diferencia, solo si están diseñadas correctamente.
Cambios en la aplicación: si puede programar actualizaciones por lotes para transacciones asincrónicas, colóquelas en un montón libre de índice y trátelas según lo programado para que no actualice las tablas en las que realiza una gran consulta.
Siempre Siempre Siempre !!! use la misma variable de tipo de datos para consultar las columnas de destino; Por ejemplo, la siguiente declaración utiliza una variable bigint para una columna smallint:

declare @i bigint set @i = 0

seleccione * de MyTable donde Col01SmallInt> = @i

En el proceso de evaluación de las páginas de índice / tabla, el motor de consulta puede optar por convertir sus datos de columna smallint a tipo de datos bigint. Considere, en cambio, cambiar el tipo de varialbe, o al menos convertirlo a smallint en su condición de búsqueda.

SQL 2005/08 le brinda "Informes" en la Aplicación de administración, eche un vistazo a los informes sobre el rendimiento de sus índices. ¿Están siendo escaneados, buscados? ¿Cuándo fue tu último escaneo de tabla? Si fue reciente, los índices no cumplen todas las consultas necesarias. Si tiene un índice que apenas se está usando (buscando o escaneando) pero que se actualiza constantemente, considere descartarlo. Puede ahorrarle muchos bloqueos innecesarios y bloqueos de teclas. ..

Eso es todo lo que puedo pensar en la parte superior de mi cabeza. Si se encuentra con un problema más específico, tendría una respuesta más específica para usted.

Compresión . Para la gran mayoría de las cargas que he intentado, usar compresión fue una tremenda jugada gratuita. Tamaño de datos reducido significa que la reducción de E / S significa un mejor rendimiento. En SQL Server 2005, las opciones de compresión son limitadas ( vardecimal ). Pero consideraría seriamente actualizar a 2008 solo para la compresión de páginas. O 2008 R2 si utiliza nvarchar frecuencia para obtener la compresión Unicode.

Retención de datos . Estableciendo políticas de retención y eliminando datos antiguos de manera agresiva. Menos datos significa menos E / S, significa un mejor rendimiento. A menudo esto se considera operativo, no de diseño, pero me gusta pensar en este tema como un problema de diseño de la aplicación.

Por supuesto, supongo que ya supervisa todas y cada una de las consultas para asegurarse de que ninguna haga exploraciones de tabla estúpidas de extremo a extremo.

Muchos más potenciadores de rendimiento son principalmente operativos o de implementación, no de diseño: mantenimiento (desfragmentación, reconstrucción de índices, etc.), E / S y diseño de almacenamiento, etc.

Y por último pero no menos importante, comprenda el costo oculto de varias soluciones llave en mano. Como, por ejemplo, Replicación o Duplicación de base de datos.