Azure Recipe #1 – Query Performance Insight and Long Running Queries

Problem

You have a SQL Azure database and you wish to identify which queries in your database takes more time to execute.

Solution

You can use Query Performance Insights in Azure to identify the slow running queries in SQLAzure.

The Query Performance Insight provides intelligent query analysis for both single and pooled databases. It is a nice tool that lets you to find queries to optimize and improve it. It provides insights on your DTU consumption as well as details on the queries by CPU, duration and execution count.

You must ensure that the Query store is active on your SQL Azure database for the Query Performance Insight to work fine. Additionally, you will need one of the following permissions to view the resource consuming queries – Reader, Owner, Contributor, SQL DB Contributor, or SQL Server Contributor.

You can access the Query Performance Insight by first selecting the SQL Azure database and then selecting “Query Performance Insight” under Intelligent Performance in the left sidebar.

To view the Long running Queries, select the Long Running Queries Tab. This will show you the list the queries by duration.