 |
|
|
Home > Detect performance bottlenecks with these tools |
|
|
|
|
|
|
Detect performance bottlenecks with these tools |
|
| 26 Jul 2007 | Hilary Cotter |
|
|
Bottleneck detection tools
The four tools to use for bottleneck detection are Task Manager, System Monitor, SQL Profiler and DMVs:
Task Manager
You can view Task Manager by hitting CTRL-ALT-DEL and selecting the performance tab. This applet allows you a quick glance at a two-minute window into page file and CPU usage, as well as other counters that update each second. It is useful for getting an instantaneous window into CPU utilization and the amount of paging and memory usage. The process tab lets you to see how many resources each process is consuming. Use the select columns option to display more counters for each process. Keep in mind if you are using AWE, the SQL Server process Mem Usage counter will not be accurate.
System Monitor
System Monitor (the tool formerly known as Performance Monitor) allows you to display and collect information on a wide variety of OS and SQL Server counters. Use the add button to add the counters you wish to collect. When setting up a baseline, you will need to log these counters. Expand Performance Logs and Alerts and select New Log Settings. Add the counters you want to log. The following are some essential counters:
System Monitor: processor object, processor queue length, % processor time counter (>80% indicates a processor bottleneck)
Physical disk object, % disk time (>50% indicates a disk bottleneck)
Physical disk object: avg. disk queue length (>2 indicates a disk bottleneck, but these need to be adjusted for your RAID array)
Physical disk object: avg. disk sec/read or avg. disk sec/write >10-20 ms
Physical disk object: avg. disk reads/sec or avg. disk writes/sec >85% of disk capacity
Process object: working set, private bytes
Memory object: available kbytes, system cache resident bytes, committed bytes, commit limit
SQL Server: Buffer manager object, buffer cache hit ratio, page life expectancy, checkpoint pages/sec, lazy writes/sec
Network interface object: bytes total/sec, current bandwidth, output queue length, counter (further note that network bottlenecks will most likely not manifest themselves in queue lengths. Rather, network transmission times can add latency to every packet. SQL operations from a remote client to a SQL Server can be very slow when compared to SQL operations to a local client)
Note that you have to adjust the Disk Counters for RAID as follows:
- Raid 0: I/Os per disk = (reads + writes) / number of disks.
- Raid 1: I/Os per disk = [reads + (2 * writes)] / 2.
- Raid 5: I/Os per disk = [reads + (4 * writes)] / number of disks.
- Raid 10: I/Os per disk = [reads + (2 * writes)] / number of disks.
- Further note that network bottlenecks will most likely not manifest themselves slow response times between a client and server as the network hop adds latency to each packet traveling between the two
SQL Profiler
SQL Profiler can capture all T-SQL activity on your SQL Server and log that data into a file or even a database table. Use the default template to capture the SQL statement being executed, CPU, reads, writes, and duration. In SQL Server 2005, you can add the blocked process report, which can be found in the Errors and Warnings Events (use the default template, select the Events Selection tab, select Show all events, expand Errors and Warnings and check blocked process report). In SQL 2000, you can get similar functionality using the trace flags 1204.
Dynamic Management Views (DMVs)
SQL Server 2005 provides a set of dynamic management views and functions that allow the current state of internal metadata in SQL Server. Because the data these views expose are with events such as memory pressure or server restarts, to get meaningful results from the DMVs, you need to save the results of these views to tables. Otherwise, you may draw erroneous conclusions based on what may have been transitory events. One of the really valuable features of DMVs is that they can allow you to drill down on particularly resource-intensive statements of stored procedures or batch operations.
| ABOUT THE AUTHOR: |
|
Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.
Copyright 2007 TechTarget
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
