Are They Really Using All Those Stored Procedures?

By Bill Wunder


Follow me on a few twists and turns (OK, even a few grumbles) through SQL Server’s memory pool and I’ll take you to a little "set it up and go" tool that will give you a remarkably accurate indication of which of those piles and piles of stored procedures on your system are every really getting used and the degree to which each is really called.


SQL Server memory space has evolved over time into a virtual black box. In the early generations of the product memory configuration and management was quite the voodoo science. It was necessary to tell the server how much memory went to procedure cache and it was possible to define how much of the reaming would be distributed among the various types of data cache buffers and pools. The number of configuration options was a bit daunting, the measurement techniques were obscure and/or expensive, and the documentation was woefully inadequate. Interestingly, manifestations of memory leaks were an every day occurrence. The more work the SQL Server did – and for the most part irregardless of which memory tweaks were in place – the more often the SQL Server either crashed or needed to be started or needed to be rebooted because memory management  wasn’t doing it’s job.


Fortunately, the recent releases of SQL Server are much better at memory management. The improvements have led to an almost complete elimination of manually configurable memory options and to a much more stable primary storage environment. Under the covers, though, the memory pool retains much the same architectural layout as the legacy releases. There is still a fixed amount of memory allocated for SQL Server executable code address space with the remaining majority going to cache. SQL Server executable code address space includes the kernel and the server’s internal road map(s) to the protocols, .dlls and providers currently in use by the SQL Server. This piece of memory still seems a bit susceptible to leaks that can bring a server down. (See my earlier article “Automation and T-SQL” for more on this topic). The SQL Server –g start up option is an offshoot of Microsoft’s attempts to address these problems. Personally, I’d rather avoid memory leaks when possible and reboot regularly than resort to intentionally stealing chunks of the memory pool. Don’t really think Microsoft has helped us much with the –g command line option, Must be one of those business risks driving it? (i.e. CYA technology). The memory pool – or cache - is the more interesting segment of a SQL Server’s working set. In this area reside the procedure, data and log caches as well as per connection dynamic data structures and the system data structures. Basically, these types of cache are left to talk amongst themselves to decide who gets what amount of the available pool.


It’s no surprise that data cache gets most of available pool. At least it better if you want decent performance from the SQL Server.  Obviously, connection information and system information (including sysprocesses, syslocks, sysperfinfo, syscacheobjects and the other virtual tables must be able to claim the space they need so their memory requirements directly impact the amount available for data, though the expectation would be that they do not to exact a terribly heavy cost. Procedure and log cache I would expect to remain a pretty constant fraction of data cache space size once the server has acquiesced after a start-up. Log data is probably going to disk as quickly as possible and likely gets a top priority thus holding down the space needs of that memory queue. Procedure cache is being managed through an LRU aging algorithm.


Here’s where it gets interesting. SQL Server uses the virtual table syscacheobjects to track LRU statistics and age the procedure cache. If you query syscacheobjects you will find a sort of index of the current state of the cache. The data objects in cache can change dramatically so trying to see what tables are in cache on a busy server is not practical. You can, however, easily look in syscacheobjects and see which embedded or ad-hoc query statements have recently been executed if you are trying to track down a problem where that information is useful. You can also look in this table to see which stored procedures are currently in use by checking for an “execution context” cacheobjtype or see which have recently been used by checking for “compiled object” cacheobjtype. I have found it useful to use that latter bit of information as a tool to identify which procedures are actually getting called on my SQL Servers.


If you examine the code in the stored procedure GetProceduresFoundInCache you’ll notice that it is a self contained utility - intended for creation in the admin database if course. The procedure creates the historical tracking table it uses and even a mini-stored procedure that it uses to track when the compiled objects in cache have been changed. It infers that cache has changed by regularly looking for this mini-proc in syscacheobjects every minute as it is executed from the SQL Agent job that you set up to call it. The mini-proc is effective because it should be at the lowest rung in the LRU algorithm at all times. It always has a use count of 1 which is the minimum and does not reference any other object in cache. If GetProceduresFoundInCache finds the mini-proc it does nothing. If the mini-proc is gone, GetProceduresFoundInCache captures a snapshot list stored procedures currently in cache and updates the table ProceduresFoundInCache from that snapshot.


Querying ProceduresFoundInCache can tell you when a procedure entered the table, when it was last called, and the number of times that it was found in cache when GetProceduresFoundInCache determined a sample was necessary. By Left Joining ProceduresFoundInCache to sysobjects in other user databases you can easily list those procedures that were never found in cache.


There is no written guarantee that every stored procedure will be cached, so after you have identified those that don't appear in syscacheobjects you can do a couple of things to see if a procedure just isn't ever cached. You can execute it and see if gets cached or you can add an insert into a tracking table to each identified unused procedure and leave it in the database for a comfortable period of time. The advantage to this latter approach is that you could even log the application name and host machine that referenced a procedure. Could be you'll uncover a complete application process that is unneeded or unknown.


The biggest problem I have with this tool is the high level of developer denial surrounding the amount of their hard work that isn’t being used.