SQL Server Diagnostics (Preview)


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

  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

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

Skip to main content