CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

CSS SQL Azure Diagnostics tool released

CSS SQL Azure Diagnostics tool released

  • Comments 4

I am happy to announce that CSS SQL Azure Diagnostics (CSAD) has been released. Since you cannot use PSSDiag/SQLDiag against SQL Azure, I decided to develop this tool to shorten the data collection process when troubleshooting SQL Azure issues.  You can point CSAD to your SQL Azure instance, provide the appropriate credentials and will then be presented with some good summary data about your instance.  Since I leverage the standard ReportViewer control, you can also export the reports to a number of different formats.  This makes it easy to share the reports with either your colleagues or CSS.  In addition, CSAD is a Click-Once application, so it has a very light installation and it always checks for the latest version.  (For some more details on the installation, see the very end of this post).

You can download it from http://dsdazureevanba.blob.core.windows.net/csssqlazuredeploy/publish.htm or click on the link above.

Let’s walk through using CSAD:

1)  Enter your server and user information

image

2)  Click “GO”

image

That’s it!

Now for the more interesting part of this post and walk through the results you get back…

The first thing you will see is a general information section:

image

Although there are just a couple of things in this section right now, it is a key area.  Here is where you can see your database size, plus CSAD runs some tests to see if you are running into any known service issues that have not yet been addressed.  As CSAD continues to develop, it will add more information here like SKU, version, etc.

Next you will see the first of the core tables – Top 10 CPU consumers:

image

This shows your queries that are consuming the most CPU, plus some pertinent information about these queries.  You can use this table to figure out which queries likely need some tuning.

Next, you will see your longest running queries:

image

If you continue down through the pages, you will then see your top logical and physical I/O consuming queries:

image

image

These last two tables should give you a pretty good idea on which queries are missing an index or have an incorrect index.  (NOTE: One of the next features I am adding is the ability to identify the missing index and generate the appropriate TSQL to create the index).

Lastly, I want to point out that you have the ability to either print or export this report:

image

The beauty of CSS SQL Azure Diagnostics is that it doesn’t use any inside information.  None – everything that is pulled is pulled from public DMVs.  In fact, and you can test this by unchecking “SQL Azure database” at the top of the page, you can run the same exact queries against an on-premises instance of SQL Server and get the same exact data back.  This is going to be one of the tenets of CSAD going forward – it will always only use queries and information that anybody can use against any SQL Server instance in the world – be it on-premises or in the cloud.  (NOTE:  Although the DMVs used are public, I don’t yet have them documented in the tool itself.  I promise to do that in a near-term release, though.  In addition, when I document the DMV queries, I will add a lot more information on the different columns in each table to help you interpret them).

INSTALLATION DETAILS

1)  CSAD does require the installation of the ReportViewer 2010 and the .NET 4.0 Client Profile.  It should check for both components on install, but you can also install them separately:

Download details- Microsoft Report Viewer 2010 Redistributable Package

Download details- Microsoft .NET Framework 4 Client Profile

2)  No reboot is necessary

3)  Each time CSAD starts up, it checks the Azure blob storage location for a newer version and updates itself if necessary

4)  You can uninstall it by going to Control Panel –> Add/Remove Programs

5)  I have already seen a few isolated instances where the ReportViewer control wouldn’t install.  If you run into that scenario, just install it separately using the link above

P.S.  Thanks to Chris Skorlinski for providing me with the original DMV queries.

Leave a Comment
  • Please add 2 and 4 and type the answer here:
  • Post
  • how can I install (silent)     ReportViewer 2010 and the .NET 4.0 Client Profile ? thanks            

  • First of all, thanks for a very helpful tool. It's been invaluable for tuning and troubleshooting.

    Over what time period is data collected? Since each SPROC was created? Since last restart? Is there any way to flush the data, for example after an index is added or a procedure modified?

    Also, why (on page 1) is Creation Time sometimes later than Last Execution Time? What does Creation Time represent?

  • Espinte,

    If you are doing them as part of the click-once install, I don't think you can make them silent.  However, you should be able to use the standard .NET installer switches.  blogs.msdn.com/.../490167.aspx is a pretty good post talking about the options.

    Evan

  • This is great Eric! We have added these queries as well as a few others to our Cotega performance monitoring service (cotega.com/.../analyze_database) for people that want to do this but are not able to install your tool.

Page 1 of 1 (4 items)