Fourth chapter--SQLSERVER2008-2012 Resources and performance monitoring (1)

Original: Fourth chapter--SQLSERVER2008-2012 Resources and performance monitoring (1)

The first step in performance optimization is to identify problems, and there are usually two types of problems: detection of sudden problems and detection of routine problems, and detection of conventional problems, usually with a long-term performance monitoring as the basis.

This series of articles describes

• Server performance Monitoring
• CPU Usage Monitoring
• Memory Utilization Monitoring

Objective:

You can use execution plans,Dmvs/dmfs,SQL Trace , or Database engine advisor (DTA ) When performance issues are caused by poor-quality query writing, missing necessary indexes, or other database-level conditions These means to find the root cause of the problem.

However, if performance problems occur at the hardware or operating system level (such as cpu/ memory /io/ network problems, etc.), use some sophisticated tools to help you find performance issues. Because these processes run at the operating system level.

For new operating systems such as Windows Server 2008R2,windows 7 , you can use one called Performance Monitor (Performancemonitor), Also known as Reliability and Performance Monitor (Reliability and perfomanceMonitor) for more effective monitoring.

In Windows , there are three tools that you can use to monitor performance:

1. Resource Monitor (ResourceMonitor)

2. Performance Monitor (perfomanceMonitor)

3. Reliability Monitor (ReliabilityMonitor)

Resource Monitor provides a fast, real-time, and graphical interface to display information that includes CPU, memory,I/O , and network usage, and can monitor and inspect this part of all processes currently running on the machine. And you can use this tool to end processes that you think are potentially risky for performance.

Performance Monitor provides a graphical interface tool for real-time performance monitoring that enables you to track performance data using hundreds of performance counters. and store the data in a file for further analysis.

The Reliability Monitor also provides a graphical interface to analyze the stability of the system by computing the stability index over a period of time. Any problems will degrade these system stability indexes.

These three tools are common to one interface-the Microsoft Management Console (MMC), which can be viewed together. The reliability and Performance Monitor synthesizes all the features of these three tools.

In this series of articles, you will be familiar with Resource monitor, reliability, and Performance Monitor, and demonstrate how to use these tools to examine and monitor the performance of your hardware resources.

Monitor server performance

What tools will be used to quickly see server performance when users respond very slowly to server servers?

In the past, it might have been done using Task Manager, but today you can use more powerful tools to view server resources. That is, Resource Monitor.

This article will give you a quick primer on how to use Resource Monitor to monitor hardware resources and server performance.

Preparatory work:

1. Development version or Enterprise Edition of SQLServer2008,.

2. The Resource Monitor that is installed on the Windows operating system. Appears only after Windows Server 2008R2 or Windows7 .

3. Microsoft Sample Database AdventureWorks.

Demonstration steps:

1. Open Resource Monitor by entering Resmon.exe in Run or windows+r with shortcut keys.

2. you can see that the Resource Monitor has 5 options pages. The first one is the overview page. You can view the overall performance of the server.

3. If you want to see the CPU used by the SQL Server Service, you can click on the second option page "CPU" and find the line with the process name:sqlservr.exe . After clicking on the first window, the following window will display the corresponding resources.

4. If you want to see the memory used by SQL Server , you can use the method in step 3 to view it in memory on the third option page .

5. If you want to check the active disk I/Oon the server, you can select the option page "Disk" because the sqlservr.exe process has been selected earlier, so this page will display the SQL Server The I/O activity caused by the service. You can connect to SSMS and enter the following script:

Use Adventureworksgoselect  *from    sales.salesorderdetail with (NOLOCK) GO

6. switch back to Resource Monitor now. You will see that I/O operations work on the AdventureWorks database file,

Analysis:

This article visually demonstrates the use of Resource Monitor, which provides CPU, memory, disk IO, and network resource usage information, which is divided into 5 parts to show the relevant information:

1. Overview: provides information about the server resources that are included in the other 4 options pages. When you find a process that produces a performance problem, you can end it from here.

2. CPU: This option page provides a percentage of CPU usage information and also displays detailed CPU information for each process shown in the overview page. And you can see the service of the corresponding process, the associated handle and the associated module. On the right, you can also see the real-time situation of all available CPUs that have been graphically tested. If you find that CPU usage is very high, you should check what is using these CPU resources.

3. Memory: This option page shows memory usage. You can find out how much memory the system has, how much memory is already in use, and how much memory is idle. The memory consumption of each process is also provided. The biggest advantage of resource monitor is that you can get the appropriate resource information by ticking a specific process.

4. Disk: This option page shows the disk I/O activity corresponding to each process, and through the read (b/sec) and Write (b/sec) columns, you can see the IO situation for a particular process. You can also see the IO activity on some files. The right side shows the real-time situation of the current IO .

5. Network: Displays the current network usage. You can see the different processes that correspond to all TCP connections, or you can filter specific processes.

Extended information:

By using Resource Monitor, you can quickly locate processes that consume hardware resources. You can also view bottlenecks in your network. In a SQL Server production environment, if you find that some processes, applications consume more resources than SQL Server, and have affected the performance of SQL Server, you may consider moving these frequently or applications to other servers. So that SQL Server performance does not get too much impact.

Fourth chapter--SQLSERVER2008-2012 Resources and performance monitoring (1)