How to Identify Performance Bottlenecks on Azure SQL Database
Microsoft Windows Azure SQL Database is similar to an on-premise Microsoft SQL Server and extends the SQL Server database capability to the cloud. Azure SQL Database lets you provision and deploy relational database solutions to the cloud, including many benefits such as, rapid provisioning, scalable, high availability and minimal maintenance overhead.
Maintaining Azure SQL database on the cloud is easy to manage, with less effort, because of the complete infrastructure provided by Microsoft System Center. Though the Infrastructure is managed by Microsoft, you as a DBA, developer, or member of the application support team always need to monitor the health of database.
In today's article I'll cover how we can identify a performance bottleneck on Windows Azure SQL Database.
Performance Bottleneck Consideration
Developing and maintaining applications using Azure database is a complete paradigm shift. It requires a change in the mindset when the application is under development because a developer or DBA should keep one fact in mind--there is no control on Azure SQL Database's physical administration and manipulation of resources.
On one side of coin, it allows you to manage the Azure database with less effort because it helps you to reduce maintenance overhead. On the other hand, you should more cautious when designing and developing any application for the cloud. If you don't follow database schema design and development best practices it may reduce application or database performance.
There are multiple ways to identify database bottlenecks but before exploring them you should be aware that there are some limitations to finding performance bottlenecks on Windows Azure SQL Server compared to an on-premise SQL Server.
Windows Azure SQL Server doesn't provide any method to set/change server options, including SQL trace flags. Tools and utilities, like SQL Server Profiler and DTA (Database Tuning Adviser), are not even provided to identify performance or other bottleneck issues. I know, as a DBA, developer, or member of the application support team, SQL Profiler and DTA are favorite tools to find any issues in an on-premise SQL Server database. As soon as you realize this limitation, the first thing to strike your mind will be how will you find the root cause of performance bottlenecks or any other issue?
Here is the solution; the best way to identify performance and blocking issues on Windows Azure SQL Database is DMVs.
DMVs were introduced in SQL Server 2005 for the first time and are also available in all newer versions of on-premise SQL Server. Windows Azure SQL Server also has a set of DMVs, although there's not as many compared to on-premise SQL Server, but the set does provide enough in-sight into the Azure SQL Server database including database state, blocking issues by a long running query or resource bottleneck, and inefficient query plan, etc. It's a very powerful tool and is divided in different categories based on operation or event.
These Dynamic management views are broadly divided into 3 categories below:
Database and Index related DMVs: sys.dm_db_partition_stats, sys.dm_db_wait_stats, sys.dm_db_index_operational_stats, sys.dm_db_index_physical_stats, sys.dm_db_index_usage_stats, and sys.dm_db_missing_index_details, etc.
Execution related DMVs: sys.dm_exec_sessions, sys.dm_exec_query_stats, sys.dm_exec_connections, sys.dm_exec_cached_plans, sys.dm_exec_procedure_stats, sys.dm_exec_query_memory_grants, sys.dm_exec_sql_text, sys.dm_exec_text_query_plan and sys.dm_exec_requests, etc.
Transaction related DMV's: sys.dm_tran_session_transactions, sys.dm_tran_locks, sys.dm_tran_database_transactions and sys.dm_tran_active_transactions.
You should have VIEW DATABASE STATE permission to query these DMVs.
Apart from the above set of DMVs there are two important views, sys.database_connection_stats and sys.event_log available in the master database, which helps to identify critical issues in Azure SQL Database, i.e. deadlock, throttling and connection related events. Users with permission to access the master database have read-only access to query these views.
You can get details of all these views (DMVs and other views) on MSDN.
One additional way to get detailed information about executed queries, including performance, is Windows Azure Platform Management Portal. After connecting to your Azure subscription, browse Azure SQL Database and you can find a Query Performance link under Administration, which gives execution details of each query executed on the database.
In the next section I'll cover how to identify some of the common performance and blocking issues with the help of DMVs and other views.
How to Identify a Performance Bottleneck
During your day to day support activity as a DBA or member of the application support team, it is expected that you should closely monitor events that hit database performance.
Windows Azure SQL Server Database view, sys.event_log, holds details of performance bottleneck events like database connections, connection failures, deadlocks and throttling. Sys.event_log view events can be identified by Event category, Event type and Event sub type.
Deadlock and throttling, which are captured in sys.event_log, are top priority events to be monitored. Let's have a quick look at how events in sys.event_log help you get information on any deadlock or throttling.
To identify a deadlock you can query the sys.event_log view. This view also contains information for many events, but can query deadlock specific events.
In the query below, if you pass event type value "deadlock" as a filter, it returns all records that cause deadlocks on Azure SQL Database server.
The output of the above query returns records for all available databases on that server. To get deadlock detail for a specific database, you can use the database name as a filter in query:
After executing above query it returns all deadlocks that occurred on the given database.
There are columns related to date and time, like start time and end time. You can use these columns to identify exactly when a deadlock occurred.
Throttling is a mechanism used by Azure SQL Database to prevent the server from becoming overloaded and unresponsive. It ensures that all users receive an appropriate share of resources and that no one monopolizes resources; Azure SQL Database may close or "throttle" subscriber connections under certain conditions.
You can find all throttle events in the sys.event_log view. Use the query below to identify any such event using different filters, like database name or time interval.
One thing you should be aware of is the data retention period; as per the latest policy the data in this view is retained for a maximum of 30 days, or possibly less depending on the number of databases in the logical server and the number of unique events each database generates.
Excessive Resource Consumption
There are many other reasons that may hit the performance of Windows Azure SQL Database and one of them is badly written queries, which introduce excessive recompiles or inefficient query plans. Excessive recompilation or an inefficient query plan turns into excessive resource consumption and all these details can be identified by DMVs.
Let's understand how to identify some of the common performance issues faced by any DBA or developer.
In this example, with the help of sys.dm_exec_query_stats and sys.dm_exec_sql_text DMVs, you can find the top 10 queries that are consuming maximum CPUs on the server.
Here queries are aggregated based on its hash value, so logically equivalent queries are grouped by their cumulative resource consumption.
In another example, you can identify I/O issues with the help of sys.em_exec_query_stats DMV.
In the above examples we have seen how available sets of views can help to identify the type and reason of issues on Azure SQL Database; after knowing the root cause of an issue you can recommend all possible solutions to resolve them.
Overall we have explored how DMVs and other views provide most of the critical event details and how you can extract stored information. As a DBA or developer for Azure applications you can use a given set of views and raise the alert if anything abnormal with the database health is encountered.
Currently there are a limited number of DMVs enabled for Azure SQL Database and you should always visit Windows Azure SQL Database on MSDN to get the latest updates.