How to view the history of requests (all ORs over a long period) executed in a database hosted on Azure?

Question

For a database hosted on Azure, I can view the recent history of queries made on it. This is through the Azure portal > Database> Management> Administration> Query Performance .

Unfortunately, the story found there covers only a small time interval (several minutes). I intend to create non-clustered indexes in my database and for this I need to get a log of real queries running on data on a normal day, unlike the last few minutes.

Currently, I have to constantly refresh the page and record all requests for each update. Even then, the log that I get after this complex process only reflects a small subset of the executed requests. Is it possible to view stories for longer periods?

Thanks.

Answer 1

The Windows Azure SQL Database offers dynamic management views (DMVs) that return server status information that can be used to monitor server instance health, diagnose problems, and tune performance.

For a list of available views, see System Views (Windows Azure SQL Database) .

Examples of how to find queries with an intensive processor, long queries, and intensive I / O queries are related to Configuring Azure SQL Databases, Part 2 .

For more troubleshooting tips, see Troubleshoot and optimize queries using the Windows Azure SQL database , Improve I / O performance , Retrieve Windows Azure SQL database performance information , Troubleshoot Azure Windows SQL database , Search for query blocking in SQL Azure , March Madness - SQL Azure - sys.dm_exec_query_stats .

Also consider application-level profiling, for example, as described in Profiling Database Activity in the Entity Framework and Query Profiling Azure SQL using Entity Framework or Linq-to-SQL .

For advanced monitoring, consider deploying the Windows Azure SQL Database Management Pack for System Center 2012 .

Answer 2

Here is a query that I found useful for viewing the most executed queries in an Azure SQL Server database:

SELECT TOP 10 execution_count, statement_text FROM ( SELECT QS.*, SUBSTRING( ST.text, (QS.statement_start_offset/2) + 1, (( CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE QS.statement_end_offset END - QS.statement_start_offset ) /2) + 1 ) AS statement_text FROM sys.dm_exec_query_stats AS QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST ) AS query_stats WHERE statement_text LIKE 'UPDATE%' ORDER BY execution_count DESC 

Source: March Madness - SQL Azure - sys.dm_exec_query_plan | SQLRockstar | Thomas LaRock