The Configure SQL Diagnostic Manager Collection window of the All Servers wizard allows you to set the interval at which you want to collect diagnostic data and raise alerts, and whether you want to enable query monitoring.
The collection interval you specify refers to the time SQL Diagnostic Manager waits to collect statistical information on your SQL Server instance. This data includes the resource, session, database and tables, query, and service information.
The Query Monitor is a standard SQL Server trace that collects events occurring on your SQL Server instance over a period of time.
Enabling the Query Monitor can cause SQL Server performance degradation while it runs. Enable the Query Monitor only when diagnosing specific query issues.
After making any changes to the collection interval or enabling the Query Monitor, click Next to continue.
Access the Add Servers wizard
You can open the Add Servers wizard from the SQL Diagnostic Manager Console menu by selecting File > Manage Servers, and then clicking Add in the Manage Servers window.
Select a collection interval
Choosing the appropriate interval depends on your specific monitoring needs. When your SQL Server instance performs poorly, you can decrease the collection interval so that you can track changes more often. When monitoring a SQL Server instance that has a very heavy client load and a large number of databases, you can increase the time period between collections to reduce the possibility of the frequent collections causing a performance impact on your clients.
Monitoring your environment by collecting query activities
When you experience query timeouts or other performance issues, you can collect query-related activities through the Query Monitor. Note that enabling these options can degrade performance on the monitored SQL Server instance because of the additional data collection and storage. To help alleviate degradation, SQL Diagnostic Manager allows you to select only those types of queries you want to monitor such as batches, statements, procedures, and triggers. Additional threshold fields allow you to set the levels at which you consider a query as performing poorly.
It is important to remember that once you complete your problem diagnosis, you can disable the Query Monitor on the Monitored SQL Server Properties window by selecting the Query Monitor tab and then clearing the Enable the Query Monitor check box.
Using advanced Query Monitor options
Query Monitor includes advanced options that allow you to filter applications, databases, and SQL text from collection. Use semicolons (;) to separate multiple entries. You can also use the percent character (%) as a wildcard.
You can access this window by clicking the Advanced button in the Query Monitor tab of the monitored SQL Server Properties window.