Udostępnij za pośrednictwem

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

  • Artykuł

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 https://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

  • Anonymous
    May 24, 2012
    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

  • Anonymous
    June 13, 2013
    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?

  • Anonymous
    October 19, 2013
    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.

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

  • Anonymous
    July 10, 2015
    This was really useful.  How is SQL profiling done now that the SilverLight UI is no longer available in SQL Azure V12?

  • Anonymous
    December 28, 2015
    Does this still work?  I get a 503 error when trying to visit my server's URL.

  • Anonymous
    January 11, 2016
    New url is portal.azure.com I believe