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

SQL SERVER 2016 query history storage slow sample analysis, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

To get to the point, SQL SERVER, which is different from MYSQL,PG, does not have any log in slow query, but it always feels flawed compared with the other two databases through DMV or tools.

But since SQL SERVER 2016, SQL SERVER has also realized this and added a feature. How to open and use follow me.

1 Open SSMS (don't say you don't know what SSMS is) SQL SERVER management studio

2 Click the database where you want to record the slow log-right-click Properties-query Storage (query stone)

There are not many parameters that can be adjusted, so we can pay attention to them.

Query stone capture mode

Size base cleanup mode

Can be set to automatic

At the same time, you can set the preservation time of the relevant data, etc., roughly look at it, we will understand, there will be no more talk here. Related designs can also be made on the frequency and interval of information collection.

After setting up, in fact, the database can collect information.

Several query methods are provided.

1 regression query

2 overall resource usage

3 ways to use resource rankings

4 with a way to enforce the plan

5 queries with high differences

6 tracking query

In terms of the simplest problem troubleshooting, we may first focus on slow queries.

And slow query can be reflected in several ways.

1 long query time

2 use physical Imax O more than others

3 how much memory is used

The function provided by SQL SERVER 2016 has all the dimensions and perspectives to analyze and find problems.

A more powerful function is that it can analyze and query the query data according to history.

What kind of problems can be solved by this technology?

1 statement performance problems encountered after the system is online or during testing

2 changes in the execution plan that occur during the execution of the statement

3 percentage of performance used in statement execution

4 the problem of finding missing indexes

5 quickly analyze the statement performance dimensions in the current server

The following examples are

1 A serious performance problem occurred 1 hour after the system went online. The operation and maintenance staff reported that CPU was heavily occupied, but the problem caused by the specific statement could not be found.

Methods:

Click on the overall resource usage, we find the CPU time

Give an example

Just click on the rectangle that takes up the most CPU time in the figure above, and immediately after clicking, we can find that this statement lacks an index.

Question 2 the system has been running for some time and it has been told that the system is running slowly at present, and we need to collect the difference in the number of execution times and performance of some high-performance statements in different periods of time.

The above statement is a simple query statement and takes the primary key, but why is it marked as a statement with a big gap? we can move the mouse to the picture on the right and place the mouse on the dot.

From the following figure, you can clearly see that for the same statement, the query time can range from 0.06ms to

The gap between 1888.51ms. And this is the phenomenon of some potential or prominent problems that we need to pay attention to.

You can even know the logic and busyness of the business through certain sentences.

This is the answer to the sample analysis question about the slow storage and troubleshooting of SQL SERVER 2016 query history. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.