SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

TechEd - DATTLC01 : Troubleshooting Performance Problems in SQL Server 2005

It's been a few years since I wrote my eBook on SQL Server performance monitoring but I still keep a strong interest in database performance and what people are doing to tweak more cycles out of their database servers. After all, if the database server isn't performing to expectations (so long as they are reasonable), users and management have a right to be upset. As a result, I decided to break in at TechEd by going to a seminar I thought would be a good fit for me, Troubleshooting Performance Problems in SQL Server 2005.

This one was held in the technical learning center down on the same floor as the expo. Unfortunately, what was being shown was on a flat screen, which was probably fine for up close, but mostly unreadable from the back (where several of us were standing). However, the presenters did a good job of explaining the examples and how they'd go about trying to troubleshoot the performance problem. Here are the examples they gave:

Example #1: SQL Server not accepting new connections due to blocking

As you might expect, this example was to show the power of the Dedicated Administrator Connection in SQL Server 2005. The setup for this example was simple:


  1. Create a transaction which does a write to a table but don't commit the transaction
  2. Use ostress to create enough connections, all querying the same table
  3. Attempt to connect using SQL Server Management Studio


Even with the normal connection failing, the DAC is still available. There are some limitations. Namely:


  • Limited memory space
  • No remote use of DAC by default (you'd have to log on to the server locally, either at the console or through Remote Desktop)
  • Not enabled by default on SQLExpress


The first limitation is to keep a potentially overloaded SQL Server from getting too much worse. The second is for security reasons (if the connection is not needed remotely, it shouldn't be enabled, therefore it is disabled by default). The third is to keep the memory footprint for SQLExpress as small as possible.

The demonstration showed DAC being used to kill the processes waiting on the SELECT queries. A warning was given, and a valid one, to be careful with kill. It doesn't always succeed. And even when it does, there may be situations where you don't want it to kill a particular process (for instance, the one doing the inserting).

Example #2: High CPU Utilization

As always, the first thing stated was to have a baseline. Without the baseline, it's not possible to tell if a server is running with a higher CPU than normal. Without the baseline we can see if the CPU is crossing over into the territory where we would look at it as a potential bottleneck, but if the normal CPU is below that, we don't have a lot to go on. After you get past that… how do you check on what queries are consuming your CPU? There are a couple of dynamic management views (DMVs) that can help with this:


  • sys.dm_os_schedulers - allows us to see the work queues and how the current # of tasks compares to the current # of workers
  • sys.dm_exec_query_stats - allows us to see CPU utilization much like sysprocesses does in SQL Server 2000. Also allows us to see the # of recompiles. A lot of recompiles is an indication that we need to look at the query.
  • sys.dm_exec_query_plan - with this we gain visibility into the query plan itself. Key things to look for are the hash match and sort operators as well as when we see parallelism. Parallelism tends to be dead give away that the query plan is expensive and uses up CPU.


Example #3: Monitoring Index Usage

The question was asked for this example: How do you measure index performance over time? Sure, we can take a trace of activity and run it through the tuning wizard, but are there other options. The short answer is yes. Again, dynamic management views:


  • sys.dm_db_missing_index_details
  • sys.dm_db_missing_index_columns
  • sys.dm_index_usage_stats


The first two allow us to identify cases when we need an index but one isn't present. If we see a lot of hits showing up on a particular column or column(s), then we should consider adding indexes appropriate. The third one allows us to see which indexes aren't being heavily used, if at all. Indexes which get little or no use will show up in the third DMV. If we don't see usage stats increasing for a particular index, it's a good candidate for elimination.

Example #4: TempDB Growth

This is one of those scenarios which creeps up on us. SQL Server hasn't been restarted in a while and for whatever reason (whether due to an alert, our own diligence, or pure chance), we look at the size of tempdb and gasp at how it has grown. How do we diagnose this? Again, the presenters pointed back to DMVs. Basically, looking at the query plans, especially for hash joins. Those tend to cause the TempDB database to grow in size.

All in all a good session.

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.


No comments.

Leave a Comment

Please register or log in to leave a comment.