Warming Up SQL Server

As discussed in Chapter 2, stored procedures are compiled and cached only at their first call by an application. You can help increase performance and ready a server for certain forms of access and functions when a server starts up, before users access it, by automatically executing certain stored procedures. This means that when users connect and use a stored procedure, it will already be in the stored procedure cache.

Automatically executing stored procedures on startup is also highly useful for certain management applications or functionality that a server needs to have soon after startup. I call this warming up SQL Server. You should take note of a few nuances and rules before you consider using this “warm-up” capability These nuances are as follows:

• The creator or owner of an autoexecuted stored procedure can only be the system administrator or a member of the sysadmin fixed server role.

• The stored procedure must be a background process and cannot take any input parameters.

Each autoexecuted, or startup, stored procedure makes a connection to the DBMS. You can have as many autoexecuted stored procedures as you like, but if each one consumes a connection, this could be a significant drain on resources. If the stored procedures do not need to be executed concurrently, you can nest them. Thus, you could create a single stored procedure that calls a list of stored procedures synchronously. The cascade of stored procedures only consumes one connection, the one incurred by the autoexecuting stored procedure.

By cascading or nesting stored procedures, you could thus call user-defined stored procedures and even pass parameters into them. This can be useful for an application that requires certain information and objects to be available to users when the databases come back online.

In one of my call centers, if I need to restart or “IPL” a server for any reason, or we suffer a server or host crash, an autoexecuted stored procedure sends a message to database users when the server is ready and they can reconnect. This saves the help desk from having to call or e-mail users that the server is back up and can be accessed again.

If you have a problem and you ever need to delay the autoexecuting of stored procedures, you can start the instance of SQL Server 2005 with the -f flag. This will start the server in a minimal configuration (like Safe mode on Windows Server 2003) and allow you to debug the problem. You can also specify trace flag 4022 as a startup parameter, which forces the startup to bypass autoexecution.

To create a startup or autoexecuted stored procedure, you need to be logged in as a member of the sysadmin role and you must create the procedure in the master database.

You can also use sp_procoption to designate an existing stored procedure as a startup stored procedure, to reset the startup option on a stored procedure, or to view a list of all stored procedures that execute on startup.