Troubleshooting the Performance of a SQL Server Solution

Troubleshooting the performance problems of a SQL Server 2005 solution can be a time-consuming process. When deciding on a troubleshooting performance, you can basically take two approaches:

• Proactive approach   With a proactive approach, you typically monitor your SQL Server solution on a regular basis and try to preempt performance issues when you notice that your database solution is reaching some resource or capacity limit.

• Reactive approach   With a reactive approach, you respond to a certain event, such as users complaining about poor query performance or end-of-day batch processes taking too long.

In most cases, from a pragmatic point of view, the workweek has only 40 hours (at least in Australia), so most DBAs will adopt the reactive approach. However, more experienced DBAs generally tend to recognize the value of setting up some sort of proactive approach and reaping the benefit: time.

A SQL Server 2005 solution is a complex client/server architecture. It really is not a simple system like Active Directory, Exchange Server, or the Windows operating system. Consequently, the DBA should have some knowledge of the different layers that make up a SQL Server’s client/ server architecture. These layers can be classified as the following:

• Hardware   The hardware resources include components such as the server, memory, nonuniform memory architecture (NUMA), number of processors, and disk subsystem.

• Network   The network resources include your network infrastructure such as your network interface card (NIC), routers, switches, and their configuration.

  Note 

  Don’t forget that the network might also incorporate your Active Directory (AD), virtual private networks (VPN), certificate servers, and other network dependencies.

• Operating system   The DBA should also know as much about the operating system as possible. Yes, you can’t know everything and will need to liaise with your infrastructure personnel. But you will find that some performance issues are related to the underlying version of Windows. Unfortunately, because SQL Server solutions do not get upgraded as often as other servers, you might end up having to be conversant with Windows 2000, Windows 2003, and Windows Vista.

  Note 

  Virtualization technology adds another level of potential complexity when troubleshooting performance problems with SQL Server 2005. What layer does virtualization fall under? Well, it’s a combination of both the hardware and the operating system layers.

• SQL Server database engine   A lot of SQL Server 2005’s configuration parameters are dynamic, which means the database engine automatically adjusts the way it runs on your particular hardware depending on system activity. However, in certain cases you might still need to examine what SQL Server is doing and potentially tune the database engine.

  Note 

  SQL Server 2005 effectively has its own operating system now, called the SQL operating system (SQLOS). Ideally, you should take the time to learn more about the SQL Server architecture and the SQLOS. Unfortunately, we can’t recommend a one-stop shop for finding the information, but if you search the Web, you will find a number of interesting articles and blogs.

• SQL Server components   SQL Server 2005 is more than just a database engine. Other components such as full-text indexes, Notification Services, SQL Server Reporting Services (SSRS), Service Broker, SQL Server Agent, and SQL Server Integration Services (SSIS) might all be part of your database solution and consequently also need to be examined when troubleshooting performance.

• Database   Obviously, the database design and related data issues need to be understood and potentially investigated when troubleshooting performance. But don’t forget the size and layout of the data and log files, file groups, and partitioning.

• Client applications   The way client applications have been developed and the underlying technology they use can have a significant impact on a database solution’s performance.

Note 

As a DBA, it is typically difficult to troubleshoot the client application, although tools such as SQL Server Profiler can help. It is typically sufficient to narrow down your performance problems to the client application, in which case the developers are responsible for diagnosing the problems further.

In this chapter, we will concentrate on tuning the hardware and SQL Server instance. Although we will also introduce troubleshooting database performance problems, we will cover database-related issues in more detail in Chapter 2, “Optimizing the Performance of Queries.”

Methodology for Troubleshooting Performance

You simply can’t jump all over the place looking for what you think the performance problem might be. You need to develop a structured approach for troubleshooting performance problems. Too many things are going on in the client/server architecture. Notice that we said that “You need to develop”; in other words, there is no “official” methodology or process that we could teach you that would apply to all sites and circumstances.

Basically, your troubleshooting methodology should try to narrow down the problem to the correct subsystem of your SQL Server solution. At a higher level, you can break this down into the following categories:

• Physical server

• SQL Server instance

• Database

Obviously, a more detailed methodology is required. As we stressed earlier, no recommended troubleshooting performance methodology exists. But we need to give you a basis for one, so a generic methodology for troubleshooting performance problems would be similar to this:

1. Talk to the user who has notified you of the performance problem, and document the user’s “experience.”

2. Determine whether it is a hardware problem. This is typically done by going through the following questions:

   1. Do your hardware vendor tools indicate a processor problem?

   2. Do your hardware vendor tools indicate a memory problem?

   3. Do your hardware vendor tools indicate a disk array problem?

   4. Do you hardware vendor tools indicate a NIC problem?

   5. Do your network support personnel know of any network infrastructure issues, or have there been any recent changes?

3. Determine whether it is an operating system or a SQL Server 2005–related problem through the appropriate tools. If it is an operating system problem, ask these questions:

   1. Is it a processor bottleneck?

   2. Is it a memory bottleneck?

   3. Is it an input/output (I/O) bottleneck?

4. For a SQL Server 2005–related problem, is it at the database engine level? If it is a SQL Server database engine problem, ask these questions:

   1. Is it a processor bottleneck?

   2. Is it a memory bottleneck?

   3. Is it an I/O bottleneck?

5. Determine whether it is a database-related problem:

   1. Is it a concurrency-related problem?

   2. Is it an index-related problem?

   3. Is it a query-related problem?

The important point is to have a structured approach.

Real World Scenario-The Benefits of a Performance Troubleshooting Methodology

A couple of years ago, I was responsible for rearchitecting a database solution in the coal industry. This particular database solution included a web portal that was used internationally to monitor the coal market. After three months of the system going live, performance ground to a halt, to the point of it being unusable. It was quite embarrassing for the company, which was using the latest hardware and had spent a lot of resources on the project.

The problem, as it turned out, was neither the SQL Server database engine nor the hardware. In this case, the culprit was the web application. The web developers had decided to log every click on the website to a set of auditing tables. Well, after three months, these auditing tables, which had a number of poor indexes, were so massive that they degraded the performance of the entire SQL Server solution.

I was able to determine this bottleneck only by applying a systematic approach to troubleshooting performance. Otherwise, it would have been just a hunt-and-peck exercise-a guessing game, in order words-which is unacceptable for any organization that relies on SQL Server for mission-critical systems.

-Victor Isakov

Tools for Troubleshooting Performance

SQL Server 2005 and the Windows operating systems come with a complete set of tools for monitoring, troubleshooting, and tuning performance. Your choice of a particular tool depends on your particular needs. Sometimes you’ll require a combination of tools for the task at hand.

When determining what tool is appropriate, you should take a number of factors into account, including whether the tool offers trend analysis capabilities, the possibility of replaying captured events, whether you can use it for ad hoc monitoring, any alert generation options, the presence of a graphical interface, and the option of using the tool within a custom application.

Note 

Take the time necessary to learn both the capabilities of the tool and how to use the tool. It is so common to see IT professionals in the field using such tools inefficiently or doing tasks the harder way because they are not aware of certain feature. Also, don’t assume that tools in the current version of SQL Server have the same features as the previous version, because Microsoft is continually improving the tools.

Windows Tools

The tools that come with the Windows operating system have not dramatically changed since Windows NT first came out in the early 1990s, although they have been enhanced through subsequent releases. The most commonly used performance troubleshooting tools include the following:

• Task Manager   This displays information about programs and processes running on the server (or desktop computer). You can use it to get an immediate overview of your server’s performance.

• System Monitor   System Monitor (perfmon.exe) is used to track resource usage on Windows operating systems through a set of installed performance object counters.

• Performance Logs and Alerts   The Performance Logs and Alerts tool allows you to collect performance data automatically from local or remote computers.

• Network Monitor Agent   This detects network problems and identifies network traffic patterns.

The primary tools used by the DBA are the System Monitor and the Performance Logs and Alerts tool, but don’t forget the other tools as well.

System Monitor

The System Monitor allows you to monitor the hardware resources and system services on your server. It allows you to define which performance objects and counters should be monitored. Performance objects can have multiple instances (such as in the case of processes). Figure 1.1 shows an example of the System Monitor running.

Figure 1.1: System Monitor

In case you are not familiar with the terminology associated with System Monitor, let’s go through a quick overview. Performance data generated by a system component is described as a performance object. For example, the SQLServer:Databases object represents a collection of performance data about the databases in your SQL Server 2005 instance. This object has a number of metrics that can be monitored, referred to as performance object counters, such as Active Transactions and Data File(s) Size (KB). Object instances refer to multiple instances of an object such as different databases. Figure 1.2 shows an example of the counters and instances available for the SQLServer:Databases performance object.

Figure 1.2: SQLServer:Databases performance object counters

You can use System Monitor to investigate performance-related issues for SQL Server 2005 and the Windows operating system. You can do this for the local and remote computers.

Don’t forget that monitoring on a local computer can potentially add some performance overhead that you can reduce by using a number of techniques such as monitoring fewer counters or increasing the sample interval. An alternative way of reducing the performance overhead is to monitor from a remote computer. Although this option will add network traffic, it is not usually a problem in most environments.

You can also use System Monitor to view data simultaneously from multiple computers, create charts, or export data from charts. Again, investigate what the tool does firsthand!

Tip 

Generally, you should use charts for real-time, short-term monitoring. For longer periods, use performance logs.

Performance Logs and Alerts

The Performance Logs and Alerts tool allows you to log performance data and generate alerts for specific events. Some of the more important capabilities include the following:

• The performance data are collected automatically, and because logging runs as a service, a user doesn’t need to be logged on.

• Data can be collected in several formats such as comma-separated value (CSV), tab-delimited, binary log file format, and SQL Server table format.

  Tip 

  If you collect the data as CSV or tab-delimited files, you can open them for quick analysis in Microsoft Excel.

  Warning 

  Capturing to a SQL Server table provides a great platform for further manipulating and querying the logged information. However, it is slower than the other methods because of the overhead of all the inserts into the table (because you have to go through the database engine and the data-base’s transaction log). It can slow down your system, so it is generally not recommended.

• You can use the Performance Logs and Alerts tool to view logged counter data.

• You can view collected data not just when the collection is stopped but also during the collection.

• When setting up alerts, you can specify various actions such as sending a message, running a program, or adding an entry to the application log.

Warning 

When writing to log files, such as when using the Performance Logs and Alerts tool, make sure you have sufficient space on the partition to which you are writing the performance data.

SQL Server 2005 Tools

The range of tools and commands available with SQL Server 2005 has grown considerably since the SQL Server 4.21a days, when SQL Server shipped on four floppy disks. So, it’s a matter of getting experience with using the various tools and commands and becoming familiar with their usage and the information they return. Don’t fall into the trap of using only the tool you are familiar with using. With any new release of SQL Server, you should reevaluate all the tools because they might have substantially changed.

SQL Server Profiler

SQL Server Profiler is a graphical tool for using traces. A trace captures event data such as Transact-SQL (T-SQL) statements, the start of a stored procedure executing, a lock being acquired or released on a database object, or security permission checks against objects. You can save the captured data in a file or a table for later analysis. You can also use SQL Server Profiler for monitoring Analysis Services. Another advantage of SQL Server Profiler in SQL Server 2005 is the option to correlate a trace with Windows performance log data. You will learn how to do that in Exercise 1.1 later in this chapter.

Figure 1.3 shows an example of SQL Server Profiler capturing a trace against a SQL Server 2005 instance.

Figure 1.3: SQL Server Profiler

SQL Server Profiler comes with the following predefined trace templates.

SP_COUNTS

The SP_Counts template collects all stored procedures that have been issued. The trace returns the results grouped by the stored procedure name and includes the number of times the stored procedure was executed.

The SP_Counts template captures information for the following event class:

• SP:Starting

STANDARD

The Standard template collects general information about all connections, stored procedures, and T-SQL batches that have been issued. Use the Standard template as a generic trace to monitor general activity.

The Standard template captures information for the following event classes:

• Audit Login

• AuditLogout

• ExistingConnection

• RPC:Completed

• SQL:BatchCompleted

• SQL:BatchStarting

TSQL

The TSQL template collects all T-SQL statements that have been issued and the time they were issued. Use the TSQL template to debug client applications.

The TSQL template captures information for the following event classes:

• Audit Login

• Audit Logout

• ExistingConnection

• RPC:Starting

• SQL:BatchStarting

TSQL_DURATION

The TSQL_Duration template collects all T-SQL statements that have been issued and their exe-cution time (in milliseconds) and groups them by this execution time. Use the TSQL_Duration template to identify slow queries.

The TSQL_Duration template captures information for the following event classes:

• RPC:Completed

• SQL:BatchCompleted

TSQL_GROUPED

The TSQL_Grouped template collects information identical to the TSQL trace but groups that information by the users or client applications that issued the T-SQL statements. Use the TSQL_Grouped template to investigate users or client applications.

The TSQL_Grouped template captures information for the following event classes:

• Audit Login

• Audit Logout

• ExistingConnection

• RPC:Starting

• SQL:BatchStarting

TSQL_REPLAY

The TSQL_Replay template collects detailed information about the T-SQL statements that have been issued so that they can be replayed. Use the TSQL_Replay template for iterative tuning, benchmarking, or unit testing.

The TSQL_Replay template captures information for the following event classes:

• Audit Login

• Audit Logout

• CursorClose

• CursorExecute

• CursorOpen

• CursorPrepare

• CursorUnprepare

• Exec Prepared SQL

• Existing Connection

• Prepare SQL

• RPC:Completed

• RPC:Starting

• RPC Output Parameter

TSQL_SPS

The TSQL_SPs template collects detailed information about the stored procedures calls that have been issued. Use the TSQL_SPs template to analyze the individual statements within the stored procedures.

The TSQL_SPs template captures information for the following event classes:

• Audit Login

• Audit Logout

• ExistingConnection

• RPC:Starting

• SP:Completed

• SP:Starting

• SP:StmtStarting

• SQL:BatchStarting

Tip 

You should add the SP:Recompile event if you suspect that procedures are being recompiled.

TUNING

The Tuning template collects information about T-SQL statements and stored procedures that have been issued for tuning purposes. Use the Tuning template to generate a workload file for the Database Engine Tuning Advisor when tuning your databases.

The Tuning template captures information for the following event classes:

• RPC:Completed

• SP:StmtCompleted

• SQL:BatchCompleted

One of the new features of SQL Server Profiler in SQL Server 2005 is the ability to correlate performance object counter data (recorded with System Monitor or the Performance Logs and Alerts tool) with SQL Server Profiler traces. Exercise 1.1 will show you how.

Exercise 1.1: Correlating a Trace with Windows Performance Log Data

In this exercise, you will create a deadlock and see it reflected in the SQLServer:Lock : Number of Deadlocks/sec counter. First you will set up the performance log and start a trace; after that you will run some simple T-SQL statements to create a deadlock. SQL Server Profiler can be of great help in detecting the cause of a deadlock. However, for this demonstration, you will use it just to record the T-SQL statements that will generate the deadlock.

1. Use the Windows Start menu, and choose All Programs Administrative Tools Performance.

2. Expand Performance Logs and Alerts (in the Windows Performance tool), right-click Counter Logs, and click New Log Settings.

3. Type Deadlock Log as the name for the counter log, and click OK.

4. On the General tab, click Add Counters.

5. In the Performance Object box, select SQLServer:Locks.

6. Add the Number of Deadlocks/sec counter, and leave _Total selected in the list of instances.

   

7. Click Close.

8. Enter 1 as the value for the Interval box under Sample Data Every.

   

9. Click the Log Files tab, and choose Text File (Comma Delimited) from the Log File Type list (so you can share the log file among different versions of Windows or view the log files later with Microsoft Excel).

   

10. On the Schedule tab, specify Manually for both the Start Log and Stop Log options.

11. Click OK to create the performance log.

12. Click the Counter Logs node, right-click Deadlock Log, and select Start from the context menu. Leave the System Monitor console open.

    

13. From the Windows Start menu, choose All Programs Microsoft SQL Server 2005, and click SQL Server Management Studio. For this exercise, leave SQL Server Management Studio open.

14. Connect to your SQL Server, and then from the Tools menu click SQL Server Profiler.

15. In the File menu (of SQL Server Profiler), select New Trace, and connect to your SQL Server.

16. On the General Tab, enter Correlation Trace as the trace name, select the TSQL_Replay template, and check the Save to File box to specify the trace file location and filename. (We used C:\Correlation Trace.trc.) Click Run.

    

17. Switch back to SQL Server Management Studio.

18. Open a new query window, and run the following query:

     USE tempdb ; GO CREATE TABLE Employee (   EmployeeID INT,   EmployeeName VARCHAR(64) ) GO CREATE TABLE Orders (   OrderID INT,   Amount INT ) GO INSERT INTO Employee VALUES (69, 'Angelina Jolie') GO INSERT INTO Orders VALUES (1000, 200) GO

19. Type the following statements in the query window, but do not run the query yet:

     USE tempdb ; GO BEGIN TRAN UPDATE Employee SET EmployeeName = 'Lara Croft' WHERE EmployeeID = 69 WAITFOR DELAY '00:00:10' UPDATE Orders SET Amount = 300 WHERE OrderID = 1000

20. Open a new query window, and type the following statements:

     USE tempdb ; GO BEGIN TRAN UPDATE Orders SET Amount = 350 WHERE OrderID = 1000 WAITFOR DELAY '00:00:10' UPDATE Employee SET EmployeeName = 'Aeon Flux' WHERE EmployeeID = 69

21. Run the query, and then switch to the first query to run it. It should create a deadlock. In one of the query windows you will get an error message “Msg 1205, Level 13, State 45, Line 6 Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”

22. Switch to System Monitor, and stop the Deadlock log. (Click the Counter Logs node, right-click Deadlock Log, and select Stop from the context menu.)

23. Switch to SQL Server Profiler, stop the trace, and then close it.

24. From the File menu of SQL Server Profiler, select Open, and then click Trace File. Open the trace file you just created (C:\Correlation Trace.trc).

25. From the File menu, select Import Performance Data, and select the log file recorded previously (the default path and name are C:\PerfLogs\Deadlock log_000001.csv).

26. In the Performance Counters Limit Dialog window, check the instance name of your server.

    

27. You can play with the pointer both from the trace and from the Performance Data window to get a feel for the tool. You can go directly to the maximum value of the counter (the value 1 in this case) by right-clicking the counter (you have just one counter) and selecting Go to Max Value.

    

SQL Traces

Another way to trace the activity of a SQL Server instance is by using system stored procedures. You can use the following stored procedures to manage a SQL trace:

• fn_trace_geteventinfo   Returns information about events included in a trace

• fn_trace_getfilterinfo   Returns information about filters applied to a trace

• fn_trace_getinfo   Returns information about a specific trace or all traces

• sp_trace_create   Creates a trace definition

• sp_trace_setevent   Adds or removes an event or event column from existing traces

• sp_trace_setfilter   Applies a filter to a trace

• sp_trace_setstatus   Modifies the current state of a trace

• sp_trace_generateevent   Creates a user-defined event

Note 

For more information about how to create traces, search for the “Introducing SQL Trace” topic in SQL Server 2005 Books Online.

Dynamic Management Views and Functions

Dynamic management views (DMVs) are new objects in SQL Server 2005 that expose detailed server state information such as current connections, locks, requests, tasks, and memory allocation. They make SQL Server’s internals more “transparent,” enhancing the information you previously accessed in earlier releases in the form of “virtual tables” such as sysprocesses, system stored procedures, database console commands (DBCCs), dumps, and so forth. DMVs also expose important aggregate statistical data that was not available in previous releases of SQL Server.

Figure 1.4 shows the output of the sys.dm_os_memory_objects DMV.

Figure 1.4: sys.dm_os_memory_objects DMV

Although predominantly envisaged for use by Microsoft Product Support Services (PSS), DMVs provide rich information about the current status of your SQL Server instance. This is the direction of the future, so you should get used to using DMVs instead of older commands. Many DMVs exist, and they cover various components of SQL Server. You’ll note that DMVs use a naming convention that makes them easier to use.

Tip 

The DMVs will change over time. Microsoft introduced new DMVs in SQL Server 2005 Service Packs 1 and 2. So, always make sure you go through the list of new features and improvements included in the SQL Server service packs.

SQL Server 2005 has two types of DMVs: server-scoped DMVs and database-scoped DMVs. We’ll cover them in a short while when you learn about troubleshooting SQL Server instances and databases.

SQL Server Management Studio

SQL Server Management Studio has a number of utilities built into it that also can aid you in troubleshooting performance problems. We will discuss each one as appropriate when we cover troubleshooting SQL Server instances and databases.