SQL Server2016 new features real-time query statistics

SQL Server2016 new features real-time query statistics

Many times there are scenarios where development complains that DBAs do not tune good databases, DBAs complain about poorly developed program code, so DBAs and developers become enemies and cannot really troubleshoot problems.

DBAs can use tools such as Windows Performance Monitor, SQL Server built-in Activity Monitor, SQL Trace, SQL Profiler, performance dashboard, or use an execution plan to view query costs.

In order for DBAs to have more effective tools to troubleshoot problems, SQL Server2016 has introduced a number of new features, one of which is live query Statistics (LQS real-time querying statistics), which shows what was previously not easy to see in the execution period. For example, statistical information during a query, this feature helps DBAs find the root cause of a long-time query (root cause)

Using real-time statistics queries

Using real-time statistical queries is simple, just click on the "Include Actual execution plan", "include real-time statistics" icon in the SSMs toolbar, and execute your query.

You can now see the operators used in the query in the real-Time Query Statistics tab page, which is time-consuming to query, and you can see the completion of the entire query in the upper-left corner of the tab page

The use of "real-time query statistics" will have a certain impact on performance, when the query is more complex, the waiting time will increase, and the whole process will also consume a lot of CPU resources, so the use of time must be prudent, otherwise the problem is not found, but instead caused more pressure on the database.

Tips
If you only use include real-time query statistics and do not open include actual execution plan, the elapsed time and completion rate seen in the tab page including real-time query statistics will be 0

Consumption time is not displayed

Drill down in execution plan (drill down)

During the execution of real-time query statistics, you can click on any of the operators in the execution plan to see the statistics of the operators, for example: Time spent (Elapsed), processing progress of operators (operator progress), current CPU usage, Follow this information to help DBAs find the bottleneck

In addition, when the query is still executing, you can switch to the activity monitor, and in the newly added "active resource-intensive Query" tab, you can see the time-consuming query that is currently active until the query finishes executing

The principle of real-time query statistical information

The rationale behind real-time query statistics is actually getting information through the DMV dynamic management view, which is then rendered on the tab page, so that DBAs can quickly find out how long-running queries are consuming the most time and cost without having to query the DMV on their own.

Here is the DMV used for real-time query statistics, and of course you can use the following DMV to query for the required statistics

SELECT *  from   sys.dm_exec_requestsSELECT*from sys.dm_exec_sql_text   Select* from sys.dm_exec_query_memory_grantsSelect*  from sys.dm_exec_query_plan SELECT *  from Sys.dm_exec_query_profiles

Limit

The following features are not currently supported in real-time query statistics

Columnstore index (Columnstore indexes)
Memory-Optimized table (optimized tables)
Locally compiled stored procedure (natively compiled stored procedures)

More SQL Server2016 useful features please expect O (∩_∩) o

SQL Server2016 new features real-time query statistics