Performance Tuning - Pro SQL Database for Windows Azure - page 218

Table 11-2. Dynamic management views used for performance tuning

DMV Comments

sys.dm_exec_connections Partially supported. Returns the list of connections established in SQL Database.
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 Database.
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 Fully supported. Returns the text of a SQL batch.
sys.dm_exec_text_query_plan Fully supported. Returns 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.

Note

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
Database 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.

Note

For example, establish a connection to SQL Database, 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 11-1 , the session ID is 144: you can see it both on the
selected tab and in the status bar at the bottom.