Performance issues can occur at any time and can be caused by many things. Some of the most common causes are as follows:
• System resource contention • Application design problems
• Queries or procedures that have long execution times • Blocking
Performance issues normally occur unexpectedly, but sometimes they can be predicted. A disk or memory failure, for example, is difficult to predict and is frequently detected after the problem arises. However, a performance issue that is caused by a missing index can be detected proactively, by monitoring the system resources and the database activity. Often, the monitoring information will show a specific trend. In the beginning, when there is little data in the system, the issue will not be visible in the counters. As more data is added to the database, one or more counters will start behaving differently.
Troubleshooting performance issues involves the use of a series of steps to isolate and determine the cause. Identifying these causes is typically very time- consuming, and you may spend several days evaluating the collected
information. If there are performance issues, you can use the following troubleshooting scenario:
2. Check the hardware performance. 3. Check the database design.
When you are trying to identify any bottlenecks that exist in an installation, we recommend that you start by checking the hardware that the installation is running on. The first thing to verify is whether the hardware meets the system and configuration requirements. Identify which RAID level is used. And are you using separate disks for database and transaction log files (or is everything placed on the same disk)?
If the configuration seems correct, you should start to look at the performance of the hardware. For example, does the server have sufficient memory to serve all requests, or is the server constantly paging because of a memory shortage, causing excessive disk I/O and CPU usage?
Finally, you can analyze the queries that are running on SQL Server. By monitoring the activity inside the database, you can see which queries cause the performance issue. Afterward, you can take the steps that are required to solve the problem.
Performance problems that are related to specific tasks should always be tested in the test environment, when no other users are logged on to the database server. This helps you determine whether the performance problem is related to the task itself, or if the problem only occurs when the task is executed in combination with other tasks on the same server.
Deadlocks occur when concurrent transactions try to lock the same resources but do not lock them in the same order. This can be either solved by always using the same locking order or by using a "locking semaphore" that will prevent these transactions from running concurrently.
To monitor the SQL Server installation, you can use several tools that will be described in the next sections.
System Monitor
You can use System Monitor to obtain comprehensive information about your computer and about instances of SQL Server running on your computer. You can then use this information to diagnose performance issues and identify bottlenecks in the system. In this lesson, you learn how to use the System Monitor tool to collect and view real-time or logged data for memory, disk, processor and SQL Server activity.
With Performance Logs and Alerts, you can collect performance data
automatically from local or remote computers. You can view logged counter data using System Monitor or import the data into spreadsheet programs or databases for analysis and report generation.
System Monitor
System Monitor is an MMC snap-in that can be used to view system performance metrics such as processor and memory utilization or disk activity statistics. You can view System Monitor by starting the Performance application in the Administrative Tools program group. Alternatively, you can click Start, and then Run in the Windows Taskbar and enter perfmon.exe in the Run window.
System Monitor categorizes information into objects, counters and instances. Understanding how objects, counters and instances are related to one another is critical to using System Monitor effectively.
• Objects - in System Monitor, objects are major components or subsystems of the computer system. Objects can be hardware (for example, a hard disk), software (for example, a process), or applications (for example, an instance of SQL Server). There are a fixed number of objects in Windows Server 2003, and installing SQL Server adds more objects specific to SQL Server to the list.
• Counters - counters collect data about different aspects of objects. For example, for the Process object, counters collect data on the % processor time and the user time. Counters are built into the operating system and continually read performance data, whether it is visible in System Monitor or not. If an object has multiple
instances, counters track statistics for each instance or for the total of all instances.
• Instances - instances are multiples of the same object type. For example, if a system has multiple processors, the Processor object type will have multiple instances. When you view performance information in System Monitor, you can choose to view the values for an individual instance of an object (for example, the utilization of a single processor) or the combined values for all instances (for example, the overall processor utilization across all processors in the system).
In System Monitor, you can specify which counters are to be displayed. You can display the counter values as a graph, a histogram (bar chart), or a report. Graphs, histograms and reports can be viewed in a browser and printed when
performance data is saved as an HTML file. Reports can be exported into a spreadsheet, such as Microsoft® Office Excel® for further analysis.
Performance Logs and Alerts
In addition to the System Monitor MMC snap-in, the Performance tool includes a second snap-in named Performance Logs and Alerts. You can use this snap-in to capture performance information to a log file for later viewing in System Monitor. There are two types of log files: counter logs and trace logs. Counter logs record data about hardware resources and system resources based on
Log files provide useful information for troubleshooting and planning. While charts, alerts, and reports on current activity provide instant feedback, log files enable you to track counters over a long period of time. Thus, you can examine information more thoroughly and document system performance.
You can configure alerts that fire when a counter reaches a specific threshold value. Alerts are useful if you are not actively monitoring a particular counter but want to be notified when it exceeds or falls below a specified value so that you can investigate and determine the cause of the change. For example, you can set an alert to fire when the percentage of disk space used exceeds 80 percent or when the number of failed logon attempts exceeds a specific number.
NOTE: Alerts in System Monitor, though they perform a similar function, are not related to alerts in SQL Server.
For more information about how to create and configuring alerts in Windows Server 2003, see Microsoft Knowledge Base article 324752 How to Create and Configure Performance Monitor Alerts in Windows Server 2003
(http://go.microsoft.com/fwlink/?linkid=3052&kbid=324752).
NOTE: The alert functionality depends on the Windows Server 2003 Messenger Service, the Windows Server 2003 Alerter Service, and the existence of the recipient account registration in the Windows Internet Name Service (WINS). The Messenger and Alerter services are disabled by default and must be enabled and started to allow network messages to be transmitted.