Performance Secrets for SQL Server Developers

One of the primary things that I do as a consultant is to help companies evaluate the gap between where their current SQL Server deployments are and where they should be in relation to industry best practices. By offering a number of different auditing services and options, I help clients validate code, optimize performance, and evaluate their needs for future growth. And because many of my audits are holistic in nature, I’ve gained great insights over the years into some common developer pitfalls and mistakes that can adversely affect performance. This article focuses on 10 common performance problems (presented in no particular order) that developers can avoid with a modicum of effort.

1. Failure to Properly Configure AWE Memory Usage
Although configuring server memory is obviously an IT or DBA responsibility, I’ve seen too many environments where “poorly performing code” was actually the result of a system with 8GB (or more) of RAM restricting SQL Server to only 2GB. In fact, from what I’ve seen, I’d wager that this problem is so widespread that more than half of production SQL Server deployments suffer from it. Of course, by helping clients remedy this situation, I end up being a hero; adding more RAM is typically one of the easiest ways to boost performance, as long as SQL Server can use the memory.

Happily, you, too, can be a hero by enabling SQL Server to use more than 2GB of RAM. It’s a relatively simple operation—although I wish that the SQL Server installer offered to configure Address Windowing Extensions (AWE) during installation. To start, verify that SQL Server is using AWE, which you can do by executing the code shown in the first part of Listing 1. (Note that some lines in the listings are wrapped to fit on the printed page.) If you have more than 2GB of physical RAM and AWE isn’t enabled, you can use the remaining code in Listing 1 to quickly configure SQL Server to use the additional memory.

The trick to enabling AWE is to ensure that the service account used by SQL Server has been granted the Lock pages in memory user right, as Figure 1 shows. Without this critical configuration change, SQL Server can’t use more than 2GB of RAM, no matter how much physical memory is available. Also note that if you need to change this configuration, you need to restart the SQL Server service before you can realize the benefits. (For more information, see the Microsoft article “How to configure SQL Server to use more than 2 GB of physical memory” at support.microsoft.com/?kbid=274750, which applies to SQL Server 2005 and SQL Server 2000.)

2. Failure to Normalize
Many performance-tuning articles suggest that you intentionally denormalize databases for the sake of performance (to what I jokingly call 2 ¾ normal form). Although internal denormalization is a valid approach that’s covered elsewhere, it’s also a sad fact that databases are occasionally deployed by developers who either don’t know anything about normalization or who fail to grasp its importance.

In these cases, the extra business logic required to extract useful information from jagged Frankencolumns of tangled data adds significant processing overhead. However, this additional overhead usually doesn’t become a performance problem until the database becomes heavily used. Of course, by that time there’s typically no easy or cost-effective way to remedy the situation. Therefore, although it sounds trite, one of the best things that developers can do to ensure good performance is start with a solid, normalized foundation. The cost and headache of doing otherwise can quickly become a true performance nightmare.

3. Failure to Use Clustered Indexes
When you’re looking to squeeze seconds or milliseconds of additional performance out of your code, the prospect of shutting down your production database for a few hours to correct a performance problem probably doesn’t make much sense. But doing so is the only way to remedy common performance problems stemming from the lack of a clustered index.

As with normalization problems, failure to use clustered indexes is a performance issue that typically won’t surface until a table starts to get fairly large (say, a few million rows). But by that time, the table has typically become heavily fragmented (both physically and logically, through the use of forwarding records), and queries involving bookmark operations can become slow enough to draw the attention of end users and management. Sadly, at this point the only available options are to either re-create the table or try to slap a clustered index on it. Both operations can take hours (though recreating the table is typically faster), and end users can’t use the table while the data is being restructured— which in most cases renders your database useless for lengthy periods.

Therefore, it’s a good rule of thumb to make sure that all tables have a clustered index. Likewise, given the fact that a well-placed clustered index can drastically improve ranged queries, it’s also a good practice to ensure that any table with more than 20,000 rows has a well-considered clustered index.

4. Failure to Optimize
If you’re interested in improving the performance of your code, your efforts will only go so far if your database is missing key indexes, has heavy table or index fragmentation, or doesn’t have accurate statistics. Furthermore, since some DBAs are either too swamped to optimize index placement (or consider it a developer responsibility), it behooves developers to ensure that foreign keys and other joined columns are indexed on both sides of the join (i.e., in both tables).

Likewise, for developers whose code is being fingered for performance problems, it’s always good policy to verify that indexes are being regularly rebuilt and defragmented and that statistics are being regularly updated. Doing so can help you avoid wasted effort. Because I find that “failure to DBA” is a common problem in many engagements where I’m called in to help tune “poorly running code,” I’ve provided two scripts (Web Listings 1 and Web Listings 2 at www.sqlmag.com, InstantDoc ID 99148) that you can use to help evaluate statistics and index fragmentation.

5. Failure to Create SARGable Queries
Highly optimized queries against large tables in SQL Server take advantage of index-seek operations instead of more expensive scanning operations involving tables and indexes. (The difference between these operations is analogous to seeking against the alphabetized listing of last names in your phone book to find every “Campbell” versus scanning the phone book for every “Michael”—the difference can be expressed in orders of magnitude.) Accordingly, queries that can take advantage of index-seek operations are commonly referred to as SARGable queries (where SARG is a word play on Search ARGument).

Creating SARGable queries might sound a bit spooky to the uninitiated, but once you learn a few simple syntax rules and ensure that a viable index is in place, creating SARGable queries actually becomes second nature. Happily, you can find a wealth of resources (both online and in print) that cover this topic in depth, but for a quick overview, see Listing 2.

Continue on Page 2

   Previous  [1]  2  Next