Using SQL Server Diagnostics (Preview)

I was VERY excited when I read the following tweet (below) from Bob Ward regarding SQL Server Diagnostics capability. What is it you are asking? It is an extension to SQL Server Management Studio (SSMS) where it gives you the ability to Upload / Analyse dump files created by SQL Server.

bobsql.JPG

This is HUGE news, I could not resist writing up on it ( sorry the Cloud series will be delayed for a few more days) because last month I had a really strange Dump file produced by SQL Server. These files are located in the \LOG\ folder of your install. The information for my instance was as below.

Dark Clouds Are Ahead

bugs

There is a log file that holds the actual stack trace, if you remember an older post of mine I was able to use WinDBG (in the past) to at least get human readable text, but for this case I couldn’t.

stacktraces

Believe me, this was a really weird one.

The Tool

So let’s put the new tool into action! You can find it here. https://www.microsoft.com/en-us/download/confirmation.aspx?id=55516

It is a classic installer, just be sure to read the privacy agreements. This is on my local machine so I am happy with it.

ssds

Keep in mind the SSMS 2017 requirement as shown below.

ssds1

The install takes 10 seconds, if that. So next I head straight into my SSMS 2017 for the tools menu.

ssmsmain

This is the main analysis screen.

dumpitnow

Notice the region to upload – If you are using a work machine I would suggest getting authorisation. The great thing here is that this is GDPR compliant.

Once ready hit the upload button, it goes through 3 phases. Upload, Analysis and a recommendation.

Upload

The Silver Lining

Give or take 10 minutes here is the final output:

analysisdone

The recommendations seem to be a list of KB articles, reading through them they are references to bugs. I have been messaging a member from the Diagnostics team at Microsoft and these are in ranked order. So the top one is most relevant to my issue and I should be installing the CU referenced within that specific KB link.

I was also told that going forward there will be 3 recommended fixes rather than 10.

Here are some screen shots of its findings.

clrerror

assert

Others

It’s better than having

Hex

At the main screen of analysis you may notice a delete option. This does NOT delete the upload. The actual dump uploaded will be persisted for 90 days, then after that an automatic purge will occur.

By the way, if you entered your email address you will be sent an email From Microsoft Enterprise Concierge Service with a summary of findings.

emailed

Takes self-diagnostics to a new level. What I like about this tool is that it sheds some light  when things are dark and confusing. I guess I will be patching this instance up, next I will be looking at my other server which has 77 dump files!

Thanks to the Diagnostic Team and Bob Ward at Microsoft with the detailed replies to my queries – much appreciated.

8 thoughts on “Using SQL Server Diagnostics (Preview)

  1. Pingback: Self-Analysis Of SQL Server Dump Files – Curated SQL

  2. Pingback: Using Get-SQLDiagFix to get information from the SQL Server Diagnostic API with PowerShell | SQL DBA with A Beard

  3. The note says “This extension requires Microsoft SQL Server Management Studio (SSMS) 2016 or a later version.”

    But it doesn’t appear to work with SQL Server Management Studio 2017.1. I have 2017.1 installed, but when I run the msi, I get a modal dialog with “You need SQL Server Management Studio (SSMS) 2016 or higher to continue installing SQL Server Diagnostics extension.” Only option is OK, and then setup ends.

    (OS is Win2012R2 Standard if that matters, running in a VM with SQL 2016 installed as a server, plus SSMS 2017. I don’t have SSMS 2016 on there.)

    Please let us know what is the fix for this issue ?

    Like

  4. I’ve installed it, but I’m curious about where my uploaded dumps go and who is reviewing them? GDPR applies to the EU; not the US. What assurances do we have for privacy and security of our uploaded server data? Thanks.

    Like

  5. Hi admin, i must say you have hi quality content here.
    Your page can go viral. You need initial traffic only.
    How to get it? Search for; Mertiso’s tips go viral

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s