locked
Profiling of SQL Azure Database RRS feed

  • Question

  • Hi

    I Want to Profile the SQl Azure database. I was trying to hook the SQL azure with the SQL 2005 Profiler so that I can trace the event wise query duration, CPU , disk etc.but while connecting the SQL Azure with the SQL profiler it is showing Sys admin rights rights required and SQl azure is not allowing to provide the Sysadmin rights.

    Please tell me how I can trace the event wise queries fired on the data base. Please help, it is urgent.

    Thanks,

    Wednesday, February 12, 2014 6:57 AM

Answers

  • you can't use SQL Profiler to profile an Azure SQL Database.

    apart from the portal as Edward pointed above, you may also use the DMVs, see

    Monitoring Windows Azure SQL Database Using Dynamic Management Views

    • Edited by JuneTEditor Wednesday, February 12, 2014 7:57 AM
    • Proposed as answer by Fanny Liu Thursday, February 13, 2014 2:52 AM
    • Marked as answer by Fanny Liu Tuesday, February 18, 2014 11:35 AM
    Wednesday, February 12, 2014 7:57 AM
    Answerer
  • Hi,

    You can find profile information in the database section of windowsazure.com portal. Have a look at this blog for some more details.

    Edward 

    That blog post is misleading - the portal doesn't show you "profiler" data, it shows you a dump from the query cache.  You can do this yourself via TSQL using the DMVs.

    The SQL Profiler runs a trace, which is not supported in WASD.  No realtime information is available like this, although the infrastructure appears to exist for Extended Events.  There were plans afoot to release a cut-down version of these, although nothing has been announced as yet.

    I'm afraid you're stuck with two options:

    1. Look at the query cache before and after the event, to try and measure the stats of each query

    2. Run a query at timed intervals to capture the contents of dm_exec_requests

    • Proposed as answer by Fanny Liu Thursday, February 13, 2014 2:52 AM
    • Marked as answer by Fanny Liu Tuesday, February 18, 2014 11:35 AM
    Wednesday, February 12, 2014 9:54 AM

All replies

  • Hi,

    You can find profile information in the database section of windowsazure.com portal. Have a look at this blog for some more details.

    Edward 

    Wednesday, February 12, 2014 7:47 AM
  • you can't use SQL Profiler to profile an Azure SQL Database.

    apart from the portal as Edward pointed above, you may also use the DMVs, see

    Monitoring Windows Azure SQL Database Using Dynamic Management Views

    • Edited by JuneTEditor Wednesday, February 12, 2014 7:57 AM
    • Proposed as answer by Fanny Liu Thursday, February 13, 2014 2:52 AM
    • Marked as answer by Fanny Liu Tuesday, February 18, 2014 11:35 AM
    Wednesday, February 12, 2014 7:57 AM
    Answerer
  • Hi,

    You can find profile information in the database section of windowsazure.com portal. Have a look at this blog for some more details.

    Edward 

    That blog post is misleading - the portal doesn't show you "profiler" data, it shows you a dump from the query cache.  You can do this yourself via TSQL using the DMVs.

    The SQL Profiler runs a trace, which is not supported in WASD.  No realtime information is available like this, although the infrastructure appears to exist for Extended Events.  There were plans afoot to release a cut-down version of these, although nothing has been announced as yet.

    I'm afraid you're stuck with two options:

    1. Look at the query cache before and after the event, to try and measure the stats of each query

    2. Run a query at timed intervals to capture the contents of dm_exec_requests

    • Proposed as answer by Fanny Liu Thursday, February 13, 2014 2:52 AM
    • Marked as answer by Fanny Liu Tuesday, February 18, 2014 11:35 AM
    Wednesday, February 12, 2014 9:54 AM
  • Hi,

    Extended Events is the feature which will help you to achieve what you are looking for. Let me brief something about this feature.

    Extended Events once configure will write all the traces into two ways :

    1. Blob Storage : In this approach, you need Blob Storage Account on Azure & You need to create a Blob Container. In this case basically Extended Events will write all the trace information into file with extension .xel and it will write file directly on cloud i.e. Blob Storage Container

    2. Ring-Buffer : In this approach, you need to simply configure Extended Events where you can mention buffer size to hold trace information & in order to read trace information you need to write some queries which will return XML format data & then you can convert XML into readable table format

    I would suggest you to go with option 2 : Ring-Buffer as this one is easiest & does not require any blob storage. Dis-advantage for option 1 is that once .xel file is created on blob storage our SSMS cannot directly read that from cloud unless you download manually & open in SSMS

    Kindly refer below link for steps to be performed in detail :

    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-xevent-code-ring-buffer 

    Friday, November 18, 2016 6:15 PM