5 Things You Can Do in SQL Diagnostic Manager That You May Not Know

by Feb 3, 2016

There is a library of ‘how-to’ videos created for SQL Diagnostic Manager showcasing how to perform certain tasks and how to troubleshoot specific problems using the tool. Below are a handful of quick 3 minute videos to show you some examples of what you can do within SQL Diagnostic Manager that you may not have known.

Using Server Tags in SQL diagnostic manager

This may be useful for large environments where you are adding or grouping servers frequently in order to stay on top of growing organizations. For example, you can use tags to configure alert thresholds & rules, assign security permissions, set custom counters and run reports based on tags for specific servers.

Using SQL diagnostic manager to view contention regarding TempDB

TempDB can be a mystery at times. SQL Diagnostic Manager includes a graphical view of tempDB that you can view by a given server. See items such as: tempDB space used by file, tempDB space used over time, tempDB waits (contention) and use the history function to see past tempDB active sessions that caused contention.

There is a blog post talking about tempDB which you might find helpful talking about unplanned autogrowth in your tempdb database. The blog discusses an example of autogrowth and how to troubleshoot using SQL Diagnostic Manager to identify root cause.

How to configure monitoring of the host layer for SQL server instances running in a VMware

DBAs need visibility to appropriate metrics to monitor VM performance that is not usually available in vCenter. Often they also don’t have appropriate access permissions to VMWare tools &/or the capabilities in vSphere are limited in scope to effectively troubleshoot issues. This includes information not only concerning the virtualized server, but also the physical host it is running on and the VM Kernel that is managing the relationship between the physical resources and the myriad of virtual resources in the host’s virtualized systems. To this end, the following capabilities are available in diagnostic manager to help fill this gap:

  • Performance metrics for VMs and their related ESX hosts to the CPU and Memory Usage charts
  • Network throughput charts (VM and Host)
  • Disk Usage charts (VM and Host)
  • Alerts on 13 VM and Host related metrics
  • Ability to add additional VM and Host counters through the Custom Counters feature
  • VM and Host related data to relevant reports

Users can track and monitor Hyper-V specific performance metrics as well for a complete view of the virtualized server and its physical host. This particular video focuses on VMWare virtual servers configuration.

Introduction to the SQL diagnostic manager management pack for Microsoft SCOM

The SQL DM Management Pack was built with out-of-the-box integration into Microsoft SCOM. The comprehensive SQL Server performance metrics of SQL DM feed into the System Center console without any customization or intervention needed. It pulls alerts from the SQL DM repository, displays them in the SCOM console, and integrates them into the SCOM database. A shortcut within SCOM lets a DBA easily open a SQLDM diagnostic session as well further streamlining triage efforts. This video provides of overview of how that works.

Getting started using the PowerShell plug-in for SQL diagnostic manager

A powerful and often underutilized capability in SQL Diagnostic Manager is the PowerShell snap-in feature. It is built-in to the product and can help with automating tasks as needed. Below are some ways in which you could use the Powershell snap-in:

  • Add servers to SQL DM as part of a deployment/go-live script
  • Turn on maintenance mode before reboot, turn off again when back online
  • Turn on query monitoring at the beginning of a job and off again when finished
  • Add/remove users to SQL DM application security in standard permission scripts

This video shows you how to set up the SQL DM drive to issue PS commandlets for tasks such as those mentioned above.

Hopefully you found this videos helpful. You can find more videos for SQL Diagnostic Manager and other products on the Resource Center.  Also feel free to visit the SQL DM forum to ask questions and read more tips and helpful tidbits on using the product.