Introduction
This blog post describes ten lesser-known features of SQL Diagnostic Manager with links to short how-to videos and additional resources. Learn how to add custom counters and custom SQL scripts, capture deadlocks, integrate with SCOM, monitor application transactions, query waits, tempdb contention and virtualization metrics, and setup actionable responses, baselines, and custom dashboards.
#1 Monitor Application Transactions
Monitor wait states and application transactions by continuously sampling queries and plans with the SQL Workload Analysis add-on. This add-on significantly upgrades the wait state analysis capabilities of SQL Diagnostic Manager. Combine the operational monitoring from SQL Diagnostic Manager and transactional monitoring from SQL Workload Analysis to view instance properties and alerts, receive prescriptive recommendations for operational diagnosis, analyze workloads for transactional diagnosis, and arrive at a comprehensive root cause analysis. Refer also to the blog posts “How Can SQL Workload Analysis Help You?” and “SQL Diagnostic Manager General Availability” introducing the add-on, and the product pages, the data sheet and the product documentation for the add-on.
#2 Set Up Baselines
Calculate baselines out of a pool of collected data based on the selected period and collection interval. Represent baselines by mean, standard deviation, minimum, maximum, and count statistics. Provide alert recommendations to set effective alert thresholds. Visualize when to set up baseline periods with effective baseline periods. Enable alerting based on baseline violations. Define and schedule multiple baselines per server. Quickly propagate baselines to more than a single instance. View relevant baseline in effect in various metric graphs of the Resources tab. Configure the server baseline options with the Baseline Configuration tab of the Monitored SQL Server Properties window. Refer also to the blog post “Why Be Normal?” and the product documentation “Configure server baseline options”.
#3 Create Custom Dashboards and Add Custom Counters
Setup custom dashboards with the Custom Dashboards tab of the web console. Define and save multiple dashboard views to compare metrics across SQL Server instances. Customize the Dashboard view of the desktop console per instance by selecting the panels that are important to view for that instance. Alter the view to monitor the most important metrics in the Dashboard Designer by selecting panels in the Panel Gallery. Add more performance metrics to SQL Diagnostic Manager with custom counters for historical trending, on-demand monitoring, schedule refresh alerting, and reporting. Add any Windows system counter, any SQL Server system counter stored in the sysperfinfo system table, any numerical value returned from custom SQL scripts, and any virtual machine and host server performance counters that are accessible through the host server. Refer also to the product documentation “Create custom dashboards”, “Customize your Dashboard view”, and “Use custom counters to track metrics”.
#4 Capture Deadlocks
Follow the performance of the active and blocked sessions with the Sessions panel. Deadlocks have no lead blockers and are circular in nature so that they do not display in the Blocking Tree. Instead, deadlocks display in the Blocking Chart. Drill-down to view the deadlock victim and the code responsible for the deadlock. Self-blocking sessions do not count as blocked or blocking sessions. Use the History Browser to view past performance and go back to the time to see the sessions involved with the received blocking alerts. Detect deadlocks with the Deadlock alert. Refer also to the product documentation “Sessions panel”, “Analyze blocked sessions”, “Create alert response bundles”, and “Deadlock alert”.
#5 Create Actionable Responses
Configure alerts to inform and warn of approaching issues. The Alert Actions and Responses window adds, modifies, imports and exports, and configures alert responses. The Available Alert Responses tab enables and disables action responses. The Available Action Providers tab enables and disables action providers. Action providers include email via SMTP, prescriptive analysis, query monitor, query waits, Windows Event Log, network management via SNMP, Windows PowerShell, program action, sent an alert to Microsoft’s System Center Operations Manager (SCOM), send an event to SCOM, SQL agent, and SQL script. Send alerts to different groups of people based on metric, time-of-day, instance, and more with advanced notifications. Refer also to the blog post “SQL Diagnostic Manager Secrets: Configuring Alerts”, and the product documentation “Configure how SQL Diagnostic Manager responds to alerts”.
#6 Monitor Query Waits
Identify and monitor problematic SQL queries, batches, statements and stored procedures. Analyze query waits over time and by duration with the Query Waits tab of the Single Instance dashboard. Better find where the biggest bottlenecks are occurring and what changes could potentially have the biggest performance boost on the SQL Server instance. The Query Waits Over Time view provides a selectable time range for the stacked bar chart display of waits. The Query Waits By Duration view displays a horizontal bar graph of query waits by the length of the wait. Both views come with various dimensions for display to investigate further any performance issues: waits, waits category, statements, applications, databases, clients, sessions, and users. Analyze different wait type categories with the Query Wait Statistics report. Better find where the biggest bottlenecks are occurring and what changes could have the greatest performance impact by analyzing these waits. Select from various categories of wait types. Refer also to the video “Query Wait Performance”, and the production documentation “View your SQL Server query waits information” and “Query Wait Statistics” analysis report.
#7 Add Custom SQL Scripts to Measure SQL Server Uptime
Enter Transact-SQL script for custom counters with the Provide Custom SQL Script window of the Add Custom Counter wizard. Custom SQL scripts return a single result set with a single numeric field. Custom SQL scripts display in the Queries tab with User Defined Counter in their application name. Refer also to the blog post “Achieve Better Automation with Idera’s SQL Diagnostic Manager” (Example 3) and the product documentation “Provide custom SQL script”.
#8 Integrate with Microsoft SCOM
Integrate and extend the monitoring capabilities of Microsoft’s System Center Operations Manager (SCOM) with the Management Pack. Gain more insight into the availability and performance of monitored SQL Server instances from a single console. The Management Pack includes automatic discovery of installations of SQL Diagnostic Manager and SQL Server instances managed from those installations. Once discovered, propagate the current status, health, and events of monitored SQL Server instances up to SCOM with the Management Pack. Use Alert Actions and Responses to control which alerts to escalate to SCOM and whether they go into SCOM as an Event or an Alert. The Management Pack is easy to install and does not incur any additional monitoring overhead. Refer also to the blog post “Get Better Integration with SCOM using SQLdm SCOM Management Pack”, the product documentation “Integrate SQL Diagnostic Manager with SCOM”, and the install guide.
#9 Monitor Tempdb Contention
Track the status of the tempdb database with the Tempdb panel. View the status of the tempdb database with the Tempdb Summary tab. Display a list of sessions using tempdb along with their cumulative usage and tempdb space. Its charts include options for viewing the current capacity usage and recent trends of the files over time. View space usage and data throughput statistics over time for the tempdb database with the Tempdb Statistics report. Focus on a single chart type (such as data file growth) and track its value increase over a specified time. The Session Tempdb Space Usage alert provides the amount of tempdb space used by a running session. The Tempdb Contention alert provides the latch wait time for tempdb allocation maps. Refer also to the blog post “Identifying the cause of tempdb autogrowth using SQL Diagnostic Manager”, and the product documentation “Tempdb panel”, “Get the tempdb status summary”, “Tempdb Statistics” analysis report, “Session Tempdb Space Usage (MB) alert” and “Tempdb Contention (ms) alert”.
#10 Monitor Virtualization Metrics
Easily monitor the performance of virtual machine (VM) environments of VMware or Hyper-V. View performance metrics and alerts for VMs and their host servers including CPU, memory, disk usage, and network. Virtual SQL Server instances appear in the Servers pane in the same way as physical SQL Server instances. Display the same dashboard panels for virtual instances as for physical instances, and include specific metrics for the VM and its host server. Access virtualization alerts and reports. Refer also to the product documentation “How SQL Diagnostic Manager works with a virtual environment”.
Takeaways
Watch the how-to videos and refer to the additional resources to learn how to use ten lesser-known features of SQL Diagnostic Manager. These topics include monitoring application transactions, setting up baselines, creating custom dashboards, adding custom counters, capturing deadlocks, creating actionable responses, monitoring query waits, adding custom SQL scripts to measure SQL Server uptime, integrating with SCOM, monitoring tempdb contention, and monitoring virtualization metrics. Further, refer to the Resource Center, and this blog, the product pages and the data sheet for SQL Diagnostic Manager. Also, download a free 14-day trial and request a live product demonstration.