Share via

SQL Server Diagnostics (Preview)

  • Article

EDIT (1/10/2018) : As of yesterday, this preview service was shutdown. SQL Server customers encountering product issues that generate dumps should contact Microsoft CSS for support and guidance.

Today, Microsoft is releasing the SQL Server Diagnostics (Preview) extension within SQL Server Management Studio and Developer APIs to empower SQL Server customers to achieve more through a variety of offerings to self-resolve SQL Server issues.

What this offers to our customers?

Analyze Dumps - Customers using this extension will be able to debug and self-resolve memory dump issues from their SQL Server instances and receive recommended Knowledge Base (KB) article(s) from Microsoft, which may be applicable for the fix. The memory dumps are stored in a secured and compliant manner as governed by the Microsoft Privacy Policy.

For example, Joe, a DBA from Contoso, Ltd., finds that SQL Server has generated a memory dump while running a workload, and he would like to debug the issue. Using this feature, John can upload the dump and receive recommended KB articles from Microsoft, which can help him fix the issue.

 

FAQ:

Q. What version of SQL Server is supported?

Answer: SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016.

Q. What version of SQL Server Management Studio (SSMS) is supported?

Answer: Microsoft SQL Server Management Studio (SSMS) 2016 or a later version ( SQL Server Management Studio 17.1 ).

If you don't have SSMS installed, please install it from: SQL Server Management Studio

Q. Which process dumps can be uploaded for analysis?

Answer: The dumps we are analyzing are for SQL Server process (sqlservr.exe).

Q. What type of dumps are supported?

Answer: We support mini dumps, filtered dumps and full dumps. The dumps can be of the following types - .dmp, .hdmp, .mdmp.

Q. What are the scenarios in which the dump may have been generated?

Answer:  The dumps could have been generated for exceptions, hangs, non-yielding scheduler and manual.

Q. What SQL Server platforms are supported?

Answer: We support SQL Server on-premise and SQL Server running on VM's.

Q. What is maximum size of the dumps?

Answer: The maximum size of the dumps can be up to 100 GB. If the file size is more than 8 GB we recommend to use LZ4 compression to compress the file before the upload.

Q. How is the recommendation determined?

Answer: The memory dump is analyzed for the root cause like exceptions, assertion or non-yielding schedulers and it is matched with any publicly available fixes for the issue in the form of KB articles.

 

View Recommendations -

Customers will be able to keep their SQL Server instances up-to-date by easily reviewing the recommendations for their SQL Server instances. Customers can filter by product version or by feature area (e.g. Always On, Backup/Restore, Column Store, etc.) and view the latest Cumulative Updates (CU) and the underlying hotfixes addressed in the CU.

 

 

To continue with the previous example, Contoso, Ltd. uses SQL Server 2016, and Joe wants to understand the fixes pertaining to every feature area before he applies the latest CU. Using this feature, he can view all the fixes that are part of the CU across the different SQL Server feature areas. In the future, Joe also wants to receive proactive recommendations, including bug fixes, assessment and migration to latest SQL versions, from Microsoft.

FAQ:

Q. What are the recommendations when I click on View Recommendations ?

Answer: We provide the information on the Latest Cumulative Updates for all supported SQL Server versions -  SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016.

Developer APIs -

Developers who want to discover and learn about our APIs can view our developer portal and then use our APIs in their custom applications. Developers can log and discuss issues and even submit their applications to the application gallery.

For example, Contoso, Ltd. has a monitoring system in place that detects issues whenever SQL Server generates memory dumps. Using our developer APIs, Joe can now extend the monitoring system to call our APIs to pick up the memory dump, create and send custom alerts, including recommended Knowledge Base (KB) article(s) applicable to the fix, to his extended team.

 

Note:

This extension requires Microsoft SQL Server Management Studio (SSMS) 2016 or a later version. If you do not have the latest Microsoft SQL Server Management Studio (SSMS) installed, please install it from here.

If you would like to assess database migration to SQL Server from Microsoft Access, DB2, MySQL, Oracle and Sybase please reference this article.

To migrate existing on-premises SQL Server, Oracle, and MySQL databases to Azure, whether your target database is Azure SQL Database, Azure SQL Database Managed Instance, or Microsoft SQL Server in an Azure virtual machine, learn more about the Azure Database Migration Service. For an opportunity to participate in a limited preview of this service, please submit a nomination.

Comments

  • Anonymous
    June 23, 2017
    Excellent. I love these feature Analyze Dump and view recommendations.
    • Anonymous
      June 26, 2017
      Thank you.
  • Anonymous
    June 24, 2017
    Nice stuff
    • Anonymous
      June 26, 2017
      Thank you.
  • Anonymous
    June 24, 2017
    Really liked the Analyze dump feature great
    • Anonymous
      June 26, 2017
      Thank you.
  • Anonymous
    June 25, 2017
    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.)
    • Anonymous
      June 26, 2017
      Hey Brent, we're aware of the issue and are the team is working to fix it ASAP. It will be available at the same download center once fixed.Thanks for pointing this out
      • Anonymous
        June 26, 2017
        Hi Brent,We are working to fix the installation experience when the OS is installed outside C: drive.Thanks,Rony
        • Anonymous
          July 04, 2017
          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 ?
        • Anonymous
          July 13, 2017
          Actually doesn't work when I have the OS on the C: drive
  • Anonymous
    June 26, 2017
    Great feature, however, the recommendation was for a KB article that I cannot find. Is it public? KB3165545
    • Anonymous
      July 12, 2017
      The comment has been removed
  • Anonymous
    June 26, 2017
    I have downloaded latest SSMS 17.1, but i couldn't see this feature on it.
    • Anonymous
      June 26, 2017
      This is a SSMS add-on. The download link for the Diagnostics preview is at the top of the blog post.
      • Anonymous
        June 26, 2017
        Thanks for the reply, let me try and get back if any help required.
      • Anonymous
        June 27, 2017
        Please download the extension from here : SQL Server Diagnostics (Preview)https://www.microsoft.com/en-us/download/details.aspx?id=55516
  • Anonymous
    June 26, 2017
    Which tool you recommend for LZ4 compression?
  • Anonymous
    June 26, 2017
    I tried using this add-in with SSMS 17.1 and it is stuck 'Loading Regions'. Is there anything that I need to change?
    • Anonymous
      June 27, 2017
      Hi Sagar, Could you please help with below information:1. Did you see any pop up message when you clicked Analyze Dumps in SSMS ?2. Have you installed the SSMS and the SQL Server Diagnostics extension on the server machine ? If yes then could you please look into the System Requirements section for few additional settings you have to do.
      • Anonymous
        June 27, 2017
        Hi Arijit,Thanks for your reply. I see now where the problem is. I have got this installed on Windows 8 system and it is not in the list of supported operating systems.I have now installed this on a Windows Server 2012 R2 system and it still does not go beyond 'Loading Regions'. I have made the below change as requested:1. Add https://ecs.microsoft.com/ to trusted sites2. In IE Settings, on the Advanced tab, deselect the Do not save encrypted pages to disk setting under the Security sectionI have 2 versions of management studio: Microsoft SQL Server Management Studio 13.0.15600.2 (SQL Server 2016 Management Studio)Microsoft SQL Server Management Studio 14.0.17119.0 (v17.1)I can access the website from the server if that make a difference: https://ecs.microsoft.com/Let me know if you require any further info around this?
        • Anonymous
          June 28, 2017
          Hi Sagar,Could you please provide us an email address we can get back to you? Looks like the service calls from your workstation is failing and we would need to work with you to get more details.Thanks,Rony
          • Anonymous
            June 29, 2017
            Hi Rony,You can reach me on sagar.bhargava@gmail.com
            • Anonymous
              July 07, 2017
              Thanks Sagar. One of our team members will be reaching out to you.
  • Anonymous
    June 27, 2017
    Does SSMS 2017 and the SQL Diagnostic add on meed to be installed on the server that generated the dump?
    • Anonymous
      June 27, 2017
      Nope. In fact, we recommend to install SSMS on a client workstation and move and upload your dump from client machines.
  • Anonymous
    June 28, 2017
    Seems to fail for SQL 2012 dumps at the analysis point. Works fine for 2016 though. Anyone here successfully processed a 2012 dump?
    • Anonymous
      July 07, 2017
      Hi Andrew,We have customers who are able to successfully process SQL Server 2012 dumps. Could you please provide us with your email address where we can connect with you?Thanks,Rony
  • Anonymous
    June 28, 2017
    Amazing. Tks for the post. ;-)
    • Anonymous
      June 28, 2017
      Thank you !
  • Anonymous
    July 03, 2017
    Great tool! Do you plan to add support for reading SSAS dump files as well? Or does it already have the capability to read the SSAS dump files?Thanks,
  • Anonymous
    July 04, 2017
    Is this applicable only for SQL Server or reporting services also?
  • Anonymous
    July 11, 2017
    Hi Rony,I have written a PowerShell module to consume the API called SQLDiagAPI (It is available on the PowerShell Gallery)It seems that the GUID used to identify the machine in the SSMS application is not the same as in the examples so I have two histories one for SSMS and one for PowerShell which is a shame.I would like them to be the same if I could. What is being used to identify the machine in the SSMS application? I am using MachineGUID from here HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Cryptography\Many ThanksRob
  • Anonymous
    July 15, 2017
    I uploaded a few SQL2012 dumps and it seem to work - upload / analysis / recommendation are all green ticks - but there is an error occurring somewhere because the Status field for each file shows "Failed".
  • Anonymous
    July 20, 2017
    I'm running SQL 2016 SP1 CU3 with SQL 2016 en SQL 2014 databases. I'm checking the recommendations. Why do i get the updates for SQL 2012 and 2014? I'd be interested in updates of SQL 2016, apllicable to my current installation. Mostly hotfixes and/or security fixes. I know that SP1 CU3 is available, i've got it installed ;-)Every release gets a number, ie my current release is 13.0.4435.0. If you take that number to check whether an update, patch, fix or whathave you is applicable, this recommendations screen would be really usefull.
  • Anonymous
    November 27, 2017
    This would be an awesome tool, unfortunately it didn't work too well for me. I uploaded a mdmp file from SQL Server 2016 SP1CU5 and the recommendation it provided was to install CU5...which was already installed...hmmm. But I guess this is still in the preview stages.Will this tool eventually dive deeper into the dump and attempt to figure out the cause and report it back to the uploader?
  • Anonymous
    December 18, 2017
    Is there a way to automate this, to script it out using PowerShell or TSQL?
  • Anonymous
    January 10, 2018
    I have error:This page can’t be displayedMake sure that the web address https://ecs.microsoft.com is correct. Please , help!Kirill
    • Anonymous
      January 10, 2018
      Hello, as of yesterday, this preview service was shutdown. SQL Server customers encountering product issues that generate dumps should contact Microsoft CSS for support and guidance.