How can we improve Azure SQL Database?

Profiler for SQL Azure

497 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    anonymousanonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    Dou Dou LuDou Dou Lu shared a merged idea: Enable XEvents for SQL Azure  ·   · 

    In a DB-as-a-service model such as Azure SQL database, DMVs are the mechanism today to monitor what’s going on under the covers. We are adding DMVs as we expose more of the multi-tenant system functions. http://msdn.microsoft.com/en-us/library/windowsazure/ff394114.aspx has more details on DMVs available today.

    We are looking at additional possibilities in the future to expose a tracing mechanism in the future.

    7 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • David LeanDavid Lean commented  ·   ·  Flag as inappropriate

        But the change in pricing model toward perf based charging has put a huge a spotlight on our inability to understand & tune the load on a SQL Azure DB
        As DTU is critical to the pricing. We need a better way understand the load (DTU) we are presenting to the database, to find our worst performing queries & to tune these systems.
        Please consider adding some in-depth performance/query analysis capability.

        Some suggestions.

        1. Ideally Perfmon.

        But if it can’t be done due to security concerns, ie: the need for an SysAdmin connection. Perhaps consider a “Mini” perfmon session that you setup, ie some fixed set of tracing outputs you could gather on each DB on our behalf.

        Perhaps we pick from a menu of the Standard Traces like :-
        1. The Query (including parameters) that hit that database.
        USE: Development. Understanding the TSQL code generated from Entity Framework & equiv. Ensuring that it is not total garbage (which it usually is)
        We take that set of queries, strip the parameters into Test data tables. & use the queries to build & drive VS Load Tests & Unit tests.
        2. The Query string + Load Metrics (CPU, various IO counters, Duration)
        USE: Production & Dev load tests. Tuning worst performing queries.
        Also need to fields that assist with grouping, like Stored proc ID.
        3. Locking
        Something to assist with Deadlock detection.
        (I’d be concerned about profiling at a lock level as this gets huge very fast)
        4. Wait State Analysis.

        Maybe you could run a normal Perfmon session in the cloud on our behalf & pre-filter it to only SPID’s that connect to that customers database.

        Or Performance Data warehouse

        If Perfmon is too much of a security risk, another alternative is an ability to enlist / unenlist into a PDW workload capture.
        These reports greatly assist the understanding of performance bottlenecks & hotspots. In many ways this is even better.

        Had this brilliant tool actually been finished, I’m sure that 90% of SQL customers would be using it today. As it stands very few people do.

        What is it missing?
        1. The ability to uninstall it.
        Today it is one way. You can’t uninstall, reinstall with a different name. Move the PDW to a different server. Etc. This inflexibility scared people who trial it. Ironically it is fairly easy to write you own scripts to do it. And something MSFT could write in ½ day. (with testing etc is 2-3 weeks dev work)

        2. The ability to have multiple databases be tracked into one PDW Server
        Even if they were different schemas in the same DB. Or multiple DB’s on the same machine.
        In a corporate environment the 1:1 feel was high maintenance & inflexible. It would be much easier to cost justify a dedicated SQL Server just for PDW. Or perhaps one for Prod servers & the other for DEV, due to different subnets. That way when you stand up a new solution it just plugs into the PDW monitoring.
        I realise this may create some confusion between System Center & SQL’s Perf DW, but the reports in PDW really do help you to quickly pinpoint design inefficiencies. SysCenter just tells you SQL is the problem, but not how to fix it.

        It is quite frustrating that this effort so close to a real solution in the first release, But instead of finishing it in the next release, it was abandoned.

      • JasonJason commented  ·   ·  Flag as inappropriate

        What are our options for debugging deadlocks without official Profile support?

      • Vishal MalhotraVishal Malhotra commented  ·   ·  Flag as inappropriate

        Along with all the other benefits SQL Profiler is also a critical tool for debugging. It provides the insight on which command failed.

      • ryancrawcourryancrawcour commented  ·   ·  Flag as inappropriate

        does the new stuff added to the SQL Azure management dashboard available in your browser not provide enough?

      • Herve RoggeroHerve Roggero commented  ·   ·  Flag as inappropriate

        Profiler works by using server-side tracing stored procedures. Support for server-side tracing would be important for corporations interested in auditing data changes. Regarding performance I would like to point to an open-source project I created that helps filling the gap: enzosqlbaseline.codeplex.com. It can show you the number of packets returned, CPU consumption, Duration of statements and so on. However, SQL Profiler is critical for advanced troubleshooting needs.

      • André van de GraafAndré van de Graaf commented  ·   ·  Flag as inappropriate

        I use the SQL Profiler to understand what happens on my SQL server, to see what kind of queries are generated by my applications and to analyze my queries to improve performance. On the SQL Azure server I have an additional reason to use the SQL Profiler. In the pricing model for SQL Azure you have to pay for the data transfer sizes of your queries. The SQL Profiler can give you insight in the data transfered from the server to the client. see some more detail on: http://www.keepitsimpleandfast.com/2010/01/my-first-experiences-with-sql-azure-sql.html

      Feedback and Knowledge Base