Profiler for SQL Azure
Enable event tracing for SQL Azure database.
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.
David Lean commented
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.
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.
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.
What are our options for debugging deadlocks without official Profile support?
Vishal Malhotra commented
Along with all the other benefits SQL Profiler is also a critical tool for debugging. It provides the insight on which command failed.
does the new stuff added to the SQL Azure management dashboard available in your browser not provide enough?
Gayomard Mehta commented
SQL Profiler is very beneficial as it gives us an idea of how it is being executed...
Herve Roggero commented
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 Graaf commented
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