[Reproduced Huanhuan] Knowledge of the DMV in SQL

Trust your friends some knowledge of SQL Server performance tuning is more or less relevant. Although NoSQL-related technologies are very hot today, Rmdb (relational databases) coexist with NoSQL and are used in a variety of projects. In the general enterprise-level development, the main or rmdb occupy the dominant position. And in the Internet project, not to abandon the rmdb, for example, MySQL in a lot of Internet applications play a role. Therefore, the tuning of the database is a subject worthy of further study. This series of articles focuses on tuning knowledge related to SQL Server and hopes to bring some help to friends.

The outline of this article is as follows:

• Comparison of traditional SQL Server tuning methods
• What is a DMV
• DMV Simple Example
• What problems can the DMV solve

Comparison of traditional SQL Server tuning methods

There are a number of tools that we can use to diagnose and tune SQL Server performance: performance counters, SQL Server Profiler, and Database Engine Tuning Advisor (DB Tuning Advisor, referred to as DTA). Let's take a brief look at each one and tell us the pros and cons of each.

Performance Counters

For developers using the Windows operating system, the performance counters are no longer unfamiliar, the simplest way is to run "perfmon", you can open the Performance Monitor window, and then add the corresponding counters, to monitor. As shown in the following:

Performance counters go back to collect relevant data every once in a while, and we can save this data for later analysis.

Advantages: Easy to use, and data collection is more comprehensive. Because these operating systems are integrated and do not require additional cost, they can be used directly.

Disadvantage: The data is not accurate, the analysis data cost is very high, and has the influence to the system performance. Because performance counters in the collection of data, the timing of the capture system-related data, this will affect the performance, if the time interval is shorter, then the impact on the system is greater. In addition, for the data collected, it is necessary to have experienced people to analyze the data, and if you want to accurately analyze the results, the need to collect a large amount of data, the cost of manpower and time is also high.

SQL Server Profiler

Every time we talk about SQL Server tuning, it's going to be about SQL Server Profiler (which we'll simply refer to as profiler for further convenience). Using Profiler, you can capture the records of each query that SQL executes over a period of time. This tool is useful when there are a large number of queries that are rarely run on the database server, or when there are special user queries running. Using profiler can also capture workloads over a specified period of time, which can then be reproduced in a restored database system.

Here is an interface that uses the profiler:

Where you use the profiler, where to start the profiler and where to keep the tracked data, is a particular concern. Let's take a look at a variety of different situations in comparison.

Where to start the profiler	Trace file	Description
To start the profiler trace on the database server being monitored	Save tracked data files to the server's file system or shared directory	Pros: This approach is suitable for development on non-production environments and low-load servers, and for performance diagnosis and tracking of database applications. Cons: Increased server I/O Read and write operations
On the native Profiler, connect to the remote database server	Save the tracked data file locally	Advantage: Reduces I/O operations on the database server. Disadvantage: Increase the pressure of network transmission, occupy the database server network resources and CPU resources. Because of the need to transfer a large amount of tracking data to the local computer, which takes up the network resources, and the data in the transmission, the CPU needs to be serialized, increasing the CPU operation.

Database Engine Tuning Advisor (DTA)

DTA generally needs to be used in conjunction with SQL Server Profiler. DTA is primarily a comprehensive analysis of the data collected in the profiler, so the accuracy of its analysis results depends very much on the amount of data the profiler collects. The more data The profiler collects, the more accurate the DTA analysis is, but the greater the pressure on the database server, and vice versa. Therefore, it is generally not recommended to use the database server in a large load or production environment.

What if there is a need to analyze and tune the performance of a database in a large load, or in a production environment?

This is the topic we are going to discuss in this series of articles.

What is a DMV

Friends who have used SQL Server, have some knowledge or heard about the DMV (dynamic Management views, dynamically managed view). In fact, the DMV is the SQL Server kernel metadata, through the analysis of internal metadata, we quickly and accurately get a lot of SQL Server-related information, for performance analysis.

When the query runs in SQL Server, SQL Server automatically records information about the activity and stores it in memory, which is called the DMV.

Different types of DMV information, for example, can be related to the DMV to diagnose performance, improve performance, or monitor the operation of the database, or resolve failures.

The DMV is saved at the level of an instance of SQL Server. It also says that if a SQL Server is installed on the server, then this SQL Server is an instance, then all of the database's DMV in this instance is stored in the same memory. Of course, when we use it, we can extract only the lower-level DMV as needed, such as the DMV that extracts a database, a DMV for a table, or even a DMV for a query.

Because the DMV information is stored in memory, we do not need additional operations, we just need to take this information out, according to our requirements of the operation, statistics, analysis is enough, the data to obtain information is very fast, and will not put pressure on the server. In addition, since the DMV is saved by SQL Server itself and has already made statistical information, the data is closer to the state of the database itself.

The longer SQL Server runs, the more information is stored in the DMV (of course, the DMV is very small and does not stress the memory), and the more accurate the DMV analysis is. This is completely different from the previous profiler and DTA. The only problem is that every time the SQL Server service restarts, the DMV information that is stored in memory is gone, and it is saved from the beginning and slowly. Of course, we have a lot of solutions to this problem, for example, we can periodically export the DMV information and save it on disk.

The DMV contains a lot of information: Index related, query execution related, SQL Server OS related, Common Language Runtime (CLR) related, transaction related, security related, resource management related, data backup related, I/O related, Full-text lookup related, database mirroring related, and so on information. Therefore, we can use the information that has been stored in the DMV to conduct our analysis.

Because there are many DMV internal to SQL Server, the articles in our series focus on performance analysis and tuning, so we focus primarily on the following types of DMV: index-related, execution-related, SQL Server OS-related, CLR-related, transaction-related, I/O-related, database-related.

Below, we analyze the run of a query to see what information (or what information is stored in the DMV) is logged by SQL Server during this process:

1. Execution plan of the query (that is, how a query is executed)
2. What index is used
3. What index should be used, but not used. (because there is a performance problem with missing indexes at this time)
4. Status of I/O (contains logical I/O operations and physical I/O operations)
5. Time the query execution consumes
6. The time that the query waits for other resources to consume
7. What resources are waiting for queries

By analyzing this information, we can not only better understand how the query works, but also let us think about how to use resources more rationally and efficiently and improve performance.

In general, when using a DMV, we often need to use it with the DMF (Dynamic Management Functions). We can simply understand the DMF as a series of functions inside SQL Server. For example, by analyzing the sys.dm_exec_query_stats, you can know the query information, if the sys.dm_exec_query_stats inside the sql_handle passed to Sys.dm_exec_sql_text, then, We can then know the contents of the query's statement.

DMV Simple Example

To get a deeper understanding of the DMV, let's start by looking at a DMV to find out which queries are running the slowest. (Friends may not be familiar with the related DMV and DMF mentioned here, it's okay, follow-up articles will be introduced)

In Query Analyzer for SQL Server, run the SQL statement:

At this point, the results of the run are as follows:

In this query, we mainly by the sys.dm_exec_query_stats this DMV with Sys.dm_exec_sql_text and sys.dm_exec_query_plan these two DMF, through the analysis of the time consumed by the query, Then sort from high to low and select the top 20 to show.

From this example, we can know the points:

1. When you query the DMV, you should minimize the impact on the database as much as possible. So, at the top of the query, we added: SET TRANSACTION isolation Level READ uncommitted. Because each time the query is run, more or less the database will have different degrees of locking, and the level of locking varies. With the above settings, you tell SQL Server that the next query that executes will be the lock level: Read uncommitted. This minimizes the impact.
2. Each time a performance problem is diagnosed, the most severe performance issues are addressed first. So, we're just looking at the top 20 slowest query statements here.
3. Although the original DMV information provides a lot of information, it is often necessary to perform complex statistical analysis of the DMV. (This cost is smaller and simpler and more accurate than analyzing the data collected by SQL Server Profiler.) ）

What problems can the DMV solve

After reading the simple example above, I believe my friends have a perceptual understanding of the DMV, and below, let's take a look at what we can solve with the DMV.

Fault Diagnosis

The diagnosis is to identify where the problem lies. There are a lot of ways and tools that can help us achieve this, but with the DMV, it might be quicker to do it: nothing is faster than analyzing the metadata inside SQL Server.

Many times, the diagnosis of the problem is also the first step in performance tuning, to find out the problem, just the right remedy.

The DMV can be used to diagnose the following problems: The slowest query statements, common waits and blockages, unused indexes, a large number of I/O operations, and the lowest utilization execution plan.

As previously mentioned, we can analyze issues at different levels, such as from the entire server level, the database level, or even a query. We can do this by setting the conditions for obtaining information when obtaining the DMV information. For example, in the example in the previous section, we can get the top 20 slowest queries in the entire SQL Server and, if necessary, we could shrink the condition to a database.

Many times, when identifying problems, it is not so easy, just through a DMV to be done, need and DMF combined. Even with the other DMV (we will understand more deeply in subsequent articles).

Diagnosing a problem is an area that is especially important to solve problems.

Performance Tuning

Performance tuning is primarily the use of relevant techniques to diagnose problems before they occur, thereby improving performance. We will elaborate on the follow-up, and we won't repeat them here.

Status Monitoring

Many DMV (especially those that start with sys.dm_exec_) reflect the state of the database server execution. By looking at these DMV, we can clearly know the current state of the database server and the state of history (of course, how the SQL Server service is restarted, then the previous information is lost, unless saved on a regular basis). For example, a database needs to be batched for a long time, and if the operation is timed out or running very slowly, we can query the DMV for analysis. If you use Profiler or the associated profiler script tracking, the pressure on the database server is significant. For example, you can also analyze what queries are running in the database, how many requests are processed, how many connections to open, and so on, mainly on database operations, which can be queried through the DMV.

Original: Http://www.infoq.com/cn/articles/wy-sqlserver-performance-optimization

[Reproduced Huanhuan] Knowledge of the DMV in SQL