1. Introducción
Este artículo surge a partir de la confusión generalizada que hay en la comunidad con respecto al no uso del prefijo "sp_" en el nombre de los procedimientos almacenados. Actualmente hay varios artículos publicados en la web, entre ellos el de Miguel Egea (2003) y los "Books On Line" (BOL) de Microsoft para su producto MS SQL Server 2000, que recomiendan no utilizar el prefijo "sp_" en los nombres de los procedimientos almacenados. En ambos casos la recomendación es correcta, sin embargo considero necesario ahondar en las justificaciones de la misma. Asimismo hay quienes afirman - Guerrero y Rojas (2001) - que la explicación brindada por Microsoft en los BOL es errónea y que la utilización de este prefijo no conlleva procesamiento adicional alguno. Es por ello que no he encontrado hasta el momento un artículo en castellano que logre justificar completamente por qué el uso de este prejifo no es recomendable y decidí escribir éste para contribuir de alguna manera con la comprensión del tema.
Primeramente vamos a hacer unas pruebas que nos van a inducir a que el uso del prefijo "sp_" para el nombre de procedimientos almacenados es similar al uso de cualquier otro prefijo, luego vamos a introducirnos en el funcionamiento interno de MS SQL Server 2000 para demostrar cómo lo que en apariencia parece una ejecución normal en realidad lleva al MS SQL Server 2000 a realizar un proceso extra que repercute directamente en la eficiencia de su respuesta.
2. Pruebas iniciales
La prueba inicial que se suele hacer para empezar a indagar en este tema es la de crear dos procedimientos almacenados, uno en la base de datos master y otro en, a modo de ejemplo, la base de datos tempdb. A continuación damos muestra del código que utilizaremos para realizar la primera prueba:
USE master GO CREATE PROCEDURE dbo.sp_prueba AS SELECT 'Ejecuto en master' GO USE tempdb GO CREATE PROCEDURE dbo.sp_prueba AS SELECT 'Ejecuto en tempdb' GO
Creados los procedimientos almacenados con los cuales realizaremos la primera prueba, procederemos a ejecutarlos con distintas instrucciones de invocación para observar qué resultados se obtienen. A continuación se muestra la ejecución de la primera prueba:
USE tempdb GO EXECUTE dbo.sp_prueba EXECUTE tempdb.dbo.sp_prueba EXECUTE master.dbo.sp_prueba USE master GO EXECUTE dbo.sp_prueba EXECUTE master.dbo.sp_prueba EXECUTE tempdb.dbo.sp_prueba
Los resultados que deben aparecer luego de la ejecución están expresados en las siguientes tablas que muestran los resultados de la primera prueba:
Ejecución desde la base de datos tempdb |
|
---|---|
Instrucción |
Resultado |
EXECUTE dbo.sp_prueba |
'Ejecuto en tempdb' |
EXECUTE tempdb.dbo.sp_prueba |
'Ejecuto en tempdb' |
EXECUTE master.dbo.sp_prueba |
'Ejecuto en master' |
Ejecución desde la base de datos master |
|
---|---|
Instrucción |
Resultado |
EXECUTE dbo.sp_prueba |
'Ejecuto en master' |
EXECUTE master.dbo.sp_prueba |
'Ejecuto en master' |
EXECUTE tempdb.dbo.sp_prueba |
'Ejecuto en tempdb' |
Estos resultados preliminares suelen generar mucha confusión debido a que los resultados no son los esperados. Los resultados que deberían aparecer serían los correspondientes a una ejecución que en todo momento se realizó en la base de datos master. Más adelante demostraremos que estas no son las pruebas que se deben realizar para poder justificar el no uso del prefijo "sp_".
Antes de seguir podemos realizar una segunda prueba para observar un comportamiento particular en la ejecución de los procedimientos almacenados que tiene MS SQL Server 2000. Para mostrar dicho comportamiento procederemos a eliminar el procedimiento almacenado en la base de datos tempdb y luego procederemos a ejecutar el conjunto de instrucciones de la primera prueba. A continuación se muestra la ejecución de la segunda prueba:
USE tempdb GO DROP PROCEDURE dbo.sp_prueba GO EXECUTE dbo.sp_prueba EXECUTE tempdb.dbo.sp_prueba EXECUTE master.dbo.sp_prueba USE master GO EXECUTE dbo.sp_prueba EXECUTE master.dbo.sp_prueba EXECUTE tempdb.dbo.sp_prueba
Los resultados que deben aparecer luego de la ejecución están expresados en las siguientes tablas que muestran los resultados de la segunda prueba:
Ejecución desde la base de datos tempdb |
|
---|---|
Instrucción |
Resultado |
EXECUTE dbo.sp_prueba |
'Ejecuto en master' |
EXECUTE tempdb.dbo.sp_prueba |
'Ejecuto en master' |
EXECUTE master.dbo.sp_prueba |
'Ejecuto en master' |
Ejecución desde la base de datos master |
|
---|---|
Instrucción |
Resultado |
EXECUTE dbo.sp_prueba |
'Ejecuto en master' |
EXECUTE master.dbo.sp_prueba |
'Ejecuto en master' |
EXECUTE tempdb.dbo.sp_prueba |
'Ejecuto en master' |
De estos resultados podemos asumir que al no encontrarse el procedimiento almacenado en la base de datos tempdb el motor de MS SQL Server 2000 automáticamente buscó el procedimiento almacenado en la base de datos master.
3. Creando procedimientos almacenados del sistema
Ahora vamos a introducirnos en el funcionamiento interno de MS SQL Server 2000 y vamos a empezar a ejecutar algunas instrucciones a nivel de configuración para hacer otras pruebas. Para seguir adelante tenemos que saber que MS SQL Server 2000 tiene dos formas de saber cuando un procedimiento almacenado es del sistema, o sea que pertenece al conjunto de procedimientos almacenados que el motor de MS SQL 2000 administra. La primera es mediante una marca que se le realiza al registro del procedimiento almacenado y la segunda es mediante el prefijo "sp_" como veremos más adelante.
Antes de seguir debemos eliminar cualquier rastro de los procedimientos almacenados que generamos anteriormente, por lo cual procedemos a eliminarlos. Para ello debemos ejecutar el siguiente código:
USE master GO DROP PROCEDURE dbo.sp_prueba GO USE tempdb GO DROP PROCEDURE dbo.sp_prueba GO
Ahora procederemos a crear los mismos procedimientos almacenados de la primera prueba, solo que esta vez configuraremos al primer procedimiento almacenado como un procedimiento almacenado del sistema.
USE master GO sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE GO EXEC sp_ms_upd_sysobj_category 1 GO CREATE PROCEDURE dbo.sp_prueba AS SELECT 'Ejecuto en master' GO EXEC sp_ms_upd_sysobj_category 2 GO sp_configure 'allow updates', 0 RECONFIGURE WITH OVERRIDE GO USE tempdb GO CREATE PROCEDURE dbo.sp_prueba AS SELECT 'Ejecuto en tempdb' GO
A continuación procederemos a la ejecución del conjunto de instrucciones que utilizamos para probar en la primera prueba.
USE tempdb GO EXECUTE dbo.sp_prueba EXECUTE tempdb.dbo.sp_prueba EXECUTE master.dbo.sp_prueba USE master GO EXECUTE dbo.sp_prueba EXECUTE master.dbo.sp_prueba EXECUTE tempdb.dbo.sp_prueba
Los resultados que deben aparecer luego de la ejecución están expresados en las siguientes tablas que muestran los resultados de la tercera prueba:
Ejecución desde la base de datos tempdb |
|
---|---|
Instrucción |
Resultado |
EXECUTE dbo.sp_prueba |
'Ejecuto en master' |
EXECUTE tempdb.dbo.sp_prueba |
'Ejecuto en master' |
EXECUTE master.dbo.sp_prueba |
'Ejecuto en master' |
Ejecución desde la base de datos master |
|
---|---|
Instrucción |
Resultado |
EXECUTE dbo.sp_prueba |
'Ejecuto en master' |
EXECUTE master.dbo.sp_prueba |
'Ejecuto en master' |
EXECUTE tempdb.dbo.sp_prueba |
'Ejecuto en master' |
Si observamos los resultados de esta tercera prueba llegamos a la conclusión de que nunca se ejecutó el procedimiento almacenado de la base de datos tempdb. Dicho de otra forma, podemos afirmar que en estas condiciones el procedimiento almacenado en la base de datos tempdb está ocupando un espacio innecesario debido a que la ejecución del mismo nunca podrá llevarse a cabo.
4. Desmitificando el uso del prefijo "sp_"
Hasta ahora realizamos pruebas que pueden ser realizables desde cualquier aplicación que permita la ejecución de código T-SQL y nos muestre los resultados de estas consultas, tal es el caso de MS Query Analizer. Estas pruebas nos podrían llevar a conclusiones erróneas debido a que lo que realmente sucede aún no fue reflejado completamente. Para poder seguir adelante debemos utilizar una aplicación que nos permita observar los resultados de las ejecuciones a nivel interno, para ello las siguientes pruebas se realizarán en la aplicación MS SQL Profiler, la cual forma parte del conjunto estándar de herramientas cliente de MS SQL Server 2000.
Para realizar las siguientes pruebas procederemos a invocar el aplicativo MS SQL Profiler dentro del cual seleccionaremos las opciones de menú: File | New | Trace; automáticamente aparecerá un formulario que nos permitirá seleccionar el servidor, una vez seleccionado el servidor y su autenticación al mismo aparecerá automáticamente la pantalla de "Trace Properties" (Propiedades del trace) donde deberemos ir a la solapa de "Events" (Eventos) y allí desplegar del cuadro de "Available event classes" (Tipos de eventos disponibles) el grupo de eventos pertenecientes a "Stored Procedures" (Procedimientos almacenados) donde aparecerá el evento "SP:CacheMiss", al cual seleccionaremos y agregaremos al cuadro de "Selected event classes" (Tipos de eventos seleccionados). Ver Figura 1:
Figura 1: Configuración de MS SQL Profiler.
Una vez realizados los pasos anteriores iniciamos el seguimiento de MS SQL Server 2000 haciendo clic en el botón "Run" (Iniciar). Inmediatamente pausaremos el seguimiento porque aún debemos trabajar un poco más con T-SQL y no queremos que los resultados de estas operaciones ensucien el seguimiento, con lo cual debemos seleccionar la opción de menú File | Pause Trace ... y luego la opción de menú Edit | Clear Trace Window ... con lo cual el seguimiento quedará listo para la prueba.
Ahora procederemos a ejecutar el conjunto de instrucciones que realizamos en la primera prueba; antes de hacerlo, debemos eliminar los procedimientos almacenados que utilizamos y volverlos a crear.
USE master GO DROP PROCEDURE dbo.sp_prueba GO USE tempdb GO DROP PROCEDURE dbo.sp_prueba GO USE master GO CREATE PROCEDURE dbo.sp_prueba AS SELECT 'Ejecuto en master' GO USE tempdb GO CREATE PROCEDURE dbo.sp_prueba AS SELECT 'Ejecuto en tempdb' GO
Una vez ejecutado el código de la cuarta prueba procederemos a iniciar el seguimiento en el aplicativo MS SQL Profiler seleccionando la opción de menú: File | Run Trace ... Luego ejecutaremos el siguiente conjunto de instrucciones:
USE master GO EXECUTE master.dbo.sp_prueba GO
Seguido de la ejecución podemos observar en el aplicativo MS SQL Profiler que se han producido dos eventos "SQL:BatchCompleted" que señalan la finalización de la ejecución de cada una de las instrucciones.Ver Figura 2:
Figura 2: Resultados de la cuarta prueba (I).
Ahora procederemos a ejecutar el siguiente conjunto de instrucciones:
USE tempdb GO EXECUTE tempdb.dbo.sp_prueba GO
Seguido de esta última ejecución observamos en el aplicativo MS SQL Profiler que se produjeron tres eventos: "SQL:BatchCompleted", "SP:CacheMIss" y "SQL:BatchCompleted". El nuevo evento que se produjo fue el "SP:CacheMiss" el cual significa que MS SQL Server 2000 buscó un procedimiento almacenado en su caché y no lo encontró, con lo cual MS SQL Server 2000 tuvo que recompilar el mismo.Ver Figura 3:
Figura 3: Resultados de la cuarta prueba (II).
De aquí deducimos que estamos exponiendo a MS SQL Server 2000 a un procesamiento extra que hace que busque infructuosamente en su caché de procedimientos almacenados el procedimiento almacenado "sp_prueba". Lo que en estos resultados no se refleja es el procesamiento extra que estamos haciendo hacer en MS SQL Server 2000, dado que la columna de "Duration" (Duración) es igual para ambos casos. Por lo cual podemos probar qué sucede si creamos un procedimiento almacenado con otro prefijo y así poder comparar su rendimiento. Para hacer esto podemos crear un nuevo procedimiento con el nombre "tempdb.dbo.abc_prueba" que tenga el mismo conjunto de instrucciones que nuestro viejo procedimiento almacenado "tempdb.dbo.sp_prueba".
USE tempdb GO CREATE PROCEDURE dbo.abc_prueba AS SELECT 'Ejecuto en tempdb' GO
Y a continuación ejecutaremos un conjunto de instrucciones que nos ayudarán a identificar qué es lo que realmente sucede cuando utilizamos el prefijo "sp_" en los nombres de los procedimientos almacenados.
EXECUTE tempdb.dbo.sp_prueba GO EXECUTE tempdb.dbo.abc_prueba GO
De aquí podemos obtener los siguientes resultados (Ver Figura 4):
Figura 4: Resultados de la cuarta prueba (III).
Lo que nos permite observar que la ejecución del procedimiento almacenado con el prefijo "sp_" en su nombre, arrastro a MS SQL Server 2000 a un mayor procesamiento que el procesamiento que involucra la ejecución de cualquier otro procedimiento almacenado como es en este caso tempdb.dbo.abc_prueba.
5. Conclusión
La ejecución de un procedimiento almacenado con el prejifo "sp_" hace que MS SQL Server 2000 siempre busque primeramente el procedimiento almacenado en el caché de procedimientos almacenados en la base de datos master, lo cual no se refleja con herramientas que permiten la ejecución de T-SQL. La utilización del prefijo "sp_" hace que MS SQL Server 2000 trabaje con los procedimientos almacenados de forma similar al modo que debe trabajar cuando no hay más memoria disponible en el servidor y no puede agregar nuevas entradas al caché, por lo cual debe recompilar contínuamente todos los procedimientos almacenados que se ejecuten.
6. Artículos relacionados
Should I use the sp_ Prefix for procedure Names?
Por Brian Moran - Diciembre de 2001
Procedimientos almacenados que empiezan por sp_ ¿A quién se le ocurre?
Por Miguel Egea - 6 de enero de 2003.
7. Bibliografía
Guerrero, Fernando G. y Rojas, Carlos Eduardo (2001) Programación con Microsoft SQL Server 2000 con ejemplos, Prentice-Hall, Buenos Aires.