vista trucos tips querys para optimizar optimas grandes consultas consulta acelerar sql sql-server database performance

sql - tips - Trucos de ajuste de rendimiento favoritos



trucos consultas sql (29)

Cuando tiene una consulta o procedimiento almacenado que necesita ajuste de rendimiento, ¿cuáles son algunas de las primeras cosas que intenta?

  1. Tenga una idea bastante buena de la ruta óptima para ejecutar la consulta en su cabeza.
  2. Verifique el plan de consulta, siempre.
  3. Active STATS para que pueda examinar tanto el rendimiento de IO como el de la CPU. Concéntrese en reducir esos números, no necesariamente en el tiempo de consulta (ya que puede estar influenciado por otra actividad, caché, etc.).
  4. Busque un gran número de filas entrando en un operador, pero salen pequeños números. Por lo general, un índice ayudaría al limitar el número de filas entrantes (lo que ahorra lecturas de disco).
  5. Enfócate primero en el subárbol de mayor costo. Cambiar ese subárbol a menudo puede cambiar todo el plan de consulta.
  6. Los problemas más comunes que he visto son:
    • Si hay muchas combinaciones, a veces el servidor SQL elegirá expandir las uniones y luego aplicar las cláusulas WHERE. Por lo general, puede solucionar esto moviendo las condiciones WHERE a la cláusula JOIN, o una tabla derivada con las condiciones inlined. Las vistas pueden causar los mismos problemas.
    • Uniones subóptimas (LOOP vs HASH vs MERGE). Mi regla empírica es utilizar una unión LOOP cuando la fila superior tiene muy pocas filas en comparación con la parte inferior, un MERGE cuando los conjuntos son más o menos iguales y ordenados, y un HASH para todo lo demás. Agregar una sugerencia de unión le permitirá probar su teoría.
    • Olfateo de parámetros Si ejecutó el proceso almacenado con valores poco realistas al principio (por ejemplo, para probar), entonces el plan de consulta en caché puede ser subóptimo para sus valores de producción. Correr de nuevo CON RECOMPILE debe verificar esto. Para algunos procesos almacenados, especialmente aquellos que tratan con rangos de tamaño variable (por ejemplo, todas las fechas entre hoy y ayer, lo que implicaría un INDEX SEEK o todas las fechas entre el año pasado y este año), lo cual sería mejor con un INDEX SCAN ) puede que tenga que ejecutarlo CON RECOMPULE cada vez.
    • Mala sangría ... De acuerdo, el Servidor SQL no tiene un problema con esto, pero seguro que es imposible entender una consulta hasta que haya arreglado el formateo.

@ DavidM

Asumiendo MySQL aquí, use EXPLAIN para averiguar qué está pasando con la consulta, asegúrese de que los índices se usen de la manera más eficiente posible ...

En SQL Server, el plan de ejecución le proporciona lo mismo: le informa qué índices están siendo aciertos, etc.

@Terrapin hay algunas otras diferencias entre isnull y coalesce que vale la pena mencionar (además del cumplimiento de ANSI, que es muy importante para mí).

Coalesce vs. IsNull

Algunas veces, en SQL Server, si usas un OR en una cláusula WHERE, realmente funcionará con el rendimiento. En lugar de usar el quirófano simplemente haga dos selecciones y únelas juntas. Obtienes los mismos resultados a 1000 veces la velocidad.

Aquí está la lista práctica de las cosas que siempre le doy a alguien que me pregunta sobre la optimización.
Principalmente usamos Sybase, pero la mayoría de los consejos se aplicarán en todos los ámbitos.

SQL Server, por ejemplo, viene con una gran cantidad de bits de monitoreo / ajuste del rendimiento, pero si no tiene algo así (y tal vez incluso si lo hace), entonces consideraría lo siguiente ...

El 99% de los problemas que he visto son causados ​​por poner demasiadas tablas en una combinación . La solución para esto es hacer la mitad de la combinación (con algunas de las tablas) y almacenar en caché los resultados en una tabla temporal. Luego haga el resto de la consulta uniéndose a esa tabla temporal.

Lista de verificación de optimización de consultas

  • Ejecute ESTADÍSTICAS ACTUALIZADAS en las tablas subyacentes
    • Muchos sistemas ejecutan esto como un trabajo semanal programado
  • Eliminar registros de tablas subyacentes (posiblemente archivar los registros eliminados)
    • Considere hacer esto automáticamente una vez al día o una vez a la semana.
  • Reconstruir índices
  • Rebuild Tables (salida de datos bcp)
  • Volcar / volver a cargar la base de datos (drástica, pero podría corregir la corrupción)
  • Crear un índice nuevo y más apropiado
  • Ejecute DBCC para ver si hay corrupción posible en la base de datos
  • Bloqueos / Bloqueos
    • Asegúrese de que no haya otros procesos ejecutándose en la base de datos
      • Especialmente DBCC
    • ¿Estás usando el bloqueo de nivel de página o fila?
    • Bloquee las tablas exclusivamente antes de comenzar la consulta
    • Verifique que todos los procesos accedan a las tablas en el mismo orden
  • ¿Los índices están siendo utilizados apropiadamente?
    • Joins solo usará el índice si ambas expresiones son exactamente del mismo tipo de datos
    • El índice solo se usará si el primer campo (s) del índice coincide en la consulta
    • ¿Se utilizan índices agrupados cuando corresponde?
      • datos de rango
      • DONDE campo entre valor1 y valor2
  • Las pequeñas uniones son buenas uniones
    • De forma predeterminada, el optimizador solo tendrá en cuenta las tablas 4 a la vez.
    • Esto significa que en combinación con más de 4 tablas, tiene buenas posibilidades de elegir un plan de consulta no óptimo.
  • Romper la unión
    • ¿Puedes romper la unión?
    • Preseleccione las claves externas en una tabla temporal
    • Haz la mitad de la unión y pon los resultados en una tabla temporal
  • ¿Estás usando el tipo correcto de tabla temporal?
    • #temp tablas #temp pueden funcionar mucho mejor que @table variables @table con grandes volúmenes (miles de filas).
  • Mantener tablas resumidas
    • Construir con desencadenantes en las tablas subyacentes
    • Construir diariamente / por hora / etc.
    • Crear ad-hoc
    • Construir incrementalmente o desmontar / reconstruir
  • Vea cuál es el plan de consulta con SET SHOWPLAN ON
  • Vea lo que sucede realmente con SET STATS IO ON
  • Forzar un índice usando pragma: (index: myindex)
  • Forzar el orden de la tabla usando SET FORCEPLAN ON
  • Parameter Sniffing:
    • Rompa el procedimiento almacenado en 2
    • llame a proc2 desde proc1
    • permite que el optimizador elija el índice en proc2 si el parámetro @ ha sido cambiado por proc1
  • ¿Puedes mejorar tu hardware?
  • ¿A qué hora estás corriendo? ¿Hay un momento más tranquilo?
  • ¿Se está ejecutando el Servidor de Replicación (u otro proceso non-stop)? ¿Puedes suspenderlo? Ejecutar por ej. ¿cada hora?

Asegúrese de que la longitud de sus índices sea lo más pequeña posible. Esto permite que el DB lea más claves a la vez desde el sistema de archivos, acelerando así sus uniones. Supongo que esto funciona con todos los DB, pero sé que es una recomendación específica para MySQL.

Convierta las consultas NOT IN en LEFT OUTER JOINS si es posible. Por ejemplo, si desea encontrar todas las filas en la Tabla 1 que no están siendo utilizadas por una clave externa en la Tabla 2, puede hacer esto:

SELECT * FROM Table1 WHERE Table1.ID NOT IN ( SELECT Table1ID FROM Table2)

Pero obtienes un rendimiento mucho mejor con esto:

SELECT Table1.* FROM Table1 LEFT OUTER JOIN Table2 ON Table1.ID = Table2.Table1ID WHERE Table2.ID is null

Ejecutar la consulta utilizando WITH (NoLock) es una operación prácticamente estándar en mi lugar. Cualquiera que haya detectado consultas en las tablas de decenas de gigabytes sin él se saca y se dispara.

Elimine las llamadas de función en Sprocs, donde muchas filas llamarán a la función.

Mi colega usó llamadas a funciones (obteniendo lastlogindate de userid como ejemplo) para devolver conjuntos de registros muy amplios.

Con la tarea de optimización, reemplacé las llamadas de función en el sproc con el código de la función: obtuve muchos tiempos de ejecución de sprocs desde> 20 segundos hasta <1.

En SQL Server, use la directiva nolock. Permite que el comando de selección se complete sin tener que esperar, generalmente otras transacciones para finalizar.

SELECT * FROM Orders (nolock) where UserName = ''momma''

En general, comenzaré con las uniones: eliminaré cada una de ellas de la consulta de a una por vez y volveré a ejecutar la consulta para tener una idea si hay una unión en particular con la que tengo un problema.

En todas mis tablas temporales, me gusta agregar restricciones únicas (cuando corresponda) para hacer índices y claves principales (casi siempre).

declare @temp table( RowID int not null identity(1,1) primary key, SomeUniqueColumn varchar(25) not null, SomeNotUniqueColumn varchar(50) null, unique(SomeUniqueColumn) )

Estoy atento a:

  • Desenrolle cualquier bucle CURSOR y conviértalo en instrucciones UPDATE / INSERT basadas en conjunto.
  • Busque cualquier código de aplicación que:
    • Llama a un SP que devuelve un gran conjunto de registros,
    • Luego, en la aplicación, pasa por cada registro y llama a un SP con parámetros para actualizar los registros.
    • Convierta esto en un SP que hace todo el trabajo en una transacción.
  • Cualquier SP que hace mucha manipulación de cadenas. Es evidencia de que los datos no están estructurados correctamente / normalizados.
  • Cualquier SP que reinventa la rueda.
  • ¡Cualquier SP que no puedo entender lo que está tratando de hacer en un minuto!

He hecho un hábito de usar siempre variables de vinculación. Es posible que las variables de vinculación no sean útiles si el RDBMS no almacena en caché las sentencias de SQL. Pero si no utiliza variables de vinculación, el RDBMS no tiene la posibilidad de reutilizar los planes de ejecución de consultas y las sentencias de SQL analizadas. Los ahorros pueden ser enormes: http://www.akadia.com/services/ora_bind_variables.html . Trabajo principalmente con Oracle, pero Microsoft SQL Server funciona de la misma manera.

En mi experiencia, si no sabes si estás usando o no variables de vinculación, probablemente no lo estés. Si el idioma de su aplicación no los admite, busque uno que sí lo haga. A veces puede corregir la consulta A utilizando variables de enlace para la consulta B.

Después de eso, hablo con nuestro DBA para averiguar qué causa más daño a RDBMS. Tenga en cuenta que no debe preguntar "¿Por qué esta consulta es lenta?" Es como pedirle a su médico que le saque el apéndice. Claro que su consulta podría ser el problema, pero es tan probable que algo más esté fallando. Como desarrolladores, tendemos a pensar en términos de líneas de código. Si una línea es lenta, arregla esa línea. Pero un RDBMS es un sistema realmente complicado y su consulta lenta podría ser el síntoma de un problema mucho más grande.

Demasiados consejos de ajuste SQL son ídolos de culto a la carga. La mayoría de las veces el problema no está relacionado o está relacionado mínimamente con la sintaxis que utiliza, por lo que normalmente es mejor usar la sintaxis más limpia que pueda. Luego puede comenzar a buscar formas de sintonizar la base de datos (no la consulta). Solo modifique la sintaxis cuando eso falle.

Al igual que cualquier ajuste de rendimiento, siempre recopile estadísticas significativas. No use el tiempo de reloj de pared a menos que sea la experiencia del usuario que esté sintonizando. En cambio, observe cosas como el tiempo de CPU, las filas que se buscan y los bloques leídos del disco. Demasiado a menudo la gente optimiza para algo equivocado.

Indexe la (s) tabla (s) por la (s) clm (s) que filtra por

Mire la cláusula where: verifique el uso de índices / verifique que no se haga nada tonto

where SomeComplicatedFunctionOf(table.Column) = @param --silly

No necesariamente un truco de rendimiento SQL per se pero definitivamente relacionado:

Una buena idea sería usar memcached siempre que sea posible, ya que sería mucho más rápido simplemente obtener los datos precompilados directamente de la memoria en lugar de obtenerlos de la base de datos. También hay un sabor de MySQL que tiene incorporado memcached (de terceros).

No prefija los nombres de procedimientos almacenados con "sp_" porque todos los procedimientos del sistema comienzan con "sp_", y SQL Server tendrá que buscar más para encontrar su procedimiento cuando se llame.

Primer paso: ¡mira el plan de ejecución de consultas!
TableScan -> malo
NestedLoop -> meh advertencia
TableScan detrás de un NestedLoop -> DOOM!

ESTABLECER ESTADÍSTICAS IO EN
ESTABLECER TIEMPOS ESTADÍSTICOS

Retire los cursores donde no sea necesario.

Siempre voy primero a SQL Profiler (si se trata de un procedimiento almacenado con muchos niveles de anidamiento) o al planificador de ejecución de consultas (si se trata de unas pocas declaraciones SQL sin anidamiento). El 90% del tiempo puede encontrar el problema inmediatamente con una de estas dos herramientas.

Un poco fuera de tema pero si tienes control sobre estos problemas ...
Alto nivel y alto impacto.

  • Para entornos con alto IO, asegúrese de que sus discos sean RAID 10 o RAID 0 + 1 o alguna implementación anidada de raid 1 y raid 0.
  • No use unidades de menos de 1500K.
  • Asegúrese de que sus discos solo se utilicen para su base de datos. IE no se registra ningún SO.
  • Desactivar el crecimiento automático o una función similar. Deje que la base de datos use todo el almacenamiento anticipado. No necesariamente lo que se está utilizando actualmente.
  • diseñe su esquema y los índices para las consultas de tipo.
  • si es una tabla de tipo de registro (solo insertar) y debe estar en la base de datos, no la indexe.
  • si haces una asignación de informes (el complejo selecciona con muchas uniones), entonces deberías considerar la creación de un depósito de datos con un esquema de estrella o copo de nieve.
  • ¡No tengas miedo de replicar datos a cambio de rendimiento!

Un truco que aprendí recientemente es que SQL Server puede actualizar variables locales además de campos, en una declaración de actualización.

UPDATE table SET @variable = column = @variable + otherColumn

O la versión más legible:

UPDATE table SET @variable = @variable + otherColumn, column = @variable

Lo he usado para reemplazar los cursores / uniones complicadas cuando se implementan cálculos recursivos, y también he ganado mucho en rendimiento.

Aquí hay detalles y códigos de ejemplo que hicieron mejoras fantásticas en el rendimiento: http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx

Lecturas sucias

set transaction isolation level read uncommitted

Evita bloqueos muertos donde la integridad transaccional no es absolutamente necesaria (lo cual es generalmente cierto)

CREATE INDEX

Asegúrese de que haya índices disponibles para sus cláusulas WHERE y JOIN . Esto acelerará el acceso a los datos en gran medida.

Si su entorno es un almacén de datos o almacén, los índices abundan para casi cualquier consulta concebible.

En un entorno transaccional , el número de índices debería ser más bajo y sus definiciones más estratégicas para que el mantenimiento del índice no reduzca los recursos. (El mantenimiento del índice es cuando las hojas de un índice deben cambiarse para reflejar un cambio en la tabla subyacente, como con las operaciones INSERT, UPDATE, y DELETE ).

Además, tenga en cuenta el orden de los campos en el índice: cuanto más selectivo (mayor cardinalidad) un campo, más temprano en el índice debería aparecer. Por ejemplo, supongamos que está buscando automóviles usados:

SELECT i.make, i.model, i.price FROM dbo.inventory i WHERE i.color = ''red'' AND i.price BETWEEN 15000 AND 18000

El precio generalmente tiene una mayor cardinalidad. Puede haber solo una docena de colores disponibles, pero posiblemente miles de precios diferentes.

De estas opciones de índice, idx01 proporciona la ruta más rápida para satisfacer la consulta:

CREATE INDEX idx01 ON dbo.inventory (price, color) CREATE INDEX idx02 ON dbo.inventory (color, price)

Esto se debe a que menos automóviles satisfarán el punto de precio que la opción de color, lo que le da al motor de consulta mucha menos información para analizar.

Se sabe que tengo dos índices muy similares que difieren solo en el orden de campo para acelerar las consultas (nombre, apellido) en uno y (apellido, nombre) en el otro.

SET NOCOUNT ON

Por lo general, la primera línea dentro de mis procedimientos almacenados, a menos que realmente necesite usar @@ROWCOUNT .

  • Prefijo todas las tablas con dbo. para evitar recompilaciones
  • Ver planes de consulta y buscar escaneos de tabla / índice.
  • En 2005, explore las vistas de administración para encontrar índices faltantes.