This chapter is from the book
This chapter is from the book
This chapter is from the book
Executing Stored Procedures
Although executing a stored procedure can be as easy as listing it on a line by itself in a T-SQL command batch, you should make a habit of prefixing all stored procedure calls with the EXEC keyword, like this:
EXEC dbo.sp_who
Stored procedure calls without EXEC must be the first command in a command batch. Even if this were the case initially, inserting additional lines before the procedure call at some point in the future would break your code.
You should also be sure to owner-qualify procedure calls ("dbo" in the previous example). Omitting the owner from a procedure call causes SQL Server to momentarily place a compile lock on the procedure because it cannot locate it immediately in the procedure cache. This lock is released once the procedure-sans-owner is located in the cache, but can still cause problems in high-throughput environments. Owner-qualifying objects is simply a good habit to get into. It's one of those things you can do to save yourself problems down the road.
INSERT and EXEC
The INSERT command supports calling a stored procedure to supply rows for insertion into a table. Listing 1–11 shows how:
Listing 1–11 You can use INSERT...EXEC to save a stored procedure's output in a table.
CREATE TABLE #locks (spid int, dbid int, objid int, objectname sysname NULL, indid int, type char(4), resource char(15), mode char(10), status char(6)) INSERT #locks (spid, dbid, objid, indid, type, resource, mode, status) EXEC dbo.sp_lock SELECT * FROM #locks DROP TABLE #locks
This is a handy way of trapping the output of a stored procedure in a table so that you can manipulate it or retain it for later use. Prior to the advent of cursor OUTPUT parameters, this was the only way to perform further work on a stored procedure's result set within Transact-SQL.
Note that INSERT...EXEC works with extended procedures that return result sets as well. A simple example is shown in Listing 1–12:
Listing 1–12 INSERT...EXEC works with extended procedures as well.
CREATE TABLE #cmd_result (output varchar(8000)) INSERT #cmd_result EXEC master.dbo.xp_cmdshell 'TYPE C:\BOOT.INI' SELECT * FROM #cmd_result DROP TABLE #cmd_result
Execution Plan Compilation and Execution
When you execute a stored procedure for the first time, it's compiled into an execution plan. This plan is not compiled into machine code or even byte codes, but is pseudo-compiled in order to speed execution. By "pseudo-compiled" I mean that object references are resolved, join strategies and indexing selections are made, and an efficient plan for executing the work that the procedure is to carry out is rendered by the SQL Server query optimizer. The optimizer compares a number of potential plans for performing the procedure's work and selects the one it thinks will cost the least in terms of total execution time. It bases this decision on a number of factors, including the estimated I/O cost associated with each plan, the CPU cost, the memory requirements, and so on.
Once an execution plan has been created, it's stored in the procedure cache for future execution. This cache grows and contracts as necessary to store execution plans for the stored procedures and ad hoc queries executed by the server. SQL Server balances the need to supply adequate memory to the procedure cache with the server's other memory requirements, such as providing adequate resources for the data cache. Obviously, memory taken up by cached execution plans can't be used to cache data, so the server manages this carefully. Caching execution plans in memory saves the optimizer from having to construct a new plan each time a procedure is executed, and can improve performance dramatically.
Monitoring Execution
You can inspect the manner in which SQL Server compiles, stores, and runs execution plans using SQL Server's Profiler utility. To observe what happens when you create and run a procedure, follow these steps:
Start the Query Analyzer utility, connect to your server, and load the stored procedure script from Listing 1–1 (you can find the complete script on the CD accompanying this book).
Start the Profiler utility. You should find it in your Microsoft SQL Server Start|Programs folder.
Click the New Trace button and connect to your server.
On the Events page, remove every event class from the list on the right except the SQL:BatchStarting event class in the TSQL group.
Add every event class in the Stored Procedures group on the left except the SP:StmtStarting and SP:StmtComplete events. (A trace template file that includes these events, BasicTrace.TDF, is on the CD accompanying this book).
Click the Run button at the bottom of the Trace Properties dialog.
Return to Query Analyzer and run the script.
Return to Profiler and click the Stop Selected Trace button. You should see something like the following in the events window:
(Results abridged)
EventClass TextData --------------------- ----------------------------------- SQL:BatchStarting Use Northwind SQL:BatchStarting IF OBJECT_ID('dbo.ListCustomersByCi SQL:BatchStarting CREATE PROCEDURE dbo.ListCustomersB SQL:BatchStarting EXEC dbo.ListCustomersByCity SP:CacheMiss SP:CacheMiss SP:CacheInsert SP:Starting EXEC dbo.ListCustomersByCity SP:Completed EXEC dbo.ListCustomersByCity
The trace output begins with four separate T-SQL command batches. Because the commands are separated by the GO batch terminator, each executes as a separate T-SQL batch. The last batch is the call to the stored procedure via the EXEC command. This call is responsible for the events that follow.
Note the SP:CacheInsert event immediately before the SP:Starting event. In conjunction with the SP:CacheMiss events, this tells us that ListCustomersByCity wasn't in the procedure cache when it was called, so an execution plan was compiled for it and inserted into the cache. The final two events in the trace, the SP:Starting and SP:Completed events, indicate that once the execution plan for the stored procedure was inserted into the cache, it was executed.
To see what happens when a procedure is executed directly from the cache, follow these steps:
Click the Start Selected Trace button to restart the trace.
Return to Query Analyzer, highlight the EXEC line in the query, and run it by itself.
Return to Profiler and stop the trace. You should see something like this:
(Results abridged)
EventClass TextData -------------------- ---------------------------- SQL:BatchStarting EXEC dbo.ListCustomersByCity SP:ExecContextHit SP:Starting EXEC dbo.ListCustomersByCity SP:Completed EXEC dbo.ListCustomersByCity
The ExecContextHit event tells us that an executable version of the stored procedure was found in the cache. Note the absence of the SP:CacheMiss and CacheInsert events. This tells us that the execution plan that was created and inserted into the cache when we ran the stored procedure the first time is reused when we run it a second time.
Execution Plans
When SQL Server runs an execution plan, each step of the plan is processed and dispatched to an appropriate internal manager process (e.g., the T-SQL manager, the DDL and DML managers, the transaction manager, the stored procedure manager, the utility manager, the ODSOLE manager, and so on). SQL Server calls these managers repeatedly until it has processed all the steps in the execution plan.
Execution plans are never stored permanently. The only portion of a stored procedure that is stored on disk is its source code (in syscomments). Because they're cached in memory, cycling the server disposes of all current execution plans (as does the DBCC FREEPROCCACHE() command).
SQL Server automatically recreates a stored procedure's execution plan when
The procedure's execution environment differs significantly from its creation environment (see Environmental Issues discussed later in the chapter for more information)
The sysobjects schema_ver column changes for any of the objects the procedure references. The schema_ver and base_schema_ver columns are updated any time the schema information for a table changes. This includes column additions and deletions, data type changes, constraint additions and deletions, as well as rule and default bindings.
The statistics have changed for any of the objects the procedure references. This means that the auto-update statistics and auto-create statistics events can cause stored procedure recompilation.
An index is dropped that was referenced by the procedure's execution plan
A copy of the procedure's execution plan is not available in the cache. Execution plans are removed from the cache to make room for new plans using a Least Recently Used (LRU) algorithm.
Certain other specialized circumstances occur, such as when a temporary table is modified a fixed number of times, when DDL and DML statements are interleaved, and when the sp_configure system procedure is called (sp_configure calls DBCC FREEPROCCACHE)
During the earlier discussion on creating procedures and SQL Server's limitation regarding having multiple CREATE TABLE statements for a temporary table in a single procedure, I mentioned that the ad hoc code approach (Listing 1–4) forces the procedure's execution plan to be recompiled while it's running. To see this for yourself, restart the trace we've been using and rerun the stored procedure from that query. You should see something like the following in Profiler:
EventClass TextData
--------------------- ----------------------------------------
SQL:BatchStarting exec testp 2
SQL:StmtStarting exec testp 2
SP:ExecContextHit
SP:Starting exec testp 2
SQL:StmtStarting -- testp CREATE TABLE #temp (k1 int identity)
SQL:StmtStarting -- testp IF @var=1
SQL:StmtStarting -- testp ALTER TABLE #temp ADD c1 varchar(2)
SP:Recompile
SP:CacheMiss
SP:CacheMiss
SP:CacheInsert
SQL:StmtStarting -- testp ALTER TABLE #temp ADD c1 varchar(2)
SQL:StmtStarting -- testp INSERT #temp DEFAULT VALUES
SP:Recompile
SP:CacheMiss
SP:CacheMiss
SP:CacheInsert
SQL:StmtStarting -- testp INSERT #temp DEFAULT VALUES
SQL:StmtStarting -- testp EXEC('SELECT c1 FROM #temp')
SQL:StmtStarting -- Dynamic SQL SELECT c1 FROM #temp
SP:Completed exec testp 2
Notice that not one, but two SP:Recompile events occur during the execution of the procedure: one when the ALTER TABLE is encountered (this statement refers to the temporary table created by the procedure, forcing a recompile) and another when the INSERT is encountered (this statement accesses the newly modified temporary table schema, again forcing a recompile). Assuming you've captured the SQL:StmtStarting or SP:StmtStarting event class in the trace, you'll typically see an SP:Recompile event enveloped in two identical StmtStarting events: The first one indicates that the statement began to be executed, but was put on hold so that the recompile could happen; the second indicates that the statement is actually executing now that the recompile has completed. This starting/stopping activity can have a serious impact on the time it takes the procedure to complete. It's worth pointing out again: Creating a temporary table within a procedure that you then process in other ways will cause the procedure's execution plan to be recompiled (one way to avoid temporary tables is to use local table variables instead). Moreover, interleaving DDL and DML within a procedure can also cause the plan to be recompiled. Because it can cause performance and concurrency problems, you want to avoid causing execution plan recompilation when you can.
Another interesting fact that's revealed by the trace is that the execution plan for the dynamic T-SQL string the procedure creates and executes is not cached. Note that there's no CacheMiss, CacheInsert, CacheHit, or ExecContextHit event corresponding to the dynamic SQL query near the end of the trace log. Let's see what happens when we change the EXEC() call to use sp_executesql instead (Listing 1–13):
Listing 1–13 You can use sp_executesql rather than EXEC() to execute dynamic T-SQL.
USE tempdb GO drop proc testp GO CREATE PROC testp @var int AS CREATE TABLE #temp (k1 int identity) IF @var=1 ALTER TABLE #temp ADD c1 int ELSE ALTER TABLE #temp ADD c1 varchar(2) INSERT #temp DEFAULT VALUES EXEC dbo.sp_executesql N'SELECT c1 FROM #temp' GO exec testp 2
When you execute the procedure, you should see trace output like this:
EventClass TextData ------------------ ------------------------------------------------------- SQL:BatchStarting exec testp 2 SQL:StmtStarting exec testp 2 SP:CacheMiss SP:CacheMiss SP:CacheInsert SP:Starting exec testp 2 SQL:StmtStarting -- testp CREATE TABLE #temp (k1 int identity) SQL:StmtStarting -- testp IF @var=1 SQL:StmtStarting -- testp ALTER TABLE #temp ADD c1 varchar(2) SP:Recompile SP:CacheMiss SP:CacheMiss SP:CacheInsert SQL:StmtStarting -- testp ALTER TABLE #temp ADD c1 varchar(2) SQL:StmtStarting -- testp INSERT #temp DEFAULT VALUES SP:Recompile SP:CacheMiss SP:CacheMiss SP:CacheInsert SQL:StmtStarting -- testp INSERT #temp DEFAULT VALUES SQL:StmtStarting -- testp EXEC dbo.sp_executesql N'SELECT c1 FROM #temp' SP:CacheMiss SP:CacheMiss SP:CacheInsert SELECT c1 FROM #temp SQL:StmtStarting SELECT c1 FROM #temp SP:Completed exec testp 2
Note the SP:CacheInsert event that occurs for the dynamic SELECT statement now that we are calling it via sp_executesql. This indicates that the execution plan for the SELECT statement has been inserted into the cache so that it can be reused later. Whether it actually will be reused is another matter, but at least the possibility exists that it can be. If you run the procedure a second time, you'll see that the call to sp_executesql itself generates an ExecContextHit event rather than the CacheMiss event it causes the first time around. By using sp_executesql, we've been able to use the procedure cache to make the procedure run more efficiently. The moral of the story is this: sp_executesql is generally a more efficient (and therefore faster) method of executing dynamic SQL than EXEC().
Forcing Plan Recompilation
You can also force a procedure's execution plan to be recompiled by
Creating the procedure using the WITH RECOMPILE option
Executing the procedure using the WITH RECOMPILE option
Using the sp_recompile system procedure to "touch" any of the tables the procedure references (sp_recompile merely updates sysobjects' schema_ver column)
Once an execution plan is in the cache, subsequent calls to the procedure can reuse the plan without having to rebuild it. This eliminates the query tree construction and plan creation that normally occur when you execute a stored procedure for the first time, and is the chief performance advantage stored procedures have over ad hoc T-SQL batches.
Automatically Loading Execution Plans
A clever way of loading execution plans into the cache at system start-up is to execute them via an autostart procedure. Autostart procedures must reside in the master database, but they can call procedures that reside in other databases, forcing those procedures' plans into memory as well. If you're going to take this approach, creating a single autostart procedure that calls the procedures you want to load into the cache rather than autostarting each procedure individually will conserve execution threads (each autostart routine gets its own thread).
TIP
To prevent autostart procedures from running when SQL Server first loads, start SQL Server with the 4022 trace flag. Adding –T4022 to the SQL Server command line tells the server not to run autostart procedures, but does not change their autostart status. The next time you start the server without the 4022 trace flag, they will again execute.
Executing a Stored Procedure via Remote Procedure Calls (RPC)
As an aside, one thing I should mention here is that the call to a stored procedure need not be a T-SQL batch. The ADO/OLEDB, ODBC, and DB-Library APIs all support executing stored procedures via RPC. Because it bypasses much of the usual statement and parameter processing, calling stored procedures via the RPC interface is more efficient than calling them via T-SQL batches. In particular, the RPC API facilitates the repetitive invocation of a routine with different sets of parameters. You can check this out in Query Analyzer (which uses the ODBC API) by changing the EXEC line in the script to the line in Listing 1–14:
Listing 1–14 Calling ListCustomersByCity via RPC
{CALL dbo.ListCustomersByCity}
This line uses the ODBC "call escape sequence" to invoke the routine using an RPC call. Restart the trace in Profiler, then execute the CALL command in Query Analyzer. You should see something like the following in the Profiler events window:
(Results abridged)
EventClass TextData ------------------- ------------------------------ RPC:Starting exec dbo.ListCustomersByCity SP:ExecContextHit SP:Starting exec dbo.ListCustomersByCity SP:Completed exec dbo.ListCustomersByCity RPC:Completed exec dbo.ListCustomersByCity
Note the absence of the BatchStarting event. Instead, we have an RPC:Starting event followed, ultimately, by an RPC:Completed event. This tells us that the RPC API is being used to invoke the procedure. The procedure cache is unaffected by the switch to the RPC API; we still execute the procedure using the plan in the procedure cache.
Temporary Procedures
You create temporary procedures the same way you create temporary tables—a prefix of a single pound sign (#) creates a local temporary procedure that is visible only to the current connection, whereas a double pound sign prefix (##) creates a global temporary procedure all connections can access.
Temporary procedures are useful when you want to combine the advantages of using stored procedures such as execution plan reuse and improved error handling with the advantages of ad hoc code. Because you can build and execute a temporary stored procedure at run-time, you get the best of both worlds. For the most part, sp_executesql can alleviate the necessity for temporary procedures, but they're still nice to have around when your needs exceed the capabilities of sp_executesql.
System Procedures
System procedures reside in the master database and are prefixed with sp_. You can execute a system procedure from any database. When executed from a database other than the master, a system procedure runs within the context of that database. So, for example, if the procedure references the sysobjects table (which exists in every database) it will access the one in the database that was current when it was executed, not the one in the master database, even though the procedure actually resides in the master. Listing 1–15 is a simple system procedure that lists the names and creation dates of the objects that match a mask:
Listing 1–15 A user-created system procedure that lists objects and their creation dates.
USE master
IF OBJECT_ID('dbo.sp_created') IS NOT NULL
DROP PROC dbo.sp_created
GO
CREATE PROC dbo.sp_created @objname sysname=NULL
/*
Object: sp_created
Description: Lists the creation date(s) for the specified object(s)
Usage: sp_created @objname="Object name or mask you want to display"
Returns: (None)
$Author: Khen $. Email: khen@khen.com
$Revision: 2 $
Example: sp_created @objname="myprocs%"
Created: 1999-08-01. $Modtime: 1/04/01 12:16a $.
*/
AS
IF (@objname IS NULL) or (@objname='/?') GOTO Help
SELECT name, crdate FROM sysobjects
WHERE name like @objname
RETURN 0
Help:
EXEC dbo.sp_usage @objectname='sp_created',
@desc='Lists the creation date(s) for the specified object(s)',
@parameters='@objname="Object name or mask you want to display"',
@example='sp_created @objname="myprocs%"',
@author='Ken Henderson',
@email='khen@khen.com',
@version='1', @revision='0',
@datecreated='19990801', @datelastchanged='19990815'
RETURN -1
GO
USE Northwind
EXEC dbo.sp_created 'Order%'
(Results)
name crdate ------------------------ ------------------------- Order Details 2000-08-06 01:34:08.470 Order Details Extended 2000-08-06 01:34:10.873 Order Subtotals 2000-08-06 01:34:11.093 Orders 2000-08-06 01:34:06.610 Orders Qry 2000-08-06 01:34:09.780
As I've said, any system procedure, whether it's one you've created or one that ships with SQL Server, will use the current database context when executed. Listing 1–16 presents an example that uses one of SQL Server's own system stored procedures. It can be executed from any database to retrieve info on that database:
Listing 1–16 System procedures assume the current database context when executed.
USE Northwind EXEC dbo.sp_spaceused database_name database_size unallocated space -------------------------------- ------------------ ------------------ Northwind 163.63 MB 25.92 MB reserved data index_size unused -------------------- ----------- ------------------ ------------------ 4944 KB 2592 KB 1 808 KB 5 44 KB
Sp_spaceused queries several of SQL Server's system tables to create the report it returns. Because it's a system procedure, it automatically reflects the context of the current database even though it resides in the master database.
Note that, regardless of the current database, you can force a system procedure to run in the context of a given database by qualifying its name with the database name (as though it resided in that database) when you invoke it. Listing 1–17 illustrates:
Listing 1–17 You can force a system procedure to assume a specific database context.
USE pubs EXEC Northwind..sp_spaceused database_name database_size unallocated space ---------------------------- ------------------ ------------------ Northwind 163.63 MB 25.92 MB reserved data index_size unused ------------------ ----------- ------------------ ------------------ 4944 KB 2592 KB 1808 KB 544 KB
In this example, even though sp_spaceused resides in the master and the current database is pubs, sp_spaceused reports space utilization info for the Northwind database because we qualified its name with Northwind when we invoked it. SQL Server correctly locates sp_spaceused in the master and executes it within the context of the Northwind database.
System Objects versus System Procedures
User-created system procedures are listed as user objects rather than system objects in Enterprise Manager. Why? Because the system bit of a procedure's status column in sysobjects (0xC0000000) isn't set by default. You can call the undocumented procedure sp_MS_marksystemobject to set this bit. The lone parameter taken by the procedure is the name of the object with the system bit you wish to set. Many undocumented functions and DBCC command verbs do not work properly unless called from a system object (See Chapter 22 for more information). Check the IsMSShipped property of the OBJECTPROPERTY() function to determine whether an object's system bit has been set. Listing 1–18 is a code fragment that demonstrates this function:
Listing 1–18 System procedures and system objects are two different things.
USE master
GO
IF OBJECT_ID('dbo.sp_test') IS NOT NULL
DROP PROC dbo.sp_test
GO
CREATE PROC dbo.sp_test AS
select 1
GO
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.sp_test'),'IsMSShipped') AS 'System
Object?', status, status & 0xC0000000
FROM sysobjects WHERE NAME = 'sp_test'
GO
EXEC sp_MS_marksystemobject 'sp_test'
GO
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.sp_test'),'IsMSShipped') AS 'System
Object?', status, status & 0xC0000000
FROM sysobjects WHERE NAME = 'sp_test'
(Results)
System Object? status -------------- ----------- ----------- 0 1610612737 1073741824 (1 row(s) affected) System Object? status -------------- ----------- ----------- 1 -536870911 -1073741824 (1 row(s) affected)
As I've said, there are a variety of useful features that do not work correctly outside system procedures. For example, a stored procedure can't manipulate full text indexes via DBCC CALLFULLTEXT() unless its system bit is set. Regardless of whether you actually end up using this functionality, it's instructive to at least know how it works.