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.
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.
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.
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.
Figure 4. My pid was 3892. Yours will be something else.
Running sqldumper.exe resulted in the following output:
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?
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.