Microsoft Azure SQL Database is a cloud based relational database service from Microsoft. SQL Database provides web-facing database functionality as a utility service. Cloud-based database solutions such as SQL Database can provide many benefits, including
rapid provisioning, cost-effective scalability, high availability, and reduced management overhead. This paper provides guidelines on the Dynamic Management Views that are available in SQL Database and how they can be used for troubleshooting purposes.
SQL Database is a cloud based relational database with SQL Server 2008 engine at its core. Dynamic Management Views (DMVs) were introduced with SQL Server 2005 and since then, have become a very powerful tool for troubleshooting issues at all levels performance
from the state of the system to deadlock information etc. While most of these DMVs have been disabled for the first release of SQL Database, they are being enabled in phases as part of the scheduled Service Updates (SUs) to SQL Database. DMVs expose information
at the instance level. Since SQL Database is a shared infrastructure model, the DMVs have to be modified to filter the output and show information only as appropriate. In this effort, the following DMVs have been enabled in the first phase.
These DMVs being released typically require VIEW SERVER STATE permissions in an on-premise SQL Server. The new permission level required on SQL Database would be VIEW DATABASE STATE to query these DMVs.
Following are the transaction related DMVs released in January SU:
· sys.dm_tran_active_transactions - returns information about transactions for the SQL Database server
· sys.dm_tran_database_transactions - returns information about transactions at the user database level
· sys.dm_tran_locks - returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The columns in the
result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.
· sys.dm_tran_session_transactions - returns correlation information for associated transactions and sessions.
Following are the execution related DMVs released in January SU:
· sys.dm_exec_connections - returns information about the connections established to SQL Database and the details of each connection.
· sys.dm_exec_query_plan - returns the showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing.
· sys.dm_exec_query_stats - returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed
from the cache, the corresponding rows are eliminated from this view.
· sys.dm_exec_requests - returns information about each request that is executing within SQL Database.
· sys.dm_exec_sessions - returns one row per authenticated session on SQL Database.
· sys.dm_exec_sql_text - Returns the text of the SQL batch that is identified by the specified
sql_handle. This table-valued function replaces the system function
· sys.dm_exec_text_query_plan - returns the showplan in text format for a Transact-SQL batch or for a specific statement within the batch. The query plan specified by the plan handle can either be cached or currently executing.
This table-valued function is similar to sys.dm_exec_query_plan (Transact-SQL), but has the following differences:
· The output of the query plan is returned in text format.
· The output of the query plan is not limited in size.
The following DMV is released in the January SU:
· sys.dm_db_partition_stats - returns page and row-count information for every partition in the current database.
Only information that is directly related to the server in scope is exposed. Columns that would otherwise return server information at the instance level would return NULL in SQL Database. So if you have custom troubleshooting queries that you are already
using on an on-premise SQL Server then you don’t need to modify those queries to execute on SQL Database. They can be run directly in SQL Database seamlessly.
Truncation of Trans Logs
Truncate SQL Trans Logs will enable you to have better DB maintenance.
SQL Server generates an optimized query plan for all the queries that are executed. This allows the SQL Server optimizer to reuse the query plan when the same or similar query is executed to retrieve the data in the fastest time possible. As the data changes
and the statistics on those data change the query plans become out of date and can become inefficient. It is important to identify these queries and tune them for optimal performance of the application and consistent user experience. The DMVs discussed above
directly help in identifying the problematic queries.
Following are some basic queries to troubleshoot poor performance of queries:
select top 25
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
order by plan_generation_num desc
Inefficient query plans:
(select top 50
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
(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,
statement_start_offset as stmt_start_offset,
(total_logical_reads + total_logical_writes) Desc
This article is also available in other languages, including Russian.
Wiki: Решение проблем и оптимизация запросов Azure SQL Database (ru-RU)
Creating a foreign key does not also create an index on the column. If you're doing
SELECT * FROM sometable WHERE sometable.aForeignKey = someparameter
you may want to add the index or the DMV will tell you.
SQL universal commands will never change
Richard Mueller edited Revision 13. Comment: Replaced RGB values with color names in HTML to restore colors, added tags
Rocking good article!
Shreeharsh Ambli edited Revision 16. Comment: added tag