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

2/12/2011 4:05:53 PM
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
sys.dm_exec_connectionsReturns 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_planFully supported. Returns the XML execution plan of a SQL query or a batch.
sys.dm_exec_query_statsFully supported. Returns aggregate performance information for cached query plans.
sys.dm_exec_requestsFully supported. Returns information about the statements being executed by SQL Azure.
sys.dm_exec_sessionsPartially 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_textReturns the text of a SQL batch.
sys.dm_exec_text_query_planReturns the execution plan in text format for a SQL query or batch.


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.


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:


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
login_time2010-04-22 16:43:30.557The login time of the session. Note that sessions can be reused over time, through connection pooling. This time represents the last successful login.
host_nameDEVDSK01The machine name that made the connection to the SQL Azure database.
program_nameSSMSThe application name that is executing the statement on the client workstation.
host_process_id7720The 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_time15The CPU time, in milliseconds, consumed by the SQL statements since the connection was established.
memory_usage2Number of 8KB bytes consumed by the connection so far.
total_elapsed_time32The 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.
reads1Number of physical reads.
writes1Number of physical writes.
logical_reads322Number of logical reads.
row_count50Number of rows returned.
original_login_nameMyTestLoginThe 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.
Video tutorials
- How To Install Windows 8

- How To Install Windows Server 2012

- How To Install Windows Server 2012 On VirtualBox

- How To Disable Windows 8 Metro UI

- How To Install Windows Store Apps From Windows 8 Classic Desktop

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
programming4us programming4us