SQL Azure : Tuning Techniques (part 1) - Dynamic Management Views

Let's dive into the specifics of performance tuning, keeping in mind what you've learned so far. You start by looking at database tuning capabilities and then move up the stack, all the way to the client library making the actual SQL call.

1. Dynamic Management Views

SQL Azure provides a few handy system views called dynamic management views (DMVs) that are also available in SQL Server. SQL Azure exposes a subset of the DMVs, but all those related to query execution are available. SQL Azure supports the DMVs listed in Table 1.

Table 1. Dynamic management views used for performance tuning

DMV	Comments
sys.dm_exec_connections	Returns the list of connections established in SQL Azure. Note that some of the interesting columns, such as client_net_address (returning the client machine's MAC address), aren't available.
sys.dm_exec_query_plan	Fully supported. Returns the XML execution plan of a SQL query or a batch.
sys.dm_exec_query_stats	Fully supported. Returns aggregate performance information for cached query plans.
sys.dm_exec_requests	Fully supported. Returns information about the statements being executed by SQL Azure.
sys.dm_exec_sessions	Partially supported. Returns the current session opened along with performance information about that session. However, it doesn't return last-login information, such as the last_successful_logon column.
sys.dm_exec_sql_text	Returns the text of a SQL batch.
sys.dm_exec_text_query_plan	Returns the execution plan in text format for a SQL query or batch.

NOTE

Although queries against some of these views can run when you're connected to the master database, they don't return the information you're looking for unless you connect to the database that your application is running against. Also, a user must have VIEW DATABASE STATE permission to see all executing sessions on the database; otherwise, the user sees only the current session.

If you're looking for performance metrics for a SQL statement and you can isolate the statement to a unique database connection, or the statement is no longer executing, the dm_exec_sessions DMV is for you. This is one of the system views that provides performance metrics such as CPU time and duration. However, this DMV accumulates the performance metrics over all the statements executed through the same connection. So, in order to test a database query and retrieve performance metrics of that query alone, you need to establish two connections: one to execute the query, and another to read the performance metrics so as not to interfere with the performance data that SQL Azure has collected.

NOTE

You need to establish two connections using the same login name, or you can't retrieve the performance metrics of the SQL statement you're trying to tune.

For example, establish a connection to SQL Azure, and run the following SQL query:

SELECT TOP 50 * FROM sys.indexes

Note your session id; it's found on the status bar in Microsoft SQL Server Management Studio. You can also find it on the query tab, in parentheses. For example, in Figure 1, the session id is 144: you can see it both on the selected tab and in the status bar at the bottom.

Figure 1. Capturing a statement's performance metrics

Next, open another query window, which opens a new connection in SQL Azure. Run the following query (see Figure 12-1), and make sure to specify the session id being investigated:

SELECT
   login_time,
   host_name,
   program_name,
   host_process_id,
   cpu_time,
   memory_usage,
   total_elapsed_time,
   reads,
   writes,

logical_reads,
   row_count,
   original_login_name
FROM sys.dm_exec_sessions
WHERE session_id = 176           // replace with your session_id

This statement returns important performance metrics for your previous SQL statement, as explained in Table 2.

Table 2. Selected columns from sys.dm_exec_sessions

Metric	Value	Comment
login_time	2010-04-22 16:43:30.557	The login time of the session. Note that sessions can be reused over time, through connection pooling. This time represents the last successful login.
host_name	DEVDSK01	The machine name that made the connection to the SQL Azure database.
program_name	SSMS	The application name that is executing the statement on the client workstation.
host_process_id	7720	The Windows Process ID (PID) that is executing the statement on the client workstation. You can view the PID of your applications in Task Manager in Windows.
cpu_time	15	The CPU time, in milliseconds, consumed by the SQL statements since the connection was established.
memory_usage	2	Number of 8KB bytes consumed by the connection so far.
total_elapsed_time	32	The duration of the statement in milliseconds. This includes the time to execute the statement and the time it takes to return the data to the client machine.
reads	1	Number of physical reads.
writes	1	Number of physical writes.
logical_reads	322	Number of logical reads.
row_count	50	Number of rows returned.
original_login_name	MyTestLogin	The login name of the user who successfully connected.

At this point, you need to be aware of a point that is very important for performance tuning. The cpu_time is perhaps the best metric you can use to determine how long a statement takes to execute in SQL Azure. The total_elapsed_time can be misleading. Elapsed time represents the time it takes for SQL Azure (or SQL Server) to fetch the data and return all the data to the client. So, if your client is slow at displaying data, SQL Azure slows down to match the speed of the client; the slowdown is a function of TCP (Transmission Control Protocol) programming and has nothing to do with SQL Azure. As a result, total_elapsed_time shows the entire time it takes to execute the statement and the client time necessary to finish fetching the data. The total_elapsed_time is the same than the Duration measure returned by SQL Profiler.