| Downloads |
|---|
| 41458.zip |
For SQL Server Magazine's 5-year anniversary issue, the contributing editors were asked to submit some of what we thought were our best tips from the past 5 years. At first, I thought that the information I typically write about doesn't lend itself to tips, but then I realized that this wasn't exactly true. Frequently, when I'm discussing SQL Server's internal behavior, I suggest how you can use this information to your advantage. So let's look at five suggestions for putting this inside information to work.
Keep Your Queries Covered
TIP Consider adding one or two additional columns to nonclustered indexes so that some of your critical or frequent queries can become covered queries.
Because clustered indexes sort the data on the data pages in index-key order, they're highly efficient when you're looking for lots of duplicates or a range of data. However, a table can have only one clustered index. A nonclustered index also contains sorted data, but only at the index's leaf level. So a nonclustered index on city would list all cities in alphabetical order, but if you also want the customer names associated with those cities, you have to follow the pointer (aka bookmark) from each city to its associated data row. If you're interested in many customers, accessing each of the relevant data pages could be a lot of work. But what if the customer name were part of the index? SQL Server allows up to 16 columns in a nonclustered index key, so you could have 16 columns in the leaf level. If all the data your query needs is part of the index, SQL Server never needs to go to the data pages, so your query will run very fast. A query for which you can retrieve all the data by index access alone is called a covered query, and an index that includes all the columns a query needs is called a covering index.
You might have more covering indexes than you think. Remember that if a table has a clustered index, the clustered key is the bookmark for all nonclustered index leaf rows, which is like always having an extra key in your nonclustered indexes. So if a table has a nonclustered index on lastname and firstname and a clustered index on employeeID, every nonclustered leaf row contains all three of those key values. This nonclustered index covers any queries that are looking for only firstname, lastname, and employeeID.
Keep in mind that a covering index on one table can be useful even if not every table in a query has a covering index. In a multitable query, as long as all the referenced columns from one table, including the join column, are in a nonclustered index, SQL Server can use that index as a covering index.
Beware of Hints
TIP Avoid using hints in your queries. If testing reveals that hints are necessary in some situations, plan for regular retesting.
Although SQL Server 2000 provides more than 30 optimizer hints you can use in your queries to force certain optimizer behaviors, most official tuning guides recommend that you don't use hints. For the most part, I agree with this suggestion. The optimizer is one of the most complex parts of the SQL Server query engine; by using a hint, you're preventing SQL Server from taking advantage of this sophisticated piece of code. In some cases, a hint might be justified for a query at one time, but it might not continue to be useful as your data changes or as you apply new service packs that include optimizer enhancements. Once you use a hint in your production code, SQL Server always processes the query in the way you specified, even if changes in data distribution mean that your hint no longer provides the best processing plan.
I've had the daunting task of trying to tune existing applications that make liberal use of optimizer hints. I usually suspect that most of the hints are unnecessary or even harmful months or years after they were originally deemed useful. So what do you do in such a situation? Should you go through all the production code to remove the hints, then test to see which ones to put back? Fortunately, SQL Server provides a somewhat easier—though incomplete—solution. You can use three undocumented trace flags (which I discussed in my November 2002 column, "Investigating Trace Flags," InstantDoc ID 26406) as part of SQL Server's startup parameters to tell SQL Server to ignore a certain class of hint. Trace flag 8602 tells SQL Server to ignore all index hints, trace flag 8722 tells SQL Server to ignore all query hints (in the OPTION clause), and trace flag 8755 tells SQL Server to ignore all locking hints.
By running your applications with these trace flags on, you can determine how much you'll benefit by removing the hints. After you decide that the benefit is worth the cost, you can plan for removing the hints and retesting the performance. Some hints might still be useful, but using these trace flags will tell you whether having no hints is better than keeping all those you have.
Keep Transactions Tangle-Free
TIP Check @@trancount before restarting a transaction to make sure SQL Server doesn't hold locks longer than necessary.
I've written several columns about locking and the relationship between locking and transactions. I always note that SQL Server doesn't release exclusive locks until the current transaction is completed by being committed or rolled back. Most people know about that relationship, but many people aren't aware of what happens when you nest transactions. Logically, no such thing as a nested transaction exists in SQL Server. You can have at most one active transaction for a connection. If your T-SQL code issues multiple BEGIN TRANSACTION statements, you have syntactically nested transactions, but only one is active. The effect of issuing a second BEGIN TRANSACTION statement while another transaction is active is that SQL Server increments an internal counter, which you can observe by looking at the value of the @@trancount function.
When you execute COMMIT TRANSACTION, SQL Server decrements the value of @@trancount; only when the value reaches 0 does SQL Server commit the active transaction and release any locks. So to commit any open transactions, you must have exactly as many COMMIT TRANSACTION statements as BEGIN TRANSACTION statements. However, a ROLLBACK TRANSACTION statement resets the @@trancount value to 0, and SQL Server immediately rolls back the transaction and releases all locks.
In some situations, you might think a transaction has been rolled back, but it hasn't been. Many errors return an error message and abort the current statement without rolling back the transaction. I've seen application developers restart a transaction with a new BEGIN TRANSACTION if errors occur, but they don't check @@trancount. Then when a COMMIT TRANSACTION executes, the developers think the transaction has committed, but the transaction is still active and the locks are still in place.
You can run into similar problems when a process or user sends a CANCEL command to SQL Server. A CANCEL command cancels a running batch but doesn't roll back a transaction. So if you issue another BEGIN TRANSACTION after canceling the current batch, you might have syntactically nested transactions that require multiple COMMIT TRANSACTION commands to get out of.
One way to tell whether a process has become entangled in nested transactions is to look at the sysprocesses table, as I discussed in my August 2003 column, "Track Down Troublemakers" (InstantDoc ID 39453). In the sysprocesses column called open_tran, look for values greater than 1. If a process consistently has an open_tran value greater than 1, examine the locks that the process is holding and try to track down why the process is ending up with nested transactions.
Keep Your Blackbox Enabled
TIP Keep your blackbox running, and don't forget it's there when you need it.
A blackbox is a special trace file that records the last 5MB of T-SQL statements any application sent to a SQL Server. It's a true rollover file in that it starts overwriting itself as soon as it reaches the 5MB limit. In my January 2001 column, "Profiler's Blackbox Feature" (InstantDoc ID 16078), I provided complete instructions for setting up and managing such a trace file and explained how to set up a stored procedure that automatically starts the blackbox trace every time your SQL Server starts.
The overhead of a blackbox is very low, so I recommend that you always have one running. If you see behavior that you can't explain or your SQL Server suddenly stops for no obvious reason, being able to inspect a blackbox record can be of enormous benefit.
Personalize Your Performance Monitor
TIP With user-defined counters in Performance Monitor, you can monitor any numeric information you can extract from your SQL Server data or metadata.
A client of mine was having excessive blocking problems and had set up Performance Monitor to track the total number of locks in her SQL Server system. However, this information was only somewhat useful because most locks are transient and not all locks are bad. Locks are bad only when SQL Server doesn't release them in a timely manner, preventing other processes from getting the locks they need.
I suggested that we track the number of processes that were blocking other processes. In "Track Down Troublemakers," I provided a script that reports information about processes that are blocking others but aren't being blocked themselves. These processes are called heads of lock chains. Monitoring processes at the head of a lock chain might be useful, but Performance Monitor doesn't provide such a counter by default. However, SQL Server provides 10 user-defined counters that you can define and track through Performance Monitor. Choose the SQL Server: User Settable object and select one of the 10 instances. Then, use SQL Server to assign a value to the counter. For example, for User Counter 1, you'd use the system stored procedure called sp_user_counter1 to set this counter's value. (SQL Server has nine similarly named stored procedures for the other counters.) If you call this procedure and pass it a numeric parameter, Performance Monitor displays the value you pass. I wrote the loop that Listing 1 shows to populate the value for User Counter 1 with the current number of heads of lock chains every 5 seconds.
Understanding the details of SQL Server's inner workings can give you ideas for getting better performance or for simplifying your job. Tips are where you find them. So here's one final tip: SQL Server Magazine is a great place to find awesome tips every month.
