How can we improve Azure SQL Database?

Profiler for SQL Azure

747 votes
Sign in
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    anonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    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. SQL Profile is already deprecated in SQL Server, and that’s part of the reason that it doesn’t make sense to bring to SQL DB.

    We are adding DMVs as we expose more of the multi-tenant system functions. has more details on DMVs available today.

    We are looking at exposing XEvents in way that completely cover the need for what profiler is used for in SQL Server.

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


    Sign in
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      • frankmcb commented  ·   ·  Flag as inappropriate

        Let's be serious. You guys are cracked if you think ditching Profiler is a good idea. Profiler is what separated you from Oracle. If I wanted DMVs only... I'd be in the same boat as Oracle with X$ and K$ views. Profiler was the ONLY tool out in the database that could give me a real time visual monitoring of what was REALLY happening with SQL Server. By going to DMVs/XEs, you are going back in time to Oracle 5 when everyone had to write their own homemade scripts. The first thing you did when you went to a new machine was stick in your floppy and put all these cobbled together things that you collected over time. That's what Sybase did for us: it normalized a toolbox... so you knew whatever server you went to had "sp_help", etc... By deprecating Profiler, you are now going back to everyone has to "roll their own". And that is ridiculous. I've been working with SQL Server since 4.21 and Oracle 4. Who in their right mind downgrades a tool set? Note: I worked at Microsoft for a decade in MSIT, MCS, and DPE on some of our largest and most strategic customers worldwide. Think: JoeMa, Ron Soukup, Jim Gray

      • jrstern commented  ·   ·  Flag as inappropriate

        It would be great if you could compete the extended events feature, which Microsoft started in SQL Server 2008 or so and still hasn't completed, but the server-side trace is the gold standard for performance tuning and its absence (or equivalent) in Azure is a huge gap. Tell you what, you have Azure record it for us and "expose" the trace via DMVs, that would be fine with me.

      • mscommunities commented  ·   ·  Flag as inappropriate

        Thanks for the update. The tracing feature is what we are missing the most. It is very hard to diagnose production issues that rely on this feature. Knowing what procs are called with their parameters, duration of call, etc is very important for us. Using the DMV's are great for overall tuning but not when you are debugging a specific issue. If there is a workaround I can do in the interim, I would love to hear it.

      • Jeremy Davis commented  ·   ·  Flag as inappropriate

        Great that this was completed. How do we get more information on where to find it and how to use it?

      • Jamin commented  ·   ·  Flag as inappropriate

        Translation: Do your own DB server. Come back in a few years when we figure this out...

      • Diego commented  ·   ·  Flag as inappropriate

        If SQL Profiler is not considered to be added, it would be good then to add support for extended events (XE) that at some point was deemed as the successor of Profiler/SQL Trace.

      • David 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.

      • Jason commented  ·   ·  Flag as inappropriate

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

      • Vishal 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.

      • ryancrawcour commented  ·   ·  Flag as inappropriate

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

      • Herve 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: 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 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:

      Feedback and Knowledge Base