Last week I attended one of the 24 hours of PASS sessions by Glenn Berry about diagnosing performance problems with DMV’s. If you don’t know what the 24 hours of PASS is, it’s an initiative by PASS where there are 24 sessions, one session every hour for an entire day.
Glenn Berry is also knows as Dr. DMV because he made some genius scripts to analyze your instances and databases using the dynamic managed views (DMV).
I’ve used Glenn’s DMV scripts for years but always found them tedious to execute because there are about 70 individual scripts that either query instance or retrieve database information. Glenn did make it easier for you by creating Excel templates to save the information in.
There are separate scripts for each version of SQL Server that updated every month. Glenn only updates the versions for 2012 to 2016 with new features. The scripts are very well documented and even contain hints about how to solve some issues.
As almost every DBA I’m very lazy when it comes to repetitive tasks and when something takes a couple of minutes to do manually, I’ll make sure I don’t have to do it again by automating it.
So that’s what I did and what I created is a PowerShell module to export all the information from the DMV queries to an Excel sheet automatically.
The hardest part of automating this module was to parse the entire script into individual queries. Fortunately Glenn has made this easy for me by placing a marker at the end of each query.
Note: Because the scripts are only updated from version 2012 and forward I made some changes to the older scripts to make sure the files could be parsed. If you let the script download the DMV files you’ll have no issues executing the module.
How it works
The module has one cmdlet called “Export-DMVInformation”.
The module is dependent on the module “ImportExcel” to make it possible to export the data to an Excel file. You can find the ImportExcel module here.
The module can be executed simply by supplying the name of the instance, but there are other options to make the end result more satisfying.
The cmdlet has the following parameters:
- instance: name of the instance. If it’s a default instance just use the server name
- database: the name of the database to query. If no name is given the master database will be used.
- username: The username in case sql authentication is needed
- password: The password that goes with the username if sql authentication is needed
- dmvlocation: The location of the dmv queries. The default is “My Documents\dmv\queries”
- destination: The destination where the Excel files need to be written to. The default is “My Documents\dmv\results”.
- excludeinstance: A flag to exclude the instance queries. Default is 0 or $false.
- querytimeout: Timeout in seconds for the query to not let the query take up too much time. The default 300 seconds (or 5 minutes).
The heart of the cmdlet is the dmv file because without it doesn’t work. You don’t have to worry about which version needs to be used because the cmdlet will find that out itself.
If there are no DMV files, or you couldn’t bother do download them, don’t worry. The cmdlet will initiate a download and download the DMV files for you to the default location set in the parameter “dmvlocation”.
A partial result of the cmdlet can be seen below:
There will be a Excel file with data for the instance and a separate file for the database when the cmdlet is finished.
How to install
The easiest method to install the module is by copying the code below and entering it in a PowerShell window:
(new-object Net.WebClient).DownloadString("https://raw.githubusercontent.com/sanderstad/Export-DMVInformation/master/Get-ExportDMVInformation.ps1") | iex
Alternative installation method
Alternatively you can download the module from here.
Unzip the file.
Make a directory (if not already present) named “Export-DMVInformation” in one of the following standard PowerShell Module directories:
- $Home\Documents\WindowsPowerShell\Modules (%UserProfile%\Documents\WindowsPowerShell\Modules)
- $Env:ProgramFiles\WindowsPowerShell\Modules (%ProgramFiles%\ WindowsPowerShell\Modules)
- $Systemroot\System32\WindowsPowerShell\v1.0\Modules (%systemroot%\System32\ WindowsPowerShell\v1.0\Modules)
Place both the “psd1” and “psm1” files in the module directory created earlier.
Execute the following command in a PowerShell command screen:
To check if the module is imported correctly execute the following command:
Get-Command -Module Export-DMVInformation or Get-Module -Name Export-DMVInformation
If you see a list with the functions than the module is installed successfully. If you see nothing happening something has gone wrong.
I hope you enjoy the module and that it can help you to get performance information fast.