How to view history of queries (all OR over a long period) performed on database which is hosted on Azure?

logging azure indexing azure-sql-database database-tuning
user2629743 picture user2629743 · Jul 29, 2013 · Viewed 9.2k times · Source

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

Unfortunately, the history found there, only covers a small time frame (few minutes). I intend to create non-clustered indexes on my database and for that, need to get a log of the real queries run on the data on a typical day, as opposed to the past few minutes.

Currently, i have to keep refreshing the page many times and record all the queries for each refresh, Even then, the log which i obtain after this arduous process, only reflects a small subset of the queries performed. Is there any option to view histories for longer periods?

Thanks.

Answer

Fernando Correia picture Fernando Correia · Jul 29, 2013

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

For a list of available views refer to System Views (Windows Azure SQL Database).

For examples of how to find CPU-intensive queries, long-running queries and I/O intensive queries refer to Tuning SQL Azure Databases, Part 2.

For additional troubleshooting tips refer to Troubleshoot and Optimize Queries with Windows Azure SQL Database, Improving Your I/O Performance, Gaining Performance Insight into Windows Azure SQL Database, Troubleshooting Windows Azure SQL Database, Finding Blocking Queries in SQL Azure, March Madness – SQL Azure – sys.dm_exec_query_stats.

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

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

Related questions

How does Microsoft.Extensions.Logging work for full .net framework?

The article (Monitor and diagnose Azure Service Fabric applications) indicates following (please note text in bold): ASP.NET Core logging Choosing how to instrument your code can be difficult, if you chose poorly and have to reinstrument, you are revisiting …

Read More
Azure function apps logs not showing

I'm relatively new to Azure and I just went through the tutorial on how to create a new Azure function, which is triggered when a new blob is created, and had this as the default code in it. public static …

Read More
grep a file, but show several surrounding lines?

I would like to grep for a string, but also show the preceding five lines and the following five lines as well as the matched line. How would I be able to do this?

Read More