Stored Procedures: Still Relevant

Author's Note: In my IT/Dev Connections Online session on "Stored Procedures: Why They're Still Relevant (and What You Need to Know to Use Them Efficiently)," I mentioned a few links and other follow-up resources that I’d make available.

Stored Procedures In a Nutshell

The key takeaway from my presentation was that even if developers are totally committed to using object-relational mappers, or ORMs (because of the productivity benefits they provide), it's still worthwhile to assume or anticipate that if/when your application becomes very successful (i.e., once you start storing lots of data—i.e., 10s of GBs—and getting larger and larger numbers of concurrent users), that you’ll start running into performance problems. Early on, many of those problems or issues can and should probably be addressed with additional hardware (RAM is dirt cheap; CPU isn’t terribly expensive—but licensing it for SQL Server is expensive) and indexes. After that, though, you’re going to potentially hit a point where you’ll need to carve out some of your ORM code and replace it with calls to sprocs—where you can finetune and finesse exponential scalability benefits out of your database.

I’ve discussed this subject at great length in my article, Entity Framework and ORMs – Understand the Trade-Offs.

But, a quicker, and much more succinct way of stating the same thought is something fellow ASPInsider Wally McClure tweeted:  

Finding Performance Problems and Issues

In my presentation, I mentioned that an easy-ish way to find performance issues is to use Profiler and dump the results of a session into Clear Trace, a free, killer, 3rd party tool, that’ll help you sanitize your queries down to core elements that can then be easily stacked or ranked based upon CPU utilization, duration, or disk usage, as needed. (This approach, though, is a bottom-up approach to performance tuning in that it means we're assuming there's a problem and looking for what might be causing it; completely the opposite of a top-down approach which is what you'd use if you knew that a specific query, or report, or operation was always sluggish.)

Another approach I mentioned was to use a script I'd created that multiples execution costs times execution counts to provide an aggregate cost of operations that lets you find the 'longest pole in the tent' (or the queries/operations that are likely consuming the most resources on your server). Along those same lines, I also mentioned a similar technique involving indexes.

Scripts for both approaches (along with some caveats/guidelines for using them), can be found below:

It's also worth mentioning that there are a whole HOST of other means and methods for finding performance issues (including using Extended Events instead of profiler as well as using WAIT STATS and performance counters and so on).

I'm also assuming you’ll be able to spot and then handle performance problems using any means outlined above. Performance tuning isn't exactly rocket science, but it does take significant experience and the right mindset and skillset. If you think you’re bumping into performance problems and don't have the requisite skills to address or even diagnose the problems, it's time to talk to a SQL Server Consultant who can either help show you how to diagnose and correct problems, or correct them for you.

Along those same lines, one thing I also mentioned in my session was that I'd provide an additional link that showcased how sprocs can be a formidable weapon in the war on performance and scalability—especially when used to address queries that try to both slice and dice at the same time. To that end, the technique I alluded to for using an hourglass approach (or chiasmus) to boost performance is outlined in my article, Generating High-Performance SQL Server Query Results. (For more information on query tuning and optimization, I highly recommend Benjamin Nevarez’s excellent book, Inside the SQL Server Query Optimizer. There are gobs of great books on optimization out there, but this one will easily get you on a solid footing by covering a clear and concise look at what amounts to being an advanced topic.)

Stored Procedures and SQL Injection

Hopefully, a number of people in my session almost bolted out of their chairs when I said that stored procedures don’t protect against SQL injection. They don't. That's the job of parameterization—or clearly and fully ensuring that command-language and data-language aren't intertwined, blurred, or combined. I've covered more on that in my article, SQL Injection – Beyond the Basics.

Sproc Coding Guidelines and Best Practices

A sizeable portion of my presentation dealt with some best practices and guidelines for key things to address when working with stored procedures. For additional information along those same lines, you might want to check my article, T-SQL Parameters and Variables – Basics and Best Practices. Likewise, something that I also called out that commonly trips up .NET developers if/when they start doing extensive coding in T-SQL is that variables are scoped ENTIRELY different within T-SQL than they are within the CLR.