Solución de problemas de rendimiento de base de datos de SQL Server con T-SQL

1. Evite el uso de cláusulas DISTINCT y UNION

Siempre que sea posible, evite el uso de cláusulas DISTINCT y la unión en las consultas T-SQL. Pueden afectar negativamente al rendimiento de las consultas, ya que requieren una operación de clasificación para identificar y eliminar los duplicados del conjunto de resultados de la consulta.

2. Utilice column_list para sus sentencias SELECT e INSERT

Aunque los parámetros column_list son opcionales para las instrucciones SELECT e INSERT, siempre es una buena idea proporcionar una lista de columna completa para estas declaraciones. Cuando no se especifica la lista de columna completa, SQL Server sólo resolverá una lista de columna completa cuando las instrucciones SELECT e INSERT se ejecuten. Además, sin parámetros column_list, las sentencias SELECT e INSERT pueden generar errores si cambia el esquema de tabla subyacente. Para reducir el tráfico de red y mejorar el rendimiento global de la consulta SELECT, utilice los nombres reales de las columnas en la instrucción SELECT.

3. Utilice procedimientos almacenados, no consultas de servicio pesado

Cuando sea posible, utilice procedimientos en lugar de parches de T-SQL, ya que el código en un procedimiento almacenado se ejecuta como una sola unidad o lote almacenado. Esto reduce en gran medida el tráfico de la red, ya que varias instrucciones T-SQL contenidas en el procedimiento almacenado no están obligadas a viajar a través de la red de forma individual. En lugar de ello, sólo el nombre del procedimiento almacenado y sus parámetros se transmiten por la red.

SQL Server siempre almacena en caché un plan de ejecución del procedimiento almacenado en un área de memoria de SQL Server denominada caché de procedimientos. Siempre y cuando haya suficiente memoria disponible, el procedimiento almacenado no se ejecuta con la opción WITH RECOMPILE; el procedimiento almacenado permanecerá en la memoria caché de procedimientos y ser reutilizado. Los planes SQL ad hoc creados cuando se ejecutan sentencias T-SQL ad hoc no siempre se almacenan en la caché de procedimientos. Por lo tanto, SQL Server no recupera y vuelve a cargar el procedimiento almacenado desde el disco y/o analiza, optimiza y compila el procedimiento almacenado cada vez que se ejecuta.

4. Utilice TRUNCATE TABLE en lugar de la instrucción DELETE

Cuando elimine todas las filas de una tabla, intente utilizar la instrucción TRUNCATE TABLE en lugar de la instrucción DELETE. La instrucción TRUNCATE TABLE es mucho más rápida que la instrucción DELETE sin la cláusula WHERE, ya que utiliza menos recursos del sistema y de registro de transacciones de base de datos. A diferencia de la instrucción DELETE, cuando se ejecuta la instrucción TRUNCATE TABLE, SQL Server no registra eliminaciones de filas individuales en un registro de transacciones. En su lugar, SQL Server simplemente registra las de-asignaciones de páginas que se producen como resultado de esta operación.

5. Mantenga las transacciones pequeñas

Ejecutar varias operaciones de gran envergadura al mismo tiempo aumenta la probabilidad de un callejón sin salida, así que debe mantener sus operaciones lo más cortas posible. Trate de romper las transacciones más grandes en varias transacciones más pequeñas y luego ejecute estas operaciones por lotes. Ya que los bloqueos exclusivos y de actualizaciones se mantienen más tiempo para las grandes transacciones, demasiadas transacciones grandes pueden bloquear otras actividades y llevar a un punto muerto. La ejecución de grandes transacciones en lotes ayuda a minimizar los viajes redondos de la red durante la operación, lo que reduce los retrasos en la realización de la transacción y la liberación de los bloqueos.

6. Reduzca el tiempo de transacción

Puede reducir el tiempo de transacción asegurándose de que usted no está realizando las mismas lecturas una y otra vez. Si la aplicación tiene que leer los mismos datos más de una vez, entonces se pueden almacenar en caché los datos en variables, tablas temporales o variables de tabla. A continuación, se pueden volver a leer los datos de la memoria caché. Esto ayudará a reducir el tiempo de bloqueo de los recursos reales. Para reducir el tiempo de bloqueo, asegúrese de que la aplicación toma los bloqueos en el último momento posible y los libera a la mayor brevedad posible.

7. Evite en lo posible los cursores

Evite los cursores tanto como sea posible. En su lugar, utilice un enfoque basado en la configuración para actualizar o insertar datos de una tabla a otra. Las mismas reglas de bloqueo se aplican a una instrucción SELECT en una definición de cursor que se aplican a cualquier otra instrucción SELECT. Al utilizar los cursores, asegúrese de tener el nivel de aislamiento correcto o de haber especificado la sugerencia de bloqueo para la instrucción SELECT de su cursor. SQL Server mantiene los bloqueos de las instrucciones SELECT en un cursor y en la instrucción SELECT independiente hasta que complete ambas operaciones. Esto sólo se aplica si SQL Server se ejecuta en modo de transacción explícita o implícita.

8. Elija los tipos de datos adecuados para las tablas, procedimientos almacenados, y variables

El tipo de datos determina la clase de datos que se pueden almacenar en una columna de la tabla de base de datos. Al crear una tabla, usted tiene que escoger el tipo de datos que se utiliza para las definiciones de columna. También puede utilizar los tipos de datos para definir los parámetros de entrada y salida de las variables y el proceso de almacenamiento. Para cada columna y variables, seleccione un tipo de datos adecuado para los datos almacenados en ella. Además, tenga en cuenta los requisitos de almacenamiento y seleccione los tipos de datos que permiten el almacenamiento eficiente. La elección de los tipos de datos adecuados para las tablas, procedimientos y variables almacenadas también mejora la integridad de los datos, garantizando que sólo el tipo correcto de datos se almacenen en una base de datos.

Sobre el autor: Basit Farooq es administrador de bases de datos, capacitador y autor técnico. Tiene más de una década de experiencia en TI y desarrollo, entrenamiento técnico y administración de bases de datos en plataformas Microsoft SQL Server. Ha escrito numerosos artículos técnicos sobre SQL Server. También ha desarrollado e implementado muchos proyectos exitosos de infraestructura de bases de datos, data warehouse e inteligencia de negocios. Tiene una maestría en Ciencias de la Computación por la Universidad Metropolitana de Londres y certificaciones estándar de la industria por parte de Microsoft, Sun, Brainbech, Prosoft y APM, incluyendo: MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 y MCTS .NET Framework 2.0 Web Applications.