Shulou(Shulou.com)06/01 Report--

This article shares with you the content of a sample analysis of Windows Server 2008 database performance monitoring. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The performance graph of the current server CPU, disk, network, and memory is shown side by side in the system resources view. These graphs dynamically reflect the real-time utilization of system resources. At the bottom of this set of graphs, we can expand to see the resource utilization of each process. In previous versions of Windows, process-level real-time performance data was limited and could only be viewed through the Windows task manager.

Like Windows Server 2003, we can turn on the Windows Server 2008 reliability and performance monitor by typing the command line command "perfmon". The default initial interface is the system resources view, as shown in figure 1:

Figure 1 Windows Server 2008 Reliability and performance Monitor system resource view

System data collection group is an important function added by Windows Server 2008 in system performance monitoring. Through it, users can integrate the relevant performance data to collect, so that they can adapt to a variety of different performance monitoring needs. We can also set a schedule for data collection, play back collected performance logs, save collection templates for monitoring other servers, and so on.

As shown in figures 2 and 3, I can create a system data collection group through the wizard, or I can save the current configuration results of the performance monitor as a new system data collection group.

Figure 2 Windows Server 2008 system data Collection Group Wizard

Figure 3 create a new system data collection group based on the current configuration of Windows Server 2008 performance monitor

The Windows Server 2008 Reliability and performance Monitor integrates the diagnostic reporting capabilities of the server performance tuning recommendation tool (in Windows Server 2003, the optimization recommendation tool needs to be downloaded separately). Through the diagnostic report, you can quickly display the log contents of any performance data collection group; through its zoom function, you can easily view the performance curve of any period of time, so as to accurately evaluate the actual impact of optimization measures on performance.

The interface for Windows Server 2008 system diagnostic reports is similar to the system performance monitoring interface for Windows Server 2003 performance monitors. It is worth mentioning that after opening the diagnostic report of Windows Server 2008, we can zoom in on a selected period of time to take a closer look at the performance data. In addition, the diagnostic report also provides a variety of data presentation forms for comprehensive analysis of the performance data log. As shown in figures 4 and 5.

Figure 4 time window scaling function of Windows Server 2008 performance Diagnostic report

Figure 5 multiple data diversity of the Windows Server 2008 performance Diagnostic report

SQL Server 2008 performance Monitoring object

SQL Server 2008 provides a wealth of performance monitoring objects and their counters. On a server, there can be multiple instances of the same performance object, for example, a Databases object must be associated with a specific database instance. For a default database instance, the performance counter name is prefixed with SQL Server:;. For a named database instance, the performance counter name is prefixed with MSSQL$:. In addition, some performance objects can have only one instance, such as MemoryManager.

The following describes the common performance counters for the main performance bottlenecks of the Windows Server 2008 database environment and explains what they mean.

CPU performance bottleneck

The performance monitor is the easiest tool to identify CPU performance bottlenecks: if the "Processor:% Processor Time" counter is consistently higher than 80%, the CPU load is too high, it is likely to be the system performance bottleneck, and you can consider upgrading CPU.

Recompilation of SQL statements

Excessive compilation or recompilation of SQL statements can also degrade the response performance of database queries. Users can monitor and compare the number of query compilations and query requests through the performance counters of SQL Server 2008. Ideally, the former should be far less than the latter. In the performance monitor, the former corresponds to SQL Server: SQL Statistics: SQL Recompilations/sec and SQL Server: SQL Statistics: SQL Compilations/sec

The latter corresponds to SQL Server: SQL Statistics: Batch Requests/sec. If there are too many SQL compilations relative to user queries, you should first determine whether the user has submitted too many random queries, and then consider other possible performance bottlenecks such as CPU.

SQL Server temporary Library (tempdb) bottleneck

Excessive use or removal of temporary tables and table variables will lead to overuse of tempdb, which will degrade system performance. In the Windows 2008 performance monitor, the commonly used SQL Server 2008 performance counters in this regard are:

SQL Server:Access Methods\ Workfiles Created / Sec

SQL Server:Access Methods\ Worktables Created / Sec

SQL Server:Access Methods\ Mixed Page Allocations / Sec

SQL Server:General Statistics\ Temp Tables Created / Sec

SQL Server:General Statistics\ Temp Tables for destruction

Cursor performance bottleneck

In the Windows Server 2008 performance monitor, the common counter for monitoring the performance of SQL Server 2008 cursors is SQL Server:Cursor Manager By Type-Cursor Requests/Sec. Through it, we can know the execution of cursors in the database. If the CPU utilization of the Windows 2008 database server is too high due to a large number of small batch cursor reads, this counter usually displays hundreds of cursor operations per second. In addition, SQL Server 2008 does not provide counters for cursor cache size.

To sum up, we can detect the performance counters of Windows Server 2008 through the reliability and performance monitor of SQL Server 2008. Combine the performance counter as needed, save it as a data collection group, execute it at the set time, and then view and analyze it through the performance diagnosis report.

For more information about SQL Server 2008 performance counters, please refer to Microsoft's official documentation http://msdn.microsoft.com/en-us/library/ms190382.aspx).

SQL Server 2008 manages data warehouse

Similar to the performance data collection group of Windows Server 2008, SQL Server 2008 provides new database performance collection and analysis tools. It mainly collects four types of performance data, which involve T-SQL query related data, SQL Server system tracking (SQL Trace) data, performance counter data and query processing activity related data. They contribute to the evaluation, management planning, and performance optimization of the following systems:

Disk utilization: collect disk utilization data at the SQL Server database level to help system administrators understand the physical disk occupancy of different databases and plan disk space.

Server activity: collect system resource utilization data at the instance level of SQL Server installation, such as CPU, memory, Imax O devices, etc. These data help system administrators to monitor recent or long-term trends in system resource utilization and identify potential resource utilization bottlenecks. This information can also be used for system resource planning of administrators.

Query statistics operation: collect query statistics at the SQL statement level, including query statements and their execution plans. These data help system administrators to identify the query statements that take up the most resources, so as to optimize query performance pertinently.

This performance data is collected through the SSIS task package. These task packages can be executed manually or set to be executed regularly or periodically. The collected data is stored in a dedicated SQL Server data warehouse, which is called SQL Server Management data Warehouse, or MDW. A MDW can be used to hold performance data from multiple SQL Server installations. The detected target database server can be located at the remote end, and the performance data is passed into the MDW through the SSIS task package that is executed regularly on it. In order to ensure the efficiency of collection, the data collection task package can store the collected data in the temporary file of the target server, and wait until the scheduled upload time to transfer it to MDW. The collected performance data is viewed in SQL Server 2008 Manager (SSMS) through pre-designed SQL Server reports for performance analysis. We can not only set the time interval for data collection or upload, but also customize the performance data sets and performance reports we need to collect.

Thank you for reading! This is the end of this article on "sample Analysis of Windows Server 2008 Database performance Monitoring". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.