Executing Batches or What s Stored About a Stored Procedure?
Typically, when a batch of Transact -SQL commands is received from a client connection, the following high-level actions are performed:
Step One: Parse Commands and Create the Sequence Tree
The command parser checks for proper syntax and translates the Transact-SQL commands into an internal format that can be operated on. The internal format is known as a sequence tree or query tree . The command parser handles these language events.
Step Two: Compile the Batch
An execution plan is generated from the sequence tree. The entire batch is compiled, queries are optimized, and security is checked. The execution plan contains the necessary steps to check any constraints that exist. If a trigger exists, the call to that procedure is appended to the execution plan. (Recall that a trigger is really a specialized type of stored procedure. Its plan is cached, and the trigger doesn't need to be recompiled every time data is modified.)
The execution plan includes the following:
- The complete set of necessary steps to carry out the commands in the batch or stored procedure.
- The steps needed to enforce constraints. (For example, for a foreign key, this would involve checking values in another table.)
- A branch to the stored procedure plan for a trigger, if one exists.
Step Three: Execute
During execution, each step of the execution plan is dispatched serially to a manager that's responsible for carrying out that type of command. For example, a data definition command (in DDL), such as CREATE TABLE, is dispatched to the DDL manager. DML statements, such as SELECT, UPDATE, INSERT, and DELETE, go to the DML manager. Miscellaneous commands, such as DBCC and WAITFOR, go to the utility manager. Calls to stored procedures (for example, EXEC sp_who ) are dispatched to the stored procedure manager. A statement with an explicit BEGIN TRAN interacts directly with the transaction manager.
Contrary to what you might think, stored procedures don't permanently store the execution plan of the procedure. (This is a feature ”the execution plan is relatively dynamic.) Think for a moment about why it's important for the execution plan to be dynamic. As new indexes are added, preexisting indexes are dropped, constraints are added or changed, and triggers are added or changed; or as the amount of data changes, the plan can easily become obsolete.
So, what's stored about a stored procedure?
The SQL statements that were used to create the procedure are stored in the system table syscomments . The first time a stored procedure is executed after SQL Server was last restarted, the SQL text is retrieved and an execution plan is compiled. The execution plan is then cached in SQL Server's memory, and it remains there for possible reuse until it's forced out in a least recently used (LRU) manner.
Hence, a subsequent execution of the stored procedure can skip not only Step 1, parsing, but also Step 2, compiling, and go directly to Step 3, execution . Steps 1 and 2 always add some overhead and can sometimes be as costly as actually executing the commands. Obviously, if you can eliminate the first two steps in a three-step process, you've done well. That's what stored procedures let you do.
When you execute a stored procedure, if a valid execution plan exists in the procedure cache; it will be used (eliminating the parsing and compiling steps). When the server is restarted, no execution plans will be in the cache; so the first time the server is restarted, a stored procedure will be compiled.
TIP
You can preload your procedure cache with execution plans for stored procedures by defining a startup stored procedure that executes the procedures you want to have compiled and cached.
After a procedure executes, its plan remains in the cache and is reused the next time any connection executes the same procedure. In addition, because multiple connections might try to execute the same plan concurrently, a part of the plan (called the shareable portion) is reentrant. Each concurrent user of that plan also requires an execution context. If you think of this in traditional programming terms, this execution context is needed to contain the dirty data.
The execution context tends to be fairly small compared to the plans. The execution contexts are themselves serially reusable for certain plans that don't contain special operators. Figures 10-1 and 10-2 below show execution with and without a stored procedure.
Figure 10-1. Efficient execution with a stored procedure.
Figure 10-2. Less efficient execution without a stored procedure.
Step Four: Recompile Execution Plans
By now it should be clear that the SQL code for stored procedures persists in the database, but execution plans don't. Execution plans are cached in memory. But sometimes they can be invalidated and a new plan generated.
So, when is a new execution plan compiled?
The short answer to this question is, whenever SQL Server needs to! This can include the following cases:
- When a copy of the execution plan isn't available in memory.
- When an index on a referenced table is dropped.
- When updated statistics are available for any table used by the procedure. (Chapter 14 covers statistics in detail.)
- When a table referenced in the procedure is altered using ALTER TABLE.
- When the table has been specifically identified, using sp_recompile , to force recompilation of any stored procedures referencing it. The system procedure sp_recompile increments the schema column of sysobjects for a given table. This invalidates any plans that reference the table, as described in the previous examples. You don't specify a procedure to be recompiled; instead, you simply supply the name of a table to sp_recompile , and all execution plans or procedures referencing the table will be invalidated.
- When the stored procedure was created using the WITH RECOMPILE option. A stored procedure can be created using WITH RECOMPILE to ensure that its execution plan will be recompiled for every call and will never be reused. Using WITH RECOMPILE can be useful if procedures take parameters and the values of the parameters differ widely, resulting in a need for different execution plans to be formulated. For example, if a procedure is passed a value to be matched in the WHERE clause of a query, the best way to carry out that query can depend on the value passed. SQL Server maintains statistics for each index as a histogram to help it decide whether the index is selective enough to be useful.
For one given value, an index might be highly selective, and the distribution statistics might indicate that only 5 percent of the rows have that value. SQL Server might decide to use that index because it would exclude many pages of data from having to be visited. Using the index would be a good strategy, and the cached plan for the procedure might include the instructions to use the index. However, using another value, the index might not be so selective. Rather than use only the index to visit most of the data pages (ultimately doing more I/O because you're reading both the index and the data), you'd be better off simply scanning the data and not reading the index.
For example, suppose we have a nonclustered index on the color column of our automobile table. Forty percent of the cars are blue, 40 percent are red, and 5 percent each are yellow, orange, green, and purple. It's likely that a query based on color should scan the table if the color being searched on is blue or red; but it should use the index for the other colors. Without using the WITH RECOMPILE option, the execution plan created and saved would be based on the color value the first time the procedure was executed.
So if we passed yellow to the procedure the first time it executed, we'd get a plan that used an index. Subsequently, if we passed blue , we might be able to use the previous plan that was created for yellow . In this case, however, we'd be traversing a nonclustered index to access a large percentage of rows in the table. This could end up being far more expensive than simply scanning the entire table. In such a case, when there's a lot of variance in the distribution of data and execution plans are based on the parameters passed, it makes sense to use the WITH RECOMPILE option.
This example also shows that two execution plans for the same procedure can be different. Suppose we're not using WITH RECOMPILE, and we execute the procedure for both blue and green simultaneously from two different connections. Assume for a moment that no plan is cached. (Each will generate a new plan, but one plan will use the index and the other won't.) When a subse-quent request for red arrives, the plan it would use is a matter of chance. And if two simultaneous calls come in for red and each plan is available, the two equivalent requests will execute differently because they'll use different plans. If, as you're processing queries, you see significant deviations in the execution times of apparently identical procedures, think back to this example.
- When the stored procedure is executed using the WITH RECOMPILE option. This case is similar to the preceding one, except that here the procedure isn't created with the option, but rather the option is specified when the procedure is called. The WITH RECOMPILE option can always be added upon execution, forcing a new execution plan to be generated. The new plan is then available for subsequent executions (not using WITH RECOMPILE).
Storage of Stored Procedures
With each new stored procedure, a row is created in the sysobjects table, as occurs for all database objects. The text of a stored procedure (including comments) is stored in syscomments which is typically useful. This allows procedures like sp_helptext to display the source code of a stored procedure so that you can understand what's going on, and it allows stored procedure editors and debuggers to exist.
For most users and developers, the fact that the full text of a procedure is stored in clear text is definitely a feature of SQL Server. Prior to SQL Server 7, a real limit existed on the size of the text for a stored procedure because of the amount of text that syscomments could store for a given stored procedure.
In the syscomments table prior to SQL Server 7, the text column was defined as varchar(255) . Any given procedure could have many rows in syscomments , with comment chunks in lengths of up to 255 characters , and those chunks were sequenced by the colid field. But colid was rather shortsightedly defined as a tinyint , to ostensibly save a byte. Because the maximum value a tinyint could take is 255, up to 255 chunks of text could be included, each of which could be up to 255 bytes in size. Hence, the maximum size of the text used to create a stored procedure was 255 — 255, or 65,025 bytes ( roughly 64 KB).
SQL Server 7 still has a limit, but it's not likely to be one you'll run up against soon. The syscomments.text field can now hold up to 8000 bytes, and colid is a smallint . Any one procedure should then be able hold 8000 — 32,768 bytes, or 250 MB. There's one more limiting factor, however, and that's the maximum size of a batch. To create the procedure, the client must send the complete definition to SQL Server in the CREATE PROCEDURE statement, and this must be sent as a single batch. SQL Server has a limit on batch size, which is 65,536 times the network packet size. Because the default value for network packet size is 4096, that gives us a batch size of 256 MB. So really, the limit of the size of your procedure text isn't worth worrying about right away.
Encrypting Stored Procedures
With the rollout of version 6.0, SQL Server developers learned somewhat painfully that some users didn't appreciate that the text of stored procedures was available in the syscomments system table. Several ISVs had already built integrated solutions or tools that created stored procedures to use with earlier versions of SQL Server. In most cases, these solutions were sophisticated applications, and the ISVs viewed the source code as their proprietary intellectual property. They had correctly noticed that the text of the procedure in syscomments didn't seem to do anything. If they set the text field to NULL, the procedure still ran fine ”so that's what these ISVs did. This way, they wouldn't be publishing their procedure source code with their applications.
Unfortunately, when it came time to upgrade a database to version 6.0, this approach exposed a significant problem for their applications. The internal data structures for the sequence plans had changed between versions 4.2 and 6.0. ISVs had to re-create procedures, triggers, and views to generate the new structure. Although SQL Server's Setup program was designed to do this automatically, it accomplished the tasks by simply extracting the text of the procedure from syscomments and then dropping and re-creating the procedure using the extracted text. It's no surprise that this automatic approach failed for procedures in which the creator had deleted the text.
When the developers learned of this problem after the release of the beta version of SQL Server 6.0, they immediately understood why developers had felt compelled to delete the text. Nonetheless, they couldn't undo the work that had already been done. Developers with these ISVs had to dig out their original stored procedure creation scripts and manually drop and re-create all their procedures. While perhaps possible, it wasn't practical to create a converter program that would operate purely on the internal data structures used to represent procedures and views. Attempting this would have been like developing a utility to run against an executable program and have it backward-engineer the precise source code (more than a disassembly) that was used to create the binary. The SQL source code compilation process is designed to be a descriptive process that produces the executable, not an equation that can be solved for either side.
Following the version 6.0 beta release, but before the final release of version 6.0, the developers added the ability to encrypt the text stored in syscomments for stored procedures, triggers, and views. This allowed programmers to protect their source code without making it impossible for the upgrade process to re-create stored procedures, triggers, and views in the future. You can now protect your source code by simply adding the modifier WITH ENCRYPTION to CREATE PROCEDURE. No decrypt function is exposed (which would defeat the purpose of hiding the textlike source code). Internally, SQL Server can read this encrypted text and upgrade the sequence trees when necessary. Because the text isn't used at runtime, no performance penalty is associated with executing procedures created using WITH ENCRYPTION.
NOTE
You give up some capabilities when you use WITH ENCRYPTION. For example, you can no longer use the sp_helptext stored procedure or object editors that display and edit the text of the stored procedure, and you can't use a source-level debugger for Transact-SQL, like the one available in the Enterprise edition of Microsoft Visual Basic or Microsoft Visual C++. Unless you are concerned about someone seeing your procedures, you shouldn't use the WITH ENCRYPTION option.
If you create a procedure, trigger, or view using WITH ENCRYPTION, the texttype column of syscomments will have its third bit set. (The texttype value will be OR'ed with decimal number 4.) For now, this simply means that the decimal value of texttype would be 6 ”the only other bit to be set would be the second one (decimal 2), indicating that the text in that procedure resulted from a CREATE statement and not a user-supplied comment. If you want to programmatically determine whether a procedure is encrypted, it's safer to check the value of the third bit by AND'ing it with 4 than it is to look for the value of 6. New bits could get added in the future, and the value of 6 might no longer be accurate.
To illustrate their effects on syscomments , we created two procedures ”one encrypted and one not ”in the following example:
CREATE PROCEDURE cleartext AS SELECT * FROM authors GO CREATE PROCEDURE hidetext WITH ENCRYPTION AS SELECT * FROM authors GO SELECT sysobjects.name, sysobjects.id, number, colid, texttype, language, text FROM syscomments, sysobjects WHERE sysobjects.id=syscomments.id AND (sysobjects.id=OBJECT_ID('hidetext') OR sysobjects.id=OBJECT_ID('cleartext')) |
Here's the output:
name id number colid texttype language text ---------- ----------- ------ ------ -------- -------- ----------------------- cleartext 110623437 1 1 2 0 CREATE PROCEDURE cleartext AS SELECT * FROM authors hidetext 126623494 1 1 6 0 ??????????????????????? ????????????????????? ????????????????????? ??????????????? |
To find created objects that have encrypted text, you can use a simple query:
-- Find the names and types of objects that have encrypted text SELECT name, texttype FROM syscomments, sysobjects WHERE sysobjects.id=syscomments.id AND texttype & 4 > 0 |
Here's the output:
name texttype -------- -------- hidetext 6 |
If you try to run sp_helptext against an encrypted procedure, it will return a message stating that the text is encrypted and can't be displayed:
EXEC sp_helptext 'hidetext' The object's comments have been encrypted. |
Altering a Stored Procedure
SQL Server 7 allows you to alter the definition of a stored procedure. The syntax is almost identical to the syntax for creating the procedure initially, except that the keyword CREATE is replaced by the keyword ALTER.
The big difference is that the procedure_name used with ALTER PROCEDURE must already exist, and the definition specified replaces whatever definition the procedure had before. Just like when using the ALTER VIEW command, the benefit of ALTER PROCEDURE comes from the fact that the procedure's object_id won't change, so all the internal references to this procedure will stay intact. If other procedures reference this one, they'll be unaffected. If you've assigned permissions on this procedure to various users and roles, dropping the procedure removes all permission information. Altering the procedure keeps the permissions intact.
Temporary Stored Procedures
Temporary stored procedures allow an execution plan to be cached, but the object's existence and the text of the procedure are stored in the temporary database ( tempdb ) system tables ”in sysobjects and syscomments . Recall that tempdb is re-created every time SQL Server is restarted, so these objects don't exist after SQL Server is shut down. During a given SQL Server session, you can reuse the procedure without permanently storing it. If you are familiar with the PREPARE/EXECUTE model used by several other products, especially with the Embedded SQL programming paradigm, you know that temporary procedures use a similar model.
Typically, you use a temporary stored procedure when you want to regularly execute the same task several times in a session, although you might use different parameter values, and you don't want to permanently store the task. You could conceivably use a permanent stored procedure and drop it when you're finished, but you'd inevitably run into cleanup issues if a stored procedure were still hanging around and the client application terminated without dropping the procedure. Because temporary stored procedures are deleted automatically when SQL Server is shut down (and tempdb is created anew at startup), cleanup isn't an issue.
Just as SQL Server has three types of temporary tables, it also has three types of temporary stored procedures: private , global , and those created from direct use of tempdb.
Private Temporary Stored Procedures
By adding a single pound sign (#) to the beginning of the stored procedure name (for example, CREATE PROC #get_author AS ...), you can create the procedure from within any database as a private temporary stored procedure. Only the connection that created the procedure can execute it, and you can't grant privileges on it to another connection. The procedure exists for the life of the creating connection only; that connection can explicitly use DROP PROCEDURE on it to clean up sooner. Because the scoping of a private temporary table is specific only to the connection that created it, you won't encounter a name collision should you choose a procedure name that's used by another connection. As with temporary tables, you use your private version, and what occurs in other connections is irrelevant.
Global Temporary Stored Procedures
By prefixing two pound signs (##) to the stored procedure name (for example, CREATE PROC ##get_author AS ...), you can create the procedure from within any database as a global temporary stored procedure. Any connection can subsequently execute that procedure without EXECUTE permission being specifically granted. Unlike private temporary stored procedures, only one copy of a global temporary stored procedure exists for all connections. If another connection created a procedure with the same name, the two names will collide and the create procedure statement will fail. Permission to execute global temporary procedures defaults to public and can't be changed. You can issue a command to deny other users permission, but it will have no effect. Any user, on any connection, can execute a global temporary stored procedure.
A global temporary stored procedure exists until the creating connection terminates and all current execution of the procedure completes. Once the creating connection terminates, however, no further execution is allowed. Only those connections that have already started executing are allowed to finish.
Procedures Created from Direct Use of tempdb
Realizing that tempdb is re-created every time SQL Server is started, you can create a procedure in tempdb that fully qualifies objects in other databases. Procedures created in tempdb in this way can exist even after the creating connection is terminated, and the creator can specifically grant and deny execute permissions to specific users. To do this, the creator of the procedure must have create procedure privileges in tempdb . Privileges in tempdb can be set up in one of two ways: you can set your privileges in model (the template database) so that they will be copied to tempdb when it is created at system restart, or you can set up an autostart procedure to set the tempdb privileges every time SQL Server is started. Here's an example of creating a procedure in tempdb and then executing it from the pubs database:
USE tempdb GO CREATE PROC testit AS SELECT * FROM pubs.dbo.authors GO Executing the procedure created above from the pubs database USE pubs EXEC tempdb..testit |
While we're on the subject of temporary objects, keep in mind that a private temporary table created within a stored procedure isn't visible to the connection after the creating procedure completes. It's possible, however, to create a local temporary table before executing a stored procedure and make the table visible to the stored procedure. The scoping of the temporary table extends to the current statement block and all subordinate levels.
NOTE
You can use the @@nestlevel system function to check for the visibility of temporary tables. A temporary table created at nest level 0 will be visible to all further levels on that connection. A table created within a procedure at nest level 1, for example, won't be visible when execution returns to the calling block at nest level 0. A global temporary table, or a table directly created in tempdb without using either # or ##, will be visible no matter what the nesting level.
System Stored Procedures and the Special sp_ Prefix
SQL Server installs a large number of system stored procedures that are used mostly for administrative and informational purposes. In many cases, these are called behind the scenes by the SQL-DMO objects used by SQL Server Enterprise Manager and other applications. But the system stored procedures can also be called directly; only a few years ago, doing so was the primary mechanism by which SQL Server was administered. Old-time SQL Server users were indoctrinated into using system stored procedures.
With the great tools and interfaces that are a core part of SQL Server today, there's not much reason to work with these system stored procedures directly anymore. But it's good to be familiar with them ”understanding them can help you understand the operations that occur on the system tables and can take much of the mystery out of what's going on behind the scenes with the graphical tools.
All the system stored procedure names begin with sp_ , and most exist in the master database. This is more than just a convention. A procedure created in the master database that begins with sp_ is uniquely able to be called from any other database without the necessity of fully referencing the procedure with the database name. This can be useful for procedures you create as well. The sp_ magic works even for extended stored procedures, which are user-written calls to DLLs. By convention, extended stored procedure names begin with xp_ , but the sp_ prefix and its special property can be applied to them as well (but only when added to the master database). In fact, some extended procedures that are supplied as part of the product, such as those used to create Automation objects (for example, sp_OACreate ), use the sp_ prefix so that they can be called from anywhere , although they're actually functions in a DLL, not a Transact-SQL stored procedure.
The sp_ prefix actually makes a procedure special in two ways. First, as already mentioned, a procedure whose name starts with sp_ can be directly called from any database, without fully qualifying the name. Second, system tables referenced in your special procedures will always refer to the tables in the database from which the procedure was called. For example, the sp_help stored procedure lists all the objects from the sysobjects system table. But every database has its own sysobjects table, so which one is used? If you execute sp_help from the pubs database, you get a list of the objects in pubs ; if you call sp_help from msdb , you get a list of objects in msdb , and so on.
Of course, if the sp_ procedure references a table that exists only in the master database, such as sysconfigures , the table in master will be referenced no matter where you are when you call the procedure. This trick works only for system tables, however. If you create a user table called MasterLookup and reference it in an sp_ stored procedure, SQL Server will look in only the master database to try to find the MasterLookup table. If it doesn't exist in master , you'll get an error message when executing your procedure.
WARNING
Microsoft strongly discourages any direct references to the system tables and suggests instead that you use only the presupplied stored procedures and the object property functions to get any system information you need. It's not guaranteed that the structures of the system tables will remain the same from one release to the next, or even between service packs . In fact, the Upgrade Wizard for converting SQL Server 6 to SQL Server 7 doesn't even attempt to upgrade any procedures that modify system tables.
If you look carefully through the SQL Server system tables, you'll find procedures beginning with sp _ that are not among the documented system stored procedures. Typically, these procedures exist to be called by some other system stored procedure that is exposed; to support some SQL Server utility, such as SQL Server Enterprise Manager; or to provide statistics to the Microsoft Windows NT Performance Monitor. These procedures aren't documented for direct use because they exist only to support functionality exposed elsewhere ”they don't provide that functionality independently.
There's nothing secret about these procedures, and their text is exposed clearly in syscomments . You're welcome to explore them to see what they do and use them if you want. But unlike the documented stored procedures, maintaining system stored procedures or striving to make them exhibit exactly consistent behavior isn't a commitment in future releases. Of course, if your applications were to become dependent on one of these procedures, you could certainly maintain your own version of it to perform exactly as you specify, or you could use one of them as a starting point and customize it to suit your needs (under a different name).
The SQL Server online documentation explains the specifics of each system stored procedure, so we don't need to cover that here. We'll just categorize and enumerate most of them to give you a general understanding of the types and number of procedures that exist. The name of the procedure usually reveals its purpose. But first, we'll look at how to autostart stored procedures.
Autostart Stored Procedures
Version 6 introduced the handy ability to mark a stored procedure as autostart . Autostart stored procedures are useful if you regularly want to perform housekeeping functions or if you have a background daemon procedure that's always expected to be running. Another handy use for an autostart procedure is to have it assign some privileges in tempdb . Or the procedure can create a global temporary table and then sleep indefinitely using WAITFOR. This will ensure that such a temporary table will always exist, because the calling process is the first thing executed, and it never terminates.
You can easily make a stored procedure start automatically ”you use the system stored procedure sp_procoption . This procedure allows you to turn options for stored procedures on or off. In version 7 of SQL Server, the only available option is called startup . The syntax of the command to enable a procedure to automatically execute on SQL Server startup is as follows :
sp_procoption procedure_name , startup, true |
You can remove the startup option by executing the same procedure and changing the value to FALSE. A procedure that's autostarted runs in the con-text of a system administrator account. (The procedure can use SETUSER to impersonate another account.) A procedure with the startup option set to TRUE must be in the master database and be owned by the database owner (dbo) in master . You can, of course, reference objects in other databases from within the startup procedure, or even call procedures that exist in other databases. A startup procedure is launched asynchronously, and it can execute in a loop for the entire duration of the SQL Server process. This allows several such procedures to be launched simultaneously at startup. When a startup procedure is running, it's seen as an active user connection.
A single startup procedure can nest calls to other stored procedures, consuming only a single user connection. Such execution of the nested procedures is synchronous, as would normally be the case. (That is, execution in the calling procedure doesn't continue until the procedure being called completes.) Typically, a stored procedure that's autostarted won't generate a lot of output. Errors, including those raised with RAISERROR, will be written to the SQL Server error log, and any result sets generated will seemingly vanish . If you need the stored procedure to return result sets, you should use a stored procedure that calls the main stored procedure with INSERT/EXEC to insert the results into a table.
If you want to prevent a procedure with the startup option from executing, you can start the server using trace flag 4022 or as a minimally configured server using the -f switch to sqlservr .exe. (Add - T4022 or -f as a parameter to SQL Server using the Services dialog box in the Windows NT Control Panel. You can also set the -T4022 flag by using the SQL Server Properties dialog box in Enterprise Manager. You'll need to right-click the name of your server and choose Properties to get to this dialog box.) These safeguards allow you to recover from problems. (Consider the illustrative but perhaps absurd example of someone including a procedure that executes the SHUTDOWN command. If you had given such a procedure the autostart option, SQL Server would have immediately shut itself down before you could do anything about it!)
The following sections discuss the broad categories for grouping stored procedures: System, Catalog, SQL Server Agent, Replication, and Extended.
System Stored Procedures
System stored procedures aid in the administration of your system, and they sometimes modify the system tables. You shouldn't configure the system to allow direct modification of the system tables because a mistake can render your database useless. That's why direct modification of system tables is prohibited by default. If modification is necessary, a system stored procedure that is known to do the job correctly is provided.
Listed here are the SQL Server system stored procedures, which can be further divided into four categories: general, security, cursor, and distributed query procedures. Each procedure's name gives you a clue about its function. The general system procedures follow:
| sp_addextendedproc | sp_droptype | sp_helpserver |
| sp_addmessage | sp_executesql | sp_helpsort |
| sp_addtype | sp_fulltext_catalog | sp_helptext |
| sp_addumpdevice | sp_fulltext_column | sp_helptrigger |
| sp_altermessage | sp_fulltext_database | sp_indexoption |
| sp_attach_db | sp_fulltext_service | sp_lock |
| sp_attach_single_file_db | sp_fulltext_table | sp_monitor |
| sp_autostats | sp_getbindtoken | sp_processmail |
| sp_bindefault | sp_help | sp_procoption |
| sp_bindrule | sp_helpconstraint | sp_recompile |
| sp_bindsession | sp_helpdb | sp_refreshview |
| sp_certify_removable | sp_helpdevice | sp_rename |
| sp_configure | sp_helpextendedproc | sp_renamedb |
| sp_create_removable | sp_helpfile | sp_serveroption |
| sp_createstats | sp_helpfilegroup | sp_setnetname |
| sp_datatype_info | sp_help_fulltext_catalogs | sp_spaceused |
| sp_dbcmptlevel | sp_help_fulltext_catalogs_cursor | sp_tableoption |
| sp_dboption | sp_help_fulltext_columns | sp_unbindefault |
| sp_depends | sp_help_fulltext_columns_cursor | sp_unbindrule |
| sp_detach_db | sp_help_fulltext_tables | sp_updatestats |
| sp_dropdevice | sp_help_fulltext_tables_cursor | sp_validname |
| sp_dropextendedproc | sp_helpindex | sp_who |
| sp_dropmessage | sp_helplanguage |
And here are the security stored procedures:
| sp_addalias | sp_defaultdb | sp_helpgroup |
| sp_addapprole | sp_defaultlanguage | sp_helplogins |
| sp_addgroup | sp_denylogin | sp_helpntgroup |
| sp_addlinkedsrvlogin | sp_dropalias | sp_helpremotelogin |
| sp_addlogin | sp_dropapprole | sp_helprole |
| sp_addremotelogin | sp_dropgroup | sp_helprolemember |
| sp_addrole | sp_droplinkedsrvlogin | sp_helprotect |
| sp_addrolemember | sp_droplogin | sp_helpsrvrole |
| sp_addserver | sp_dropremotelogin | sp_helpsrvrolemember |
| sp_addsrvrolemember | sp_droprole | sp_helpuser |
| sp_adduser | sp_droprolemember | sp_password |
| sp_approlepassword | sp_dropserver | sp_remoteoption |
| sp_change_users_login | sp_dropsrvrolemember | sp_revokedbaccess |
| sp_changedbowner | sp_dropuser | sp_revokelogin |
| sp_changegroup | sp_grantdbaccess | sp_setapprole |
| sp_changeobjectowner | sp_grantlogin | sp_srvrolepermission |
| sp_dbfixedrolepermission | sp_helpdbfixedrole | sp_validatelogins |
SQL Server 7 has a set of procedures available for finding information about existing cursors (called cursor procedures ). Here's the list:
| sp_cursor_list |
| sp_describe_cursor_columns |
| sp_describe_cursor_tables |
SQL Server supports distributed queries, which access data from multiple heterogeneous data sources. You can store this data in either the same or different computers, and you can access it by using OLE DB. Here are the stored procedures available for managing distributed queries:
| sp_addlinkedserver | sp_columns_ex | sp_primarykeys |
| sp_addlinkedsrvlogin | sp_droplinkedsrvlogin | sp_serveroption |
| sp_catalogs | sp_indexes | sp_table_privileges_ex |
| sp_column_privileges_ex | sp_linkedservers | sp_tables_ex sp_foreignkeys |
Catalog Stored Procedures
Applications and development tools commonly need access to information about table names, column types, datatypes, constraints, privileges, and configuration options. All this information is stored in the system tables (system catalogs). But system tables might require changes between releases to support new features, so directly accessing the system tables could result in your application breaking from a new SQL Server release.
For this reason, SQL Server provides catalog stored procedures, a series of stored procedures that extract the information from the system tables, providing an abstraction layer that insulates your application. If the system tables are changed, the stored procedures that extract and provide the information will also be changed to ensure that they operate consistently (from an external perspective) from one release to another. Many of these procedures also map nearly identically to ODBC calls. The SQL Server ODBC driver calls these procedures in response to those function calls. While it's fine to directly query the system catalogs for ad hoc use, if you're deploying an application that needs to get information from the system tables, use these catalog stored procedures:
| sp_column_privileges | sp_pkeys | sp_statistics |
| sp_columns | sp_server_info | sp_stored_procedures |
| sp_databases | sp_special_columns | sp_table_privileges |
| sp_fkeys | sp_sproc_columns | sp_tables |
SQL Server Agent Stored Procedures
SQL Enterprise Manager uses SQL Server Agent stored procedures to set up alerts and schedule tasks for execution. If your application needs to carry out tasks like these, you can call the following procedures directly. They must be called from or qualified by the msdb database. SQL Server Agent stored procedures appear below:
| sp_add_alert | sp_help_jobhistory |
| sp_add_category | sp_help_jobschedule |
| sp_add_job | sp_help_jobserver |
| sp_add_jobschedule | sp_help_jobstep |
| sp_add_jobserver | sp_help_notification |
| sp_add_jobstep | sp_help_operator |
| sp_add_notification | sp_help_targetserver |
| sp_add_operator | sp_help_targetservergroup |
| sp_add_targetservergroup | sp_helptask |
| sp_add_targetsvrgrp_member | sp_manage_jobs_by_login |
| sp_addtask | sp_msx_defect |
| sp_apply_job_to_targets | sp_msx_enlist |
| sp_delete_alert | sp_post_msx_operation |
| sp_delete_category | sp_purge_jobhistory |
| sp_delete_job | sp_purgehistory |
| sp_delete_jobschedule | sp_reassigntask |
| sp_delete_jobserver | sp_remove_job_from_targets |
| sp_delete_jobstep | sp_resync_targetserver |
| sp_delete_notification | sp_start_job |
| sp_delete_operator | sp_stop_job |
| sp_delete_targetserver | sp_update_alert |
| sp_delete_targetservergroup | sp_update_category |
| sp_delete_targetsvrgrp_member | sp_update_job |
| sp_droptask | sp_update_jobschedule |
| sp_help_alert | sp_update_jobstep |
| sp_help_category | sp_update_notification |
| sp_help_downloadlist | sp_update_operator |
| sp_helphistory | sp_update_targetservergroup |
| sp_help_job | sp_updatetask |
Web Assistant Procedures
SQL Server provides a number of procedures for creating and managing Web pages. These procedures are typically called from within the Web Assistant Wizard, but they're documented and you can call them directly. You can consider them a subcategory of the SQL Server Agent procedures because SQL Server Agent handles much of the automatic updating of Web pages. Here is the list of the Web assistant procedures:
| sp_dropwebtask |
| sp_enumcodepages |
| sp_makewebtask |
| sp_runwebtask |
Replication Stored Procedures
You use replication stored procedures to set up and manage publication and subscription tasks. SQL Server Enterprise Manager typically provides a front end to these procedures, but you can also call them directly. SQL Server has many replication stored procedures; frankly, it's hard to manually use replication with these procedures. (You can do it, though, if you're determined.) Everything SQL Server Enterprise Manager does ultimately uses these system stored procedures. We urge you to use SQL Server Enterprise Manager or SQL-DMO if you need to customize replication administration into your application. The following list contains the replication stored procedures:
| sp_add_agent_parameter | sp_changemergearticle |
| sp_add_agent_profile | sp_changemergefilter |
| sp_addarticle | sp_changemergepublication |
| sp_adddistpublisher | sp_changemergepullsubscription |
| sp_adddistributiondb | sp_changemergesubscription |
| sp_adddistributor | sp_changepublication |
| sp_addmergearticle | sp_changesubscriber |
| sp_addmergefilter | sp_changesubscriber_schedule |
| sp_addmergepublication | sp_changesubstatus |
| sp_addmergepullsubscription | sp_change_subscription_properties |
| sp_addmergepullsubscription_agent | sp_deletemergeconflictrow |
| sp_addmergesubscription | sp_distcounters |
| sp_addpublication | sp_drop_agent_parameter |
| sp_addpublication_snapshot | sp_drop_agent_profile |
| sp_addpublisher70 | sp_droparticle |
| sp_addpullsubscription | sp_dropdistpublisher |
| sp_addpullsubscription_agent | sp_dropdistributiondb |
| sp_addsubscriber | sp_dropdistributor |
| sp_addsubscriber_schedule | sp_dropmergearticle |
| sp_addsubscription | sp_dropmergefilter |
| sp_addsynctriggers | sp_dropmergepublication |
| sp_article_validation | sp_dropmergepullsubscription |
| sp_articlecolumn | sp_dropmergesubscription |
| sp_articlefilter | sp_droppublication |
| sp_articlesynctranprocs | sp_droppullsubscription |
| sp_articleview | sp_dropsubscriber |
| sp_browsereplcmds | sp_dropsubscription |
| sp_changearticle | sp_dsninfo |
| sp_changedistpublisher | sp_dumpparamcmd |
| sp_changedistributiondb | sp_enumcustomresolvers |
| sp_changedistributor_password | sp_enumdsn |
| sp_changedistributor_property | sp_enumfullsubscribers |
| sp_generatefilters | sp_helpsubscription |
| sp_getmergedeletetype | sp_helpsubscription_properties |
| sp_get_distributor | sp_link_publication |
| sp_grant_publication_access | sp_mergedummyupdate |
| sp_help_agent_default | sp_mergesubscription_cleanup |
| sp_help_agent_parameter | sp_publication_validation |
| sp_help_agent_profile | sp_refreshsubscriptions |
| sp_help_publication_access | sp_reinitmergepullsubscription |
| sp_helparticle | sp_reinitmergesubscription |
| sp_helparticlecolumns | sp_reinitpullsubscription |
| sp_helpdistpublisher | sp_reinitsubscription |
| sp_helpdistributiondb | sp_removedbreplication |
| sp_helpdistributor | sp_replcmds |
| sp_helpmergearticle | sp_replcounters |
| sp_helpmergearticleconflicts | sp_repldone |
| sp_helpmergeconflictrows | sp_replflush |
| sp_helpmergedeleteconflictrows | sp_replicationdboption |
| sp_helpmergefilter | sp_replication_agent_checkup |
| sp_helpmergepublication | sp_replshowcmds |
| sp_helpmergepullsubscription | sp_repltrans |
| sp_helpmergesubscription | sp_revoke_publication_access |
| sp_helppublication | sp_script_synctran_commands |
| sp_helppullsubscription | sp_scriptdelproc |
| sp_helpreplicationdb | sp_scriptinsproc |
| sp_helpreplicationdboption | sp_scriptupdproc |
| sp_helpsubscriber | sp_subscription_cleanup |
| sp_helpsubscriberinfo | sp_table_validation |
Extended Stored Procedures
Extended stored procedures allow you to create your own external routines in a language such as C and have SQL Server automatically load and execute those routines just like a regular stored procedure. As with stored procedures, you can pass parameters to extended stored procedures, and they can return results, return status, or both. This allows you to extend SQL Server capabilities in powerful ways. Many SQL Server features introduced in the last few years have been implemented using extended stored procedures. These features include additions to SQL Server Enterprise Manager, login integration with Windows NT domain security, the ability to send or receive e-mail messages, and the ability to create a Web page based on a query.
Extended stored procedures are DLLs that SQL Server can dynamically load and execute. Extended stored procedures aren't separate processes spawned by SQL Server ”they run directly in the address space of SQL Server. The DLLs are created using the Open Data Services (ODS) API, which SQL Server also uses.
Writing an extended stored procedure sounds harder than it really is, which is probably why these procedures are somewhat underused. But writing one can be as simple as writing a wrapper around a C function. For example, consider the formatting capabilities in SQL Server's PRINT statement, which are limited and don't allow parameter substitution. The C language provides the sprintf function, which is powerful for formatting a string buffer and includes parameter substitution. It's easy to wrap the C sprintf function and create an extended stored procedure that calls it, resulting in the procedure xp_sprintf . To show you how easy this is, below is the entire source code for the procedure xp_sprintf . Note that most of this code is setup code, and at the heart is the call to the C run-time function sprintf() :
// XP_SPRINTF // // Format and store a series of characters and values into an // output string using sprintf // // Parameters: // srvproc - the handle to the client connection // // Returns: // XP_NOERROR or XP_ERROR // // Side Effects: // // SRVRETCODE xp_sprintf(SRV_PROC * srvproc) { int numparams; int paramtype; int i; char string [MAXSTRLEN]; char format[MAXSTRLEN]; char values[MAXARGUMENTS][MAXSTRLEN]; char szBuffer[MAXSTRLEN]; // Get number of parameters // numparams=srv_rpcparams(srvproc); // Check number of parameters // if (numparams < 3) { // Send error message and return // LoadString(hModule, IDS_ERROR_PARAM, szBuffer, sizeof(szBuffer)); goto ErrorExit; } paramtype=srv_paramtype(srvproc, 1); if (paramtype != SRVVARCHAR) { // Send error message and return // LoadString(hModule, IDS_ERROR_PARAM_TYPE, szBuffer, sizeof(szBuffer)); goto ErrorExit; } if (!srv_paramstatus(srvproc, 1)) { // Send error message and return // LoadString(hModule, IDS_ERROR_PARAM_STATUS, szBuffer, sizeof(szBuffer)); goto ErrorExit; } for (i = 2; i <= numparams; i++) { paramtype=srv_paramtype(srvproc, i); if (paramtype != SRVVARCHAR) { // Send error message and return // LoadString(hModule, IDS_ERROR_PARAM_TYPE, szBuffer, sizeof(szBuffer)); goto ErrorExit; } } for (i=0; i < MAXARGUMENTS; i++) { memset(values[i], 0, MAXSTRLEN); srv_bmove(srv_paramdata(srvproc, i + 3), values[i], srv_paramlen(srvproc, i + 3)); } memset(string, 0, MAXSTRLEN); srv_bmove(srv_paramdata(srvproc, 2), format, srv_paramlen(srvproc, 2)); format[srv_paramlen(srvproc, 2)]=' |
Because extended stored procedures run in the same address space as SQL Server, they can be efficient; however, a badly behaved extended stored procedure could crash SQL Server, although this unlikely . A server crash would probably result from someone's maliciousness rather than carelessness. But this is a definite area for concern, and you should understand the issues that are covered in the rest of this section.
An extended stored procedure runs on the thread that called it. Each calling thread executes using the Windows NT structured exception handling constructs (most notably, try-except ). When a thread is poorly written and performs a bad operation, such as trying to reference memory outside its address space, it is terminated. But only that single connection is terminated, and SQL Server remains unaffected. Any resources held by the thread, such as locks, are automatically released.
In actual use, extended stored procedures don't introduce significant stability issues into the environment. Nonetheless, it's certainly possible for an extended stored procedure to twiddle some data structure within SQL Server (to which it would have access because the procedure is part of SQL Server's address space) that could disrupt SQL Server's operation or conceivably even corrupt data. If you're unlucky, this could happen as the result of a bug in the extended stored procedure; however, it's more likely that the procedure would cause an access violation and have its thread terminated with no ill effects.
A procedure could conceivably cause data corruption, but such data structures aren't exposed publicly , so it would be hard to write a malicious procedure. It is possible, however, and given the propensity of some malicious people to create viruses, we can't rule this problem out (although no documented cases describe this happening). The ultimate responsibility for protecting your data has to rest with your system administrator, who has control over which, if any, extended stored procedures can be added to the system.
A Word of WarningAlthough extended stored procedures can be terrific additions to your applications and in most cases do not negatively impact your system's behavior, exceptions do exist. You can save yourself hours of grief if you keep the following in mind:
- Be sure to include full error checking and exception handling. An unhandled exception will usually bring down SQL Server along with generating a Dr. Watson dump.
- Be sure to stress test your extended procedures thoroughly. Don't assume that, because they are not running on SQL Server itself, you don't have to be concerned about how SQL Server will behave with hundreds or thousands of users accessing it.
- Read and follow the guidelines in Microsoft Knowledge Base article Q190987, entitled "Extended Stored Procedures: What Everyone Should Know," which is included on the CD.
Only someone with the sysadmin role can register an extended stored procedure with the system (using sp_addextendedproc ), and only a system administrator can grant others permission to execute the procedure. Extended stored procedures can be added only to the master database (eliminating their ability to be easily transferred to other systems via a dump and load of databases, for example). Administrators should allow use of only the procedures that have been thoroughly tested and proven to be safe and nondestructive.
Ideally, administrators could also have access to the source code and build environment of the extended stored procedure to verify that it bears no malicious intent. (Some people say they don't even want their SQL Server administrator to be able to do this ”because that person might not be trustworthy. If that's the case, you have bigger problems. If you can't trust your system administrator, you'd better get a new one!)
Even without extended stored procedures, an administrator can disrupt a SQL Server environment in many ways. (Munging the system tables would be a good start.) Of course, you can decide that no one will ever add extended stored procedures to your system. That's certainly a safe approach, but you give up a powerful capability by taking this route. (It's kind of like deciding never to ride in a car to avoid having an accident .)
Even if you prohibit foreign extended stored procedures from your system, don't go overboard with a sweeping rule that would prevent use of even the procedures provided by Microsoft to implement new features. Could one of these procedures have a bug that could disrupt SQL Server? Sure, but a bug is no more likely to occur than if the code for these procedures had simply been statically linked into the sqlservr.exe file rather than implemented as a DLL and loaded on demand. (Of course, Microsoft procedures are thoroughly tested before their release. The chance of a catastrophic bug occurring is pretty low.) The fact that these are extended stored procedures in no way increases the risk of bugs . It's an engineering decision ”and a smart one ”that allows Microsoft to add more features to the product in a way that doesn't require extra change to the core product or additional resource use by environments that don't call these features.
By convention, most of the extended stored procedures provided as part of the product begin with xp_ . Unlike the sp_ prefix, no special properties are associated with xp_ . In fact, several extended stored procedures begin with sp_ (for example, sp_getbindtoken ), which allows them to be called from any database without being fully qualified (so we could just call it with EXEC sp_getbindtoken instead of EXEC master.dbo.xp_getbindtoken ). To ascertain whether a procedure is a regular stored procedure or an extended stored procedure, you shouldn't rely on the prefix of the name. Use the function OBJECTPROPERTY(). For example, the following should return 1, indicating that sp_getbindtoken is an extended procedure:
USE MASTER SELECT OBJECTPROPERTY(object_id('sp_getbindtoken'), 'IsExtendedProc') |
If you substitute a "real" stored procedure name for sp_getbindtoken , like sp_help , the function will return a 0.
As was the case with stored procedures, some extended stored procedures that are installed are not documented for direct use. These procedures exist to support functionality elsewhere ”especially for SQL Server Enterprise Manager, SQL-DMO, and replication ”rather than to provide features directly themselves.
Following are the extended stored procedures that are provided and documented for direct use. First, here are the general extended stored procedures:
| xp_cmdshell |
| xp_sprintf |
| xp_sscanf |
Here are the administration and monitoring extended stored procedures:
| xp_logevent | xp_msver |
| xp_snmp_getstate | xp_snmp_raisetrap |
| xp_sqlinventory | xp_sqlmaint |
These are the integrated security- related extended stored procedures:
| xp_enumgroups | xp_grantlogin |
| xp_loginconfig | xp_logininfo |
| xp_revokelogin |
And finally, the SQL mail-related extended stored procedures:
| xp_deletemail | xp_findnextmsg |
| xp_readmail | xp_sendmail |
| xp_startmail | xp_stopmail |
SQL Server Profiler Extended Procedures
SQL Server Profiler provides a graphical user interface to a set of extended stored procedures for monitoring dozens of aspects of SQL Server's internal behavior. You can also use these extended stored procedures to create your own applications that monitor SQL Server. We'll be discussing SQL Server Profiler in Chapter 15, but to complete our list of extended procedures, here are the ones for working with SQL Server Profiler:
| xp_sqltrace | xp_trace_getuserfilter | xp_trace_addnewqueue |
| xp_trace_getwritefilter | xp_trace_deletequeuedefinition | xp_trace_loadqueuedefinition |
| xp_trace_destroyqueue | xp_trace_pausequeue | xp_trace_enumqueuedefname |
| xp_trace_restartqueue | xp_trace_enumqueuehandles | xp_trace_savequeuedefinition |
| xp_trace_eventclassrequired | xp_trace_setappfilter | xp_trace_flushqueryhistory |
| xp_trace_setconnectionidfilter | xp_trace_generate_event | xp_trace_setcpufilter |
| xp_trace_getappfilter | xp_trace_setdbidfilter | xp_trace_getconnectionidfilter |
| xp_trace_setdurationfilter | xp_trace_getcpufilter | xp_trace_seteventclassrequired |
| xp_trace_getdbidfilter | xp_trace_seteventfilter | xp_trace_getdurationfilter |
| xp_trace_sethostfilter | xp_trace_geteventfilter | xp_trace_sethpidfilter |
| xp_trace_geteventnames | xp_trace_setindidfilter | xp_trace_getevents |
| xp_trace_setntdmfilter | xp_trace_gethostfilter | xp_trace_setntnmfilter |
| xp_trace_gethpidfilter | xp_trace_setobjidfilter | xp_trace_getindidfilter |
| xp_trace_setqueryhistory | xp_trace_getntdmfilter | xp_trace_setqueueautostart |
| xp_trace_getntnmfilter | xp_trace_setqueuecreateinfo | xp_trace_getobjidfilter |
| xp_trace_setqueuedestination | xp_trace_getqueueautostart | xp_trace_setreadfilter |
| xp_trace_getqueuedestination | xp_trace_setserverfilter | xp_trace_getqueueproperties |
| xp_trace_setseverityfilter | xp_trace_getreadfilter | xp_trace_setspidfilter |
| xp_trace_getserverfilter | xp_trace_setsysobjectsfilter | xp_trace_getseverityfilter |
| xp_trace_settextfilter | xp_trace_getspidfilter | xp_trace_setuserfilter |
| xp_trace_getsysobjectsfilter | xp_trace_setwritefilter | xp_trace_gettextfilter |
EAN: 2147483647
Pages: 144