CloudTip #14-How do I get SQL Profiler info from SQL Azure?



Your application is running slow. You need to find out what’s going on. If you’ve used SQL Profiler on a local database you might be familiar with how you can capture a trace of database activity and use it to figure out where your resources are going. The visibility makes it MUCH easier to tune a database than sorting thru a bunch of code. The question is, what do you do when you’re moving an app to the cloud?

If you’ve wondered how you can get Profile information from SQL Azure, the new online management portal for SQL Azure has been updated with design, deployment, administration and tuning features built in. The Overview screen provides quick links to the different areas of the portal, as well as easy links to help information from msdn online. You can get to the portal either by going to the Windows Azure management portal on http://windows.azure.com and after signing in going to the database section and clicking Manage, or simply browsing to your database name – https://<myserver>.database.windows.net where you substitute your database server’s name for <myserver>.

image

When I log in I can see my databases and get information about size, usage as well as the ability dive into specific usage. From there I can go into designing the schema, functions and code around my database. If I swap over to the admin page though, I have visibility into not just database size and usage, but also a link to query performance. Clicking this takes me to where I can see profile data from queries.

image

I can sort and see which calls to the database are most frequent as well as most expensive in terms of resource usage. Further I can select one and dive even deeper to see the execution plan and statistics around the calls. This information is key to making decisions on indexes and design of a well performing database.

image

In the query plan I can look for table scans or other expensive operations and if it make sense determine whether additional indexes would be useful.

image

Nice!

Digg This
Comments (7)

  1. Kasi says:

    Hi Benkov,

    This is an excellent article you have published, it helped me lot. And also I need another suggestion from you. Here we are getting all the TSQL's , my requirement is to get all the TSQL's in sequence in a particular time and extract them and need to run those n number of times, n+100, n+1000 in parallel to test the performance. I tried to import the SQL's but it looks like we can only copy one by one and also not in sequence. Please suggest me. Thanks in advance.

    Kasi

  2. KenS says:

    But how would I find out what was most recently run?  One of the prime uses I have of SQL Profiler is to get a trace of what actually got executed from a web page. How can I do that?

  3. Sam High says:

    Thanks for the education… New to SQL Azure.  On LocalDb I use ExpressProfiler.exe and was looking for the Azure equivalent. I tried to trace with this tool but got error executing sp_trace_start proc which does not exits in Azure.

  4. davidw says:

    not really useful, I saw all queries show CPU 0 and duration 0, does that means there is nothing to improve?

  5. daleh says:

    This was really useful.  How is SQL profiling done now that the SilverLight UI is no longer available in SQL Azure V12?

  6. Rahul says:

    Does this still work?  I get a 503 error when trying to visit my server's URL.

  7. Peter Dolkens says:

    New url is portal.azure.com I believe