Database Reference
In-Depth Information
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 12-2.
Table 12-2.
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.
