Stored Procedure performance using “sp_” prefix – Myth or fact?

Geeking with sp_.

WHAT’S IN A NAME?

We have all heard that prefixing SQL Server stored procedures with “sp_” is a no-no; you have probably read the many reasons not to do this, but the most familiar ones to developers are that 1) Microsoft uses the “sp_” naming convention for their system stored procedures and so this should be avoided, 2) a procedure prefixed with “sp_” might conflict with one of their procedures and thus may never be called, 3) the SQL engine may first look to the master database for an “sp_” stored procedure before looking at the particular user database where the procedure call originated which might cause a delay, 4) these procedures might not upgrade cleanly, and 5) other resolution problems. There are probably more – maybe you have some to add - but these are the ones that I am familiar with.

THAT’S WHAT SHE SAID

But, do we really know this to be truth, or do have we simply taken someone’s word on it, treated it as sacred cow, or simply restated it for years it because it sounds good? Interestingly, Microsoft or Books Online specifically, issues only the following in their help section on creating stored procedures:

We strongly recommend that you not use the prefix sp_ in the procedure name. This prefix is used by SQL Server to designate system stored procedures. For more information, see Creating Stored Procedures (Database Engine).

In the Naming Stored procedures section of Books Online they add the following to the above:

We recommend that you do not create any stored procedures using sp_ as a prefix. SQL Server uses the sp_ prefix to designate system stored procedures. The name you choose may conflict with some future system procedure. If your application uses nonschema qualified name references and your own procedure name conflicts with a system procedure name, your application will break because the name binds to the system procedure, not your own.

So here’s my question…has anyone actually ever tested this to find out for themselves? I certainly haven’t investigated this closely, and I’ve been writing stored procedures for a long time. If you answered yes, your findings didn’t make Google!, at least that I could see anyway. So, I’ve decided to find out if this is really fact or myth.

TEST SCENARIO TO FIND OUT IF IT REALLY MATTERS

For my testing cases, I have chosen to the following rules and constants for my scenario to test the performance of an sp_ stored procedure to one named proc_:

• Use a script from a previous blog to insert records into a table.
• Wrap it in a loop, place it in a stored procedure.
• Call three processes from sqlcmd, asynchronously, via a batch file.
• Use separate procedures, one prefixed sp_ and with proc_.
• Use DBCC FREEPROCCACHE, DBCC DROPCLEANBUFFERS for each run.  
• Truncate table for each run.
• Make sure log/data files have plenty of space beforehand so that no growth will occur during run.
• Alternate runs in a logical order
• Use implicit transactions
• Do not use any TSQL query hints

Using the above list, I was able to create a very intensive process which, through calculation and perfmon, revealed between 2500 and 5000 transactions per second.  I ran this over a minute and one half period, give or take, to insert several hundred thousand rows into a single table. Below are some of the results, in seconds:

Figure 1. Raw data of sp_ vs proc_

Figure 1 is a listing of the runs included in the test.  These raw numbers have been plucked out of SQL Profiler; I chose not to use perfmon because, while I could have recorded the results to a log file, I reasoned that the number of records inserted into my table is constant, so I could easily find transactions/second by simply dividing them by the time it took for the batch to complete. Doing a few simple averages I come up with the following:

Figure 2. Analysis of Figure 1.

Again, the three entries in my results come from each of the procedures fired three times asynchronously via sqlcmd from a batch file (asynchronously so they would all start at the same time and finish nearly the same time).  The results from Figure 2 indicate that both the average times and average trans/sec are considerably faster using proc_ than sp_.  The time to completion for sp_ as a percentage of proc_ ranges from roughly 14% to 23% slower.  The average run-time in milliseconds across all runs was 134.72 to 114.07 seconds (432000 inserts), or 18% faster, and an average trans/sec of 3830 to 3230, which is roughly 16% more transactions/sec.  The sp_ on average was slower and performed fewer transactions given the time frame that it took to perform the inserts.  Another interesting statistical view in Figure 3.  I have created a conditional tail expectation, or CTE in statistical-speak, on both procedures’ 24 runs.

Figure 3. Conditional Tail Expectation statistical view 

This particular analysis orders each of the runs and then allows me to find the expectation if I were to run these at any given point in time. (Twenty-four in my sample is somewhat small, but I will go ahead and use here for demonstration purposes).  For 50% of the time that I run each procedure, I am sure that sp_ will run in 105 seconds, and proc_ will run in 97 seconds.  For approximately 80% of the time, I am sure that sp_ will take 123 seconds to run, but proc_ will only take 107 seconds to run, and finally, for nearly 95% of the time that I run either procedure, sp_ will take about 130 seconds, but proc_ will only take 111 seconds; this is nearly 15% faster given the average of all runs preceding.  We can conclude, therefore, that for this test a procedure prefixed with sp_ was clearly slower than a procedure prefixed with proc_ by somewhere in the neighborhood of 15%, on average.

CONCLUSION

From our test, we can see that we should not use sp_ for naming stored procedures. Why would anyone want to do this anyway? Remember that if you prefix your stored procedure with sp_ and place it in the master database, you can call it from any database context without having to change the context of the batch or script;  I guess this is handy, but since I have been writing procedures I’ve really never had to do this that I can recall.  Maybe I have used it somewhere, but I don’t remember.

Lee Everest

-----------------------------

Interesting factoid – usp_ came from the Wrox set of books circa 2001. Ugh.

Code: http://www.texastoo.com/images/sp_test.zip