THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

SQL Server Dump Analyzer

Recently Microsoft released a preview version of the SQL Server Diagnostics extension to SSMS. You can read more about it here. I downloaded it from here and you can see the results below. If you install the extension while SSMS is up and running, you’ll have to stop and restart SSMS in order to see the new menu option.

image

Figure 1. SQL Server Diagnostics extension to SSMS.

If you want to see how the tool works, you’ll need to either wait for a dump or forcibly create one. The instructions on creating a SQL Server dump from 2010 are still current and available here.

The easiest way to force the creation of a dump file is from within Task Manager, but I wanted to direct the output to a location of my choosing, so I used sqldumper.

image

Figure 2. Creating a dump file from Task Manager.

You’ll need to use an Administrative Command Prompt to run sqldumper. The exact path depends on which version of SQL Server you are running. I’m running SQL Server 2016, so the path is C:\Program Files\Microsoft SQL Server\130\Shared where 130 corresponds to compatibility level of SQL Server 2016. SQL Server 2014 is 120 and so on.

sqldumperAnnotated

Figure 3. Output from sqldumper /? so you can see the command line options.

You need the pid of sqlservr.exe to run sqldumper. Go to the Details tab in Task Manager.

sqlservr

Figure 4. My pid was 3892. Yours will be something else.

Running sqldumper.exe resulted in the following output:

sqldumperRun

Figure 5. Output from creating a dump using sqldumper.

I selected the Analyze Dumps option. This is a cloud based service, so be prepared to wait as your large dump file is sent to Microsoft. Notice that you have to pick an Azure data center as your dump’s repository. Can you upload this outside the country where your server is? Do your corporate policies allow a dump file to be shared outside your company?

image

Figure 6. Uploading dump file for analysis.

After the upload finished, the analysis took several minutes. You have to consider corporate policies and government laws on submitting corporate data to the cloud. If it is allowable to upload your dump to an external cloud server, you might find this new service useful.

Published Tuesday, July 11, 2017 9:55 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog

Syndication

Privacy Statement