http://www.sqlservercentral.com/blogs/brian_kelley/2006/06/12/teched-dattlc01-troubleshooting-performance-problems-in-sql-server-2005/

Printed 2014/12/21 08:45PM

TechEd - DATTLC01 : Troubleshooting Performance Problems in SQL Server 2005

2006/06/12

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:

 

 

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:

 

 

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:

 

 

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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.