Recientemente acaban de enviarme para apoyar un proyecto que consiste en optimizar procedimientos almacenados en una base de datos SQL Server 2000, y pues les pongo unos tips para optimizar procedimientos almacenados.
1. Incluye siempre la sentencia SET NOCOUNT ON en tus procedimientos almacenados para evitar que salga el message que nos indica el número de registros afectos por una sentencia Transact-SQL, esto nos reducirá el trafico de red.
2. Invoca a los procedimientos almacenados usando su fully qualified name.
El nombre completo de un objeto consiste de 4 indentificadores: el nombre del servidor, nombre de la base de datos, nombre del esquema y nombre del objeto. Un nombre de objeto que especifique las 4 partes se conoce como un fully qualified name. Usando el fully qualified names eliminas una confusion acerca de cual procedimiento quieres tu ejecutar y puedes obtener una mejor performance porque el SQL Server tiene una mejor oportunidad para reusar el plan de ejecucion de procedimientos si ellos son ejecutados usando sus fully qualified names.
3. Si tu tienes un procedimiento almacenado muy largo, intenta partir el procedimiento en varios sub-procedimientos, y llama estos sub-procedimientos desde el procedimiento principal.
Los procedimientos son recompilados cuando hay un cambio en la estructura de una tabla o vista que es referenciada por el procedimiento, o cuando un numero largo de INSERTS, UPDATES o DELETES son hechos para una tabla referenciada por el procedimiento. Pero si tu dividiste el procedimiento grande en varios sub-procedimientos, cuando haya un cambio solo se recompilara el sub-procedimiento que hace referencia al objeto cambiado, en cambio los otros sub-procedimientos no seran recompilados.
4. Usa siempre loas alias para identificar las tablas usadas en tu procedimiento, asi tambien agregar siempre el esquema al que pertencen los objetos e base de datos usados en tu procedimiento almacenado.
5. Evita por sobre todo usar cursores dentro de tu procedimiento almacenado.
El motor de base de datos SQL Server no esta pensado y optimizado para trabajar con cursores, sino para trabajar con conjuntos de datos, usando otro tipo de soluciones veremos que el rendimiento de nuestro procedimiento es mucho mejor.
6. Evita usar el uso de tablas temporales dentro de tus procedimientos almacenados.
Usando tablas temporales dentro de tus procedimientos almacenados reduces la oportunidad para reusar el plan de ejecucion. Al crear una tabla temporal se obtienen bloqueos sobre la tempdb, bloqueos que afectan a todo el servidor, ademasal crear una tabla temporal se tiene acceso de escritura al disco, lo mismo sucede cuando se inserta datos en la tabla o cuando la eliminamos, con lo cual reducimos drasticamente nuestro rendimiento del procedimiento.
7. De preferencia usa siempre variables de tabla en lugar de tablas temporales
Las variables de tabla producen menos recompilaciones de los procedimientos almacenados, las variables de tabla no necesitan de bloqueos ni de tantos recursos como las tablas temporales ademas de que las variables de tabla apuntan a estructuras de memoria con lo cual producen menos overhead que las tablas temporales.
8. Usa el SQL Server Profiler para determinar cuales procedimientos almacenados son recomiplados con mayor frecuencia.
Para checkear si un procedimiento esta siendo recompilado, ejecuta el SQL Server Profiler y eleige la opción para trazear el evento en la categoria "Stored Procedures" llamada "SP:Recompile". tu puedes tambien tracear el evento "SP:StmtStarting" para determinar determinar en que punto del procedimiento empieza a ser recompilado. Cuando tu identifiques los procedimientos almacenados que son recompilados con mayor frecuencia, debes hacer las correciones respectivas para eliminar o reducir las excesivas recompilaciones.
Espero que con estos tips puedan optimizar sus procedimientos almacenados, y recuerden que con un procedimiento almacenado optimizado sus usuarios y su DBA los amarán. Hasta la proxima.
