Use Performance Monitor to optimize SQL Server

There are many tools for Performance monitoring. First, we will introduce the Performance Monitor provided by Microsoft Windows Server. windows performance monitor is a very useful tool that can check the ways in which running programs affect computer performance (CPU, ROM, IO, etc.) in real time and collect log data for future analysis and use. through performance monitoring, you can understand the system loading and the impact of such loading on system resources, analyze the change trend of performance or resource usage, and effectively adjust, optimize, or upgrade the system. you can also identify performance bottlenecks by diagnosing system faults or Determining Optimized Components or upgrading steps.

Performance Monitor is a built-in MMC console, which consists of System Monitor and Performance Logs and Alerts. server performance is recorded in real time and logs. you can use the system monitor to view the performance data of various objects such as memory, hard disk, processor, and network in real time. you can use Performance Logs to configure counter logs, record performance data, set performance alarms, and set performance alarms, the system can promptly notify the system administrator when a specific counter value is lower than or higher than the specified value.

The Performance Monitor in Windows Server 2003 is briefly introduced below. Performance data is recorded through Logs and then analyzed.

1. Open: Administrative Tools-> Performance,

Or SQL Server Profiler-> Tools-> Performance Monitor, or enter "perfmon"

2. Important performance counters

(1). Processor

(2). PhysicalDisk

(3). Memory

(4). Network Interface

(5). SQL Server Access Methods

(6). SQL Server: SQL Statistics

(7). SQL Server: Databases

(8). SQL Server General Statistics

(9). SQL Server Locks

(10). SQL Server Buffer Manager

The following table provides a brief description of important performance counters:
 

Performance counters:
Performance Object	Counter	Description
Processor	% Processor Time	The percentage of time when the processor executes non-Idle threads. This counter measures the time when the processor is busy. It is designed as the main indicator for processor activity. You can select a single CPU instance or Total.
	Interrupts/sec	Number of application or hardware interruptions being processed by the processor
PhysicalDisk	% Disk Time	The percentage of Time that the counter uses to monitor Disk busy reading/writing activities. In the system monitor, PhysicalDisk: % Disk Time counter monitors the percentage of Time that the Disk is busy reading/writing activities. If the PhysicalDisk: % Disk Time counter value is greater than 90%), check the PhysicalDisk: Current Disk Queue Length counter to learn the number of system requests waiting for Disk access. The number of pending I/O requests should be no more than 1.5 to 2 times the number of axes that constitute the physical disk. Most disks have only one axis, but independent Disk redundancy arrays (RAID) usually have multiple axes. The hardware RAID device is displayed as a physical disk in the system monitor. Multiple RAID devices created using the software are displayed in the system monitor as multiple instances. You can use the Current Disk Queue Length and % Disk Time counter values to detect bottlenecks in the Disk subsystem. If the Current Disk Queue Length and % Disk Time counter values remain high, consider the following: 1. Use a faster Disk drive. 2. Move some files to another disk or server. 3. If a RAID array is being used, add a disk to the array. The percentage of Time that the counter uses to monitor Disk busy reading/writing activities. In the system monitor, PhysicalDisk: % Disk Time counter monitors the percentage of Time that the Disk is busy reading/writing activities. If the PhysicalDisk: % Disk Time counter value is greater than 90%), check the PhysicalDisk: Current Disk Queue Length counter to learn about the pending magnetic Number of system requests for disk access. The number of pending I/O requests should be no more than 1.5 to 2 times the number of axes that constitute the physical disk. Most disks have only one axis, but independent Disk redundancy Arrays A (RAID) device usually has multiple axes. The hardware RAID device is displayed as a physical disk in the system monitor. Multiple RAID devices created using the software are displayed in the system monitor as multiple instances. You can use the Current Disk Queue Length and % Disk Time counter values to detect bottlenecks in the Disk subsystem. If the Current Disk Queue Length and % Disk Time counter have a high value, consider the following: 1. Use a faster disk drive. 2. Move some files to another disk or server. 3. If a RAID array is being used, add a disk to the array.
	Avg. Disk Queue Length	The average number of Read and Write requests (queued for the selected disk at the instance interval)
	Current Disk Queue Length	Indicates the number of disk I/O requests to be suspended. If this value is always higher than 2, it indicates congestion.
	Avg. Disk Bytes/Transfer	Average number of bytes transferred to or from the disk during write or read Operations
	Disk Bytes/sec	The Byte rate transmitted from or to the disk during read/write operations.
Memory	Pages/sec	Number of requested pages.
	Available Bytes	Number of available physical memory
	Committed Bytes	Virtual Memory allocated to physical RAM for storing or allocating to page files
	Pool Nonpaged Bytes	Number of RAM in the system memory area of the non-Paging pool
	Page Faults/sec	Is the average number of error pages per second
Network Interface	Bytes encoded ED/sec	Number of bytes received using the network adapter
	Bytes Sent/sec	Number of bytes sent using the network adapter
	Bytes Total/sec	Number of bytes sent and received using the network adapter
Server	Bytes encoded ED/sec	Compare this counter with the total bandwidth of the network adapter to determine whether the network connection has a bottleneck.
SQL Server Access Methods	Page Splits/sec	Index page overflow occurs every second. if the page is split frequently, increase the Index filling factor. data Pages will have more space to retain for data filling, thus reducing page splitting.
	Pages Allocated/sec	The number of pages allocated per second in all databases of this SQL Server instance. These pages include pages allocated from the hybrid and unified zones
	Full Scans/sec	Unlimited number of full scans per second. These scans can be base table scans or full-text index scans.
SQL Server: SQL Statistics	Batch Requests/Sec	The number of batches of Transact-SQL commands received per second. This statistical information is affected by all constraints, such as I/O, number of users, cache size, and request complexity. The high number of batch processing requests means the throughput.
	SQL Compilations/Sec	The number of compilations per second. Indicates the number of times the compiled code path is entered. Including statement-level re-compilation in SQL Server. When SQL Server user activity is stable, The value is in the stable state.
	Re-Compilations/Sec	The number of times the statement is recompiled per second. Calculates the number of times a statement is re-compiled. In general, this number should be smaller, and the stored procedure should be compiled only once in an ideal situation, The execution plan is reused. If the counter value is high, you may need to write the stored procedure in another way to reduce the number of recompilation times.
SQL Server: Databases	Log Flushes/sec	Number of log refreshes per second
	Active Transactions	Number of active transactions in the database
	Backup/Restore Throughput/sec	Read/write throughput of database backup and restoration operations per second. For example, when multiple backup devices are used in parallel or faster devices are used, changes in database backup performance can be measured. The throughput of database backup or restoration operations can determine the process and performance of backup and restoration operations
SQL Server General Statistics	User Connections	Number of active SQL connections in the system. The counter information can be used to identify the maximum number of concurrent users in the system
	Temp Tables Creation Rate	Number of temporary tables/table variables created per second
	Temp Tables For Destruction	Number of temporary tables/table variables waiting for system thread destruction to be cleared
SQL Server Locks	Number of Deadlocks/sec	Number of lock requests that cause deadlocks per second. deadlocks are harmful to application scalability and lead to poor user experience. The counter must be 0.
	Average Wait Time (MS)	Average wait time of each lock request that causes waiting
	Lock requests/sec	Number of new locks and lock conversions requested by the lock manager per second. The number of reads can be reduced by optimizing the query to reduce the counter value.
SQL Server: Memory Manager	Total Server Memory (KB)	Memory submitted from the buffer pool (this is not the total memory used by SQL Server)
	Target Server Memory (KB)	Total amount of dynamic memory available for the server
	SQL Cache Memory (KB)	Total number of dynamic memory that the server is using for dynamic SQL high-speed cache
	Memory Grants Pending	Number of processes waiting for Workspace Memory authorization per second. This counter should be as close to 0 as possible, otherwise it may indicate a memory bottleneck
SQL Server Buffer Manager	Buffer Cache Hit Ratio	Cache hit rate, which indicates the percentage of pages in the buffer cache that do not need to be read from the disk (physical I/O. if this value is low, there may be insufficient memory or incorrect indexes.
	Page Reads/sec	Number of physical database page reads per second. This statistics shows the total number of physical page reads between all databases. Because of the high overhead of physical I/O, you can use more data caching, smart indexing, more effective queries, or change the database design to minimize the overhead.
	Page Writes/sec	Number of physical database page writes per second
	Page Life Expectancy	Number of seconds the page will stay in the buffer pool if it is not referenced
	Lazy Writes/Sec	Number of buffers written by the buffer manager's inert writer per second
	Checkpoint Pages/Sec	The number of pages per second refreshed to the disk by the checkpoint or other operations that require refreshing all dirty pages

Tip:

When monitoring Windows Server or SQL Server to investigate performance-related issues, you should first focus on three aspects of hardware:

(1) CPU (CPU usage)

(2) RAM (memory usage)

(3) HDD (Disk activity is I/O)

3. Establish monitoring

Next we will establish monitoring (the HP Server I monitored is configured as Intel 4x4x3.0 GHz/RAM 16.0G, and the Business System is OLTP ).

(1) In performance-> Performance Logs and Alerts-> New Log Setting...

(2) General Tab-> Add Counters to be monitored (refer to the counter list above)

(3) General Tab-> Interval, set the monitoring Interval (15 s by default)

(4) Log Files Tab-> Log file type, select the Log File storage method (text File, Binary File, SQL Database), here I select text File (Tab delimited ).

(5) run the Schedule Tab to set the monitoring start time and end time.

4. Analysis (time period for testing and monitoring ))

After monitoring for a period of time, you will get important performance counters of the Server, and then you can analyze the performance of the Server. I am using the data perspective, it looks more intuitive.

4.1 CPU usage. Analysis % Processor Time (_ Total) (percentage of Time in use, Time in horizontal axis, % Processor Time in vertical axis)

For example, during the period of-and 2010/7/7-, the CPU usage was usually above 50%. especially at-,-CPU is very busy, during this time there will be a large number of transactions need to be processed (T-SQL query, SP, background job, User operations and so on ).

If the CPU usage remains high (from 80% to 90%), you must consider upgrading the CPU and adding more processors or system tuning. (We recommend that you first perform system tuning, hardware upgrade requires additional costs ).

4.2 Disk I/O (% Disk Time, percentage of Time the Disk is busy with reading/writing activities)

Monitoring disk activity involves two main aspects:

(1) monitor disk I/O and check for excessive page feed

(2) Isolate disk activities generated by SQL Server

From the data perspective, disk I/O reads and writes are very idle, only around, (not captured on the figure) there will be a large IO.

If the disk I/O is very high (> 90%), consider replacing the fast disk (such as SSD ).

Refer to the solutions provided by Microsoft:

Monitor disk I/O and detect excessive page feed

You can monitor the following two counters to determine disk activity:

• PhysicalDisk: % Disk Time
• PhysicalDisk: Avg. Disk Queue Length

In the system monitor, PhysicalDisk: % Disk Time counter monitors the percentage of Time the Disk is busy with read/write activities. If the PhysicalDisk: % Disk Time counter value is greater than 90%), check the PhysicalDisk: Current Disk Queue Length counter to learn the number of system requests waiting for Disk access. The number of pending I/O requests should be no more than 1.5 to 2 times the number of axes that constitute the physical disk. Most disks have only one axis, but independent Disk redundancy arrays (RAID) devices usually have multiple axes. The hardware RAID device is displayed as a physical disk in the system monitor. Multiple RAID devices created using the software are displayed in the system monitor as multiple instances.

You can use the Current Disk Queue Length and % Disk Time counter values to detect bottlenecks in the Disk subsystem. If the Current Disk Queue Length and % Disk Time counter have a high value, consider the following:

• Use a faster disk drive.
• Move some files to another disk or server.
• If you are using a RAID array, add the disk to the array.

If a RAID device is used, the % Disk Time counter indicates a value greater than 100%. In this case, use the PhysicalDisk: Avg. Disk Queue Length counter to determine the average number of system requests waiting for Disk access.

Applications or systems on which I/O depends may keep the disk active.

Monitoring Memory: Page Faults/sec counters ensures that disk activity is not caused by pages. In Windows, the reason for page feed includes:

• The configuration process occupies too much memory.
• File System activity.

If multiple Logical partitions exist on the same hard Disk, use the Logical Disk counter instead of the Physical Disk counter. Viewing Logical Disk counters helps you determine which files are frequently accessed. When you find that a Disk has a large number of read/Write activities, check the read/Write counters to determine the type of Disk activity that causes an increase in the load on each Logical volume, for example, Logical Disk: Disk Write Bytes/sec.

Isolate disk activities generated by SQL Server

The following two counters can be monitored to determine the I/O activity volume generated by the SQL Server Component:

• SQL Server: Buffer Manager: Page reads/sec
• SQL Server: Buffer Manager: Page writes/sec

In the system monitor, these counters monitor the I/O activity volume generated by the SQL Server component by checking the performance of the following operations.

• Write pages to disk
• Read page from disk

If the value of these counters reaches the capacity limit of the hardware I/O subsystem, You need to reduce these values, the method is to adjust the application or database to reduce I/O operations such as index coverage, index optimization or normalization), increase the hardware I/O capacity or add memory

4.3 Buffer Cache Hit rate (Buffer Cache Hit Ratio)

Based on the detected data, the cache hit rate is basically between 99.99% and 100%, indicating that the data cache meets almost all data requests.

4.4 Page splitting (Page Splits/sec, Page splitting scores per second due to index Page benefits)

If the page is split frequently, you can consider adding a fill factor (I set the Index fill factor to 85, that is, each page will have 15% space for data filling ).

According to my detection, there may be large page splitting only in a few time periods, and there may be a lot of data transaction operations at this time. In general, the performance is good.

4.5 Number of Log refreshes per second (Log Flushes/sec)

Log refresh occurs when transaction is submitted and data is written to the disk log file from the log cache. Log refresh should be minimized.

If a high value is detected, transaction is very active and the number of transactions is reduced.

Here is a simple example:

For example, to Insert data records into a Table

Method 1: Insert one by one and transaction one by one. log flushes will be generated.

Practice 2: pieces of data are inserted in one transaction. Only one log flushes is generated.

Obviously, the log refresh generated in the second method will be greatly reduced, and the corresponding disk I/O will be greatly reduced, which will help improve performance.

Summary:

(1) there are still a lot of log records that have not been analyzed in a simple way.

(2) Performance Monitor only provides a method to help identify problems and provide a direction for Performance optimization. Once the problem affecting Performance is found, you can proceed from this direction.

(3) There are a lot of performance testing tools on the Internet. It is better to encapsulate the above work, and the UI has been analyzed and more intuitive.

(4). If anything is incorrect, please correct it. Thank you!

In addition:

Reference: http://www.sql-server-performance.com/tips/ SQL _server_performance_monitor_coutners_p1.aspx

Microsoft counter more detailed description: http://technet.microsoft.com/en-us/library/ms190382.aspx

Title: SQL Server Performance tuning series (2) -- Server Performance Monitor (Perfmon)

Link: http://www.cnblogs.com/changbluesky/archive/2010/07/12/1771210.html