SQL Server dynamic management view (DMVs)

DMV requires View SERVER state permissions in a locally deployed SQL Server

The DMV associated with the transaction
Sys.dm_tran_active_transactions: Returns information about the transaction of your current logical database.
Sys.dm_tran_database_transactions: Returns information about a user's database-level transactions.
Sys.dm_tran_locks: Returns information about the lock Manager resource that is currently active. One row for each currently active request issued to the lock manager that has been granted a lock or is waiting for a lock to be granted. The columns in the result set are broadly divided into two groups: a resource group and a request group. The resource group describes the resource in which the lock request is being made, and the request group describes the lock request.
Sys.dm_tran_session_transactions: Returns information about the associated transaction and session.

and enforcement related to the DMV
Sys.dm_exec_connections: Returns information about connections that are established with Microsoft SQL Azure database.
Sys.dm_exec_query_plan: Returns the display plan for the batch query specified by the plan handle in XML format. The schedule specified by the plan handle can be cached or in the executing state.
Sys.dm_exec_query_stats: Returns aggregate performance statistics for the cached query plan. Each query statement in the cache plan corresponds to a row in that view, and the lifetime of the row is associated with the plan itself. When you delete a plan from the cache, the corresponding row is also removed from the view.
Sys.dm_exec_requests: Returns information about each request that is executed within Microsoft SQL Azure server.
Sys.dm_exec_sessions: Returns information about user connections and internal tasks for all activities.
Sys.dm_exec_sql_text: Returns the text of the SQL batch identified by the specified sql_handle. The table-valued function replaces the system function fn_get_sql.
Sys.dm_exec_text_query_plan: Returns a text-formatted display plan for a specific statement in a Transact-SQL batch query or batch query. The execution plan handle specifies that the query plan can be in the cached state or in the executing state. This table-valued function is similar to sys.dm_exec_query_plan, but has the following differences:
? The output of the query plan is returned in text format.
? The output of the query plan has no size limit.

the DMV associated with the database
Sys.dm_db_partition_stats: Returns page and row count information for each partition in the current database.
Only those information that is directly related to the servers in scope will be exposed. In SQL Azure, those columns in SQL Server that return instance-level server information will return null values. So, if you have some custom troubleshooting queries for SQL Server, you can run them directly in SQL Azure without having to modify them.

identify poorly performing query statements
SQL Server generates an optimized query plan for all queries that are executed. This allows the SQL Server optimizer to reuse the query plan at the same or similar query execution time, thus receiving data in the shortest possible time. Once the data has changed, the statistics on the data column make the query plan obsolete and inefficient. It is important to identify and debug these statements in order to achieve the best performance of the application and a consistent user experience. Previously listed DMV can directly help identify those queries that are problematic.

The following are some basic query statements that identify these problematic queries:

Excessive recompilation:
Select Top 25
Sql_text.text,
Sql_handle,
Plan_generation_num,
Execution_count,
dbid
Objectid
From
Sys.dm_exec_query_stats A
Cross apply Sys.dm_exec_sql_text (sql_handle) as Sql_text
where
Plan_generation_num >1
ORDER BY plan_generation_num Desc

Less efficient query plan
Select
    highest_cpu_queries.plan_handle,
    highest_cpu_ Queries.total_worker_time,
    q.dbid,
    q.objectid,
    Q.number,
    q.encrypted,
    Q.[text]
from
    (select Top
        qs.plan_handle,
         Qs.total_worker_time
    from
        sys.dm_exec_ Query_stats QS
    ORDER BY qs.total_worker_time Desc) as Highest_cpu_queries
    Cross apply Sys.dm_exec_sql_text (plan_handle) as Q
ORDER by highest_cpu_queries.total_worker_time Desc

I/O bottlenecks
Select Top 25
(Total_logical_reads/execution_count) as Avg_logical_reads,
(Total_logical_writes/execution_count) as Avg_logical_writes,
(Total_physical_reads/execution_count) as Avg_phys_reads,
Execution_count,
Statement_start_offset as Stmt_start_offset,
Sql_handle,
Plan_handle
From Sys.dm_exec_query_stats
ORDER BY
(total_logical_reads + total_logical_writes) desc

Transferred from: http://blogs.technet.com/b/sqlazurechina/archive/2011/08/18/sql-azure-optimization-debugging.aspx

SQL Server dynamic management view (DMVs)