Uso del prefijo "sp_" en el nombre de procedimientos almacenados

 

 

Autor: Damián Herrera

Fecha publicación: 24 de junio de 2005

 

Introducción. 1

Pruebas iniciales. 1

Creando procedimientos almacenados del sistema.. 3

Desmitificando el uso del prefijo “sp_” 5

Conclusión. 8

Artículos relacionados. 9

Bibliografía.. 9

 

Introducción

Este documento 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 otros 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 éste 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 éste prejifo no es recomendable y decidí escribir el presente 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 como 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.

Pruebas iniciales

La prueba inicial que se suele hacer para empezar a indagar en éste 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

Código: Primera prueba

 

Creados los procedimientos almacenados con los cuales realizaremos la primera prueba procederemos a ejecutarlos con distintas instrucciones de invocación para observar que resultados se obtienen.

 

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

Código: Ejecución de la primer prueba

 

 

Los resultados que a continuación deben aparecer quedan expresados en las siguientes tablas.

 

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’

Código: Resultados de la primer prueba

 

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 realizo 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.

 

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

Código: Ejecución de la segunda prueba

 

Los resultados que a continuación deben aparecer quedan expresados en las siguientes tablas.

 

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’

Código: Resultados de la segunda prueba

 

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 busco el procedimiento almacenado en la base de datos master.

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

Código: Limpieza de procedimientos almacenados

 

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

Código: Tercer prueba

 

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

Código: Ejecución de la tercer prueba

 

Los resultados que a continuación deben aparecer quedan expresados en las siguientes tablas.

 

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’

Código: Resultados de la tercer prueba

 

Si observamos los resultados de esta tercera prueba llegamos a la conclusión de que nunca se ejecuto 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 esta ocupando un espacio innecesario debido a que la ejecución del mismo nunca podrá llevarse a cabo.

 

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 aun 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) .

 

Pantalla: 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 aun 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

Código: Cuarta prueba

 

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

Código: Ejecución de la cuarta prueba (I)

 

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.

 

Código: Resultados de la cuarta prueba (I)

 

Ahora procederemos a ejecutar el siguiente conjunto de instrucciones:

 

USE tempdb GO   EXECUTE tempdb.dbo.sp_prueba GO

Código: Ejecución de la cuarta prueba (II)

 

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 busco un procedimiento almacenado en su cache y no lo encontró, con lo cual MS SQL Server 2000 tuvo que recompilar el mismo.

 

Código: 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 cache 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 que 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

Código: Ejecución de la cuarta prueba (III)

 

Y a continuación ejecutaremos un conjunto de instrucciones que nos ayudaran a  identificar que 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 ver los siguientes resultados:

 

 

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 éste caso tempdb.dbo.abc_prueba.

 

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 cache 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 cache, por lo cual debe recompilar continuamente todos los procedimientos almacenados que se ejecuten.

Artículos relacionados

“Should I use the sp_ Prefix for procedure Names?”

Por Brian Moran – Diciembre de 2001

URL: http://www.windowsitpro.com/SQLServer/Article/ArticleID/23011/23011.html

 

“Procedimientos almacenados que empiezan por sp_ ¿A quien se le ocurre?”

Por Miguel Egea – 6 de enero de 2003.

URL: http://www.configuracionesintegrales.com/miguele/sp_.asp?articulo=245

 

Bibliografía

 

Guerrero, Fernando G. y Rojas, Carlos Eduardo (2001) Programación con Microsoft SQL Server 2000 con ejemplos, Prentice-Hall, Buenos Aires.