Page History
...
The Query Monitor window allows you to enable or disable query monitoring using the Enable the Query Monitor check box. You can also select the settings that are used:
- Query data collectionusing Query Store
- Query Execution Plans collectiondata using extended events
- Capturing poorly-performing queries
Anchor QueryStore QueryStore
Query data using Query Store
QueryStore | |
QueryStore |
Collect query data using Query Store for instances running SQL Server 2016 or higher.
Anchor querydatacollection querydatacollection
Query data
querydatacollection | |
querydatacollection |
...
using extended events
You can select to collect query data using Extended Events (Collect query data using extended events radio button) or SQL Trace (Collect query data using SQL Trace radio button). For instances running SQL Server 2008 or higher, the Query Monitor uses by default Extended Events. First introduced in SQL Server 2008, Extended Events provide a new mechanism to capture information about events inside the Database Engine and diagnose performance problems. This functionality is highly efficient and lightweight. For more information about using Extended Events, see the Microsoft document,
. Newtablink alias Extended Events url http://msdn.microsoft.com/en-us/library/bb630282.aspx
Note | ||
---|---|---|
To capture query data for monitored pre-SQL Server 2008 instances, enable the SQL Trace collection option. Enabling this option can degrade performance on your SQL Server. | ||
Warning | ||
| ||
Query Execution Plan capturing is not available if you choose SQL Trace for the Query Monitor. Upon selecting the Collect query data using SQL Trace radio button, the Collect query data using extended events ( SQL Server 2008 and up only) radio button is disabled. |
...
...
To
...
collect query execution plans, clickCollect query data using extended events (SQL Server 2008 and up only) in the Query Monitor window. By default, this option enables the collection of estimated query plans only. If you would like to collect actual query plans instead, select the Collect Actual Query Plans (SQL Server 2008 and up only) check box.
...
For instances running SQL Server 2008 or higher, the Collect query data using extended events (SQL Server 2008 and up only) and the Collect Estimated Query Plans Only check boxes are enabled by default. |
Warning |
---|
Enabling actual query execution plans collection can have a significant performance overhead, so IDERA recommends using this feature only when troubleshooting or monitoring specific problems for short periods of time. You can also use estimated query plans. |
...
You can also define specific poorly-performing thresholds such as duration, logical disk reads, CPU usage, and physical disk writes, and select up to 1000 plans defined by these thresholds.
Diagnosing specific performance issues
...