Probar y optimizar un procedimiento almacenado es una tarea complicada, más aún cuando el procedimiento no lo hemos escrito nosotros.
Un procedimiento almacenado podría ejecutarse muy lentamente, afectando a la aplicación demandante. Las causas son muchas: hardware, memoria, sistema operativo, red, etc.
Podemos empezar realizando una estadística de la ejecución del procedimiento almacenado, detallando ciclos de CPU utilizados, lecturas, escrituras, tiempo de duración, observaciones, etc.
Vamos llevando esos datos mientras realizamos cambios, de modo que podamos tener un control, que cambio afecta o mejora el desempeño del procedimiento.
Antes de hacer las mediciones del desempeño de los procedimientos hay que ejecutar las siguientes comandos en el Analizador de Consultas:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Esto "limpia" los buffers y el cache de Sql Server, de modo que permite un análisis más exacto del rendimiento del procedimiento. Ya el resto depende de quien analice el procedimiento, aunque podemos revisar los siguientes puntos:
- Evitar crear tablas temporales, sentencias sql dinamicas y joins.
- Cuidar que la salida del procedimiento almacenado sea la misma que antes de la optimización.
- Debemos de ver cual es la ejecución de cada sentencia DML.
- Evitar los Table Scan.
- Que las sentencias SQL sean escritas en formato ANSI, no ayuda mucho al desempeño, pero si a entenderlas.
Como se ve no hay reglas exactas para optimizar procedimientos almacenados, pero si ciertas reglas generales.
