SQL Server 2005 Performance Troubleshooting – Introduction

I have been hanging out on the MSDN SQL Server Database Engine forum lately, answering questions. I have seen lots of questions along the lines of "My SQL Server is running slow. What could be wrong?"  These types of questions are very difficult to answer without some more details about what seems to be happening, what symptoms are being seen, etc.

I know it can be very difficult to stay calm and work the problem when there is a production database performance issue, and you have people on the verge of panic because your web site is sluggish or even down, but you need to try to eliminate those worries and concentrate on the troubleshooting and information gathering process.

I am planning on writing a series of posts of how to go about troubleshooting common SQL Server 2005 Performance issues, assuming little or no SQL Server 2005 experience or expertise.

In an ideal world, a DBA (or someone playing a DBA in an emergency) is familiar with the hardware and operating system configuration on the machine where SQL Server 2005 is running, is familiar with how SQL Server 2005 is configured on that machine, has some baseline metrics about how the database runs during normal conditions and has some familiarity with the workload characteristics.

What if you are not quite so well prepared, and now you are "under the gun" to fix the problem as soon as possible?  Whether you are trying to solve the problem yourself or reaching out to others for assistance, you need to start gathering information to attempt to narrow down what the issue might be. I try to start out by remembering this hierarchy: Machine, Instance, Database, Query.

Machine

What kind of hardware is SQL Server 2005 running on?

What brand and model server do you have? You can run msinfo32.exe from a Run prompt to gather some of this basic information, including make, model, OS version, amount of physical RAM.

What type and how many processors do you have? You can run CPU-Z to get detailed information about your processors, L2/L3 cache and memory.

How much RAM do you have? Both msinfo and CPU-Z will tell you this.

What kind of I/O subsystem do you have and how is it configured?  Hopefully, you have a SAN or Systems Engineer who can tell you this information. This is a critically important subject, especially for larger, high volume SQL installations. Generally speaking, you want your data files, log files and TempDB to be on different volumes, and you want to be on RAID 10 rather than RAID 5.

What exact operating system and version of SQL Server are you running?

What Operating System? System Properties or msinfo32 will tell you the operating system information. Hopefully, you see something better than what is on my ancient teaching laptop (like you see below). As of this writing, the best OS version to be on is Windows Server 2003 R2, then Windows Server 2003 SP1, then Windows 2000 Server.

What version and build of SQL Server do you have?  Run SELECT @@VERSION from a query window to determine this. Here is an example of what you might see: Microsoft SQL Server 2005 – 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Instance

What are the current high level performance measurements for the server?

What does Task Manager show you? The Performance tab gives you a quick snapshot of the CPU load, memory load, and page file usage and history. Unfortunately, if you are using AWE or have given the SQL Server Service account the "Lock Pages in Memory" right, the memory information in Task manger can be less than helpful.

 

The Processes tab can give you more information. What process(s) are using the most CPU and RAM?

What metrics do you see in Performance Monitor? How do these compare with historical or baseline measurements? In my next post, I will list some key Performance Monitor counters that you should measure, and give you some guidelines for how to interpret them.

Technorati Tag: SQL Server

This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s