Tuning SQL Azure Databases – Part 2

This article is the second part of a two-part series on how to tune SQL Azure databases. In Part 1, I discussed how to tune a particular query by inspecting execution plans and statistics. In this article, I will discuss how to tune a SQL Azure database using dynamic management views (DMVs) and dynamic management functions (DMFs).
DMVs and DMFs have proven to be an effective tool for database performance tuning. They can be used to monitor and manage both SQL Server databases and SQL Azure databases. In this article, I will focus on tuning SQL Azure databases using information that is made available through DMVs and DMFs. Before I discuss this, I want to first tell you about the DMVs and DMFs available in SQL Azure and how they differ from those in SQL Server.

DMVs and DMFs in SQL Azure

DMVs and DMFs return information that represents the internal state of the server, which is useful in diagnosing problems and tuning the server. Initially, SQL Azure did not support DMVs, but Service Update 1 (February 2010) added support for a few of them. Apparently, the support for SQL Azure DMVs and DMFs is being enabled in phases.
At this time, SQL Azure has partial support for DMVs and most of them are focused on tuning at a query level. Because SQL Azure does not provide access to the underlying physical infrastructure, it does not support DMVs that return physical infrastructure information. Table 1 summarizes the DMVs that are currently available (i.e., available as of June 1, 2011). Table 2 provides the same summary for DMFs.

The DMV’s shown in Table 1 can be broadly classified into three categories:

1. Execution-related DMVs
   1. dm_exec_requests
   2. dm_exec_sessions
   3. dm_exec_connections
   4. dm_exec_query_stats
2. Transaction-related DMVs
   1. dm_tran_database_transactions
   2. dm_tran_active_transactions
   3. dm_tran_session_transactions
   4. dm_tran_locks
3. Database-related DMVs
   1. dm_db_objects_impacted_on_version_change
   2. dm_db_partition_stats

Now, let’s see how these DMV’s differ from those available in SQL server. In SQL Server, DMVs and DMFs are used to glean information at the instance level, whereas in SQL Azure, DMVs and DMFs are used to show information pertaining to the current tenant because SQL Azure has a multi-tenant architecture. This is done by filtering out information related to other tenants on the same shared server. SQL Azure DMVs use the same schema as their SQL Server counterparts, but in SQL Azure, columns return NULL if the information exposed would be server information at the instance level.

To view the available DMVs in your SQL Azure environment, you can run the following T-SQL code:

select * from sys.all_views where name like '%dm%';

 All SQL Azure DMVs are user database scoped, which means you will run them on user databases with a principal server login or a login that has VIEW DATABASE STATE permission.
Let’s now use some of the DMVs and DMFs to glean information about currently executing queries and recently executed queries. Specifically, let’s focus on finding:

• CPU-intensive queries
• Longest-running queries
• Logical I/O-intensive queries
• Physical I/O-intensive queries

How to Find CPU-Intensive Queries

You can find the top 10 CPU-intensive queries by sorting queries by total worker time. Information about total worker time for queries is available in the sys.dm_exec_query_stats DMV, but this DMV gives the information for the corresponding sql_handle and not the SQL query text. So, to find the SQL query text for the corresponding sql_handle, you need to use the sys.dm_exec_sql_text DMF. Listing 1 show the query that uses the sys.dm_exec_query_stats DMV and the sys.dm_exec_sql_text DMF in a cross apply operation to find the top 10 CPU-intensive queries.

Note that the query in Listing 1 will list the top 10 CPU-intensive queries over all executions, so the query itself must not be CPU intensive. If you want to find the CPU-intensive queries for a single execution, you can divide the qst.total_worker_time column values by the qst.execution_count column values, call it avg_worker_time, and then sort the table on this column.

How to Find the Longest-Running Queries

You can find the top 10 longest-running queries by sorting queries by average elapsed time. The average elapsed time of a query can be calculated by dividing the total elapsed time by the execution count of a query. Information about the total elapsed time and execution count of queries is available in the sys.dm_exec_query_stats DMV, but this DMV gives the information for the corresponding sql_handle and not the SQL query text. So, to find the SQL query text for corresponding sql_handle, you use the sys.dm_exec_sql_text DMF, as the query in Listing 2 shows.

How to Find Logical I/O-Intensive Queries

You can find the top 10 logical I/O-intensive queries by sorting the queries by total logical I/O. Total logical I/O can be calculated by adding together the total number of logical reads and total number of logical writes of a query. Information about the total logical reads and total logical writes of queries is available in the sys.dm_exec_query_stats DMV, but this DMV gives the information for the corresponding sql_handle and not the SQL query text. So, to find the SQL query text for the corresponding sql_handle, you can use the sys.dm_exec_sql_text DMF, as the query in Listing 3 shows.

Note that the query in Listing 3 will list the top 10 logical I/O-intensive queries over all executions. (The query itself might not be a logical I/O query.) If you want to find the logical I/O-intensive queries for a single execution, you can divide the total_logical_IO column values by the qst.execution_count column values, call it avg_logical_IO, and then sort the table on this column column.

How to Find Physical I/O-Intensive Queries

You can find the top 10 physical I/O-intensive queries by sorting queries by average physical reads. Average physical reads can be calculated by dividing the total number physical reads by the execution count of a query. Information about the total number of physical reads and execution counts of queries is available in the sys.dm_exec_query_stats DMV, but this DMV gives the information for the corresponding sql_handle and not the SQL query text. So, to find the SQL query text for the corresponding sql_handle, you can use sys.dm_exec_sql_text DMF, as Listing 4 shows.

Proactive Performance Tuning and Monitoring

You can use the four queries I’ve shown you to help pinpoint long-running and misbehaving queries. You can then optimize them, to make your system run smoother and meet your performance expectations. However, you must set realistic performance expectations, as a SQL Azure database does not offer the same level of performance as that of an on-premise SQL Server database because of reasons such as:

• Multi-tenant architecture
• Different hardware
• Network latency

To get the best performance possible, you need to proactively tune your SQL Azure database. Although the cloud takes care of lot of things, it does not solve performance problems due to a slow running query or a performance degrade due to a chatty application. SQL Azure databases require the same proactive performance tuning and monitoring as SQL Server databases.  Unfortunately, SQL Azure does not currently support tools such as SQL Server Profiler and Index Tuning Wizard. Their support will hopefully be added in the next service updates and the Good news is that a DBA will be using the same tools (DMV’s, Execution plan, STATISTICS) to tune a SQL Azure Database.  Thus, this two-part series aimed to throw light on contemporary techniques that you can use until those tools are added. Those techniques include using execution plans, statistics, DMVs, and DMFs to obtain the information you need to find and solve problems.