SQL Server Diagnostics (Preview)


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 (39)

  1. prince rastogi says:

    Excellent. I love these feature Analyze Dump and view recommendations.

  2. Devendra Singh says:

    Nice stuff

  3. Umara1005 says:

    Really liked the Analyze dump feature great

  4. Brent Ozar says:

    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.)

    1. 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

      1. Hi Brent,

        We are working to fix the installation experience when the OS is installed outside C: drive.

        Thanks,

        Rony

        1. senthil says:

          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 ?

        2. Kenny says:

          Actually doesn’t work when I have the OS on the C: drive

  5. John Songy says:

    Great feature, however, the recommendation was for a KB article that I cannot find. Is it public? KB3165545

  6. Sivakumar says:

    I have downloaded latest SSMS 17.1, but i couldn’t see this feature on it.

    1. This is a SSMS add-on. The download link for the Diagnostics preview is at the top of the blog post.

      1. Sivakumar says:

        Thanks for the reply, let me try and get back if any help required.

      2. Please download the extension from here : SQL Server Diagnostics (Preview)

        https://www.microsoft.com/en-us/download/details.aspx?id=55516

  7. Eli says:

    Which tool you recommend for LZ4 compression?

  8. Sagar Bhargava says:

    I tried using this add-in with SSMS 17.1 and it is stuck ‘Loading Regions’. Is there anything that I need to change?

    1. 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.

      1. Sagar Bhargava says:

        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 sites
        2. In IE Settings, on the Advanced tab, deselect the Do not save encrypted pages to disk setting under the Security section

        I 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?

        1. 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

          1. Sagar Bhargava says:

            Hi Rony,

            You can reach me on sagar.bhargava@gmail.com

            1. Thanks Sagar. One of our team members will be reaching out to you.

  9. Frank Gill says:

    Does SSMS 2017 and the SQL Diagnostic add on meed to be installed on the server that generated the dump?

    1. Nope. In fact, we recommend to install SSMS on a client workstation and move and upload your dump from client machines.

  10. Andrew Pruski says:

    Seems to fail for SQL 2012 dumps at the analysis point. Works fine for 2016 though. Anyone here successfully processed a 2012 dump?

    1. 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

  11. Caroline Lavecchia says:

    Amazing. Tks for the post. 😉

  12. Shabnam Watson says:

    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,

  13. Sandip Kumar says:

    Is this applicable only for SQL Server or reporting services also?

  14. Rob Sewell says:

    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 Thanks

    Rob

  15. Sean Holm says:

    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”.

  16. Reitse Eskens says:

    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.

  17. Ryan says:

    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?

  18. Vuk says:

    Is there a way to automate this, to script it out using PowerShell or TSQL?

  19. Kirill Panov says:

    I have error:
    This page can’t be displayed
    Make sure that the web address https://ecs.microsoft.com is correct.
    Please , help!
    Kirill

    1. 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.

Skip to main content