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.