CODEWITHSUNDEEP
sql-serverazureazure-sql-database
Ashkan S
Ashkan S

Reputation: 11471

Azure SQL Server: How to check which queries and how much resources they have used?

I have a SQL server DB (on Azure SQL Elastic pool). I want to optimize my queries so the best approach for me would be to check how many times a query has been ran and how much resources (CPU, IO, etc.) they have used. How can I do it?

I wonder if it is possible to do something like what we have on Application Insights.

Upvotes: 1

Views: 3583

Answers (2)

Grant Fritchey
Grant Fritchey

Reputation: 2775

There are two ways to gather information for yourself within Azure SQL Database. First is an aggregated method called Query Store. It captures plans, queries and wait statistics and aggregates them (by default over 60 minutes) and stores the data for 30 days (by default). You can then query this information to retrieve it for yourself. There are also reports built in to SSMS. Automated tuning in Azure takes advantage of this information as well.

Second, you can get granular and detailed information by using Extended Events. You can capture information at the call, batch or statement level. It will show you every bit of resource being used by any given query. You just have to be prepared to manage the information being gathered.

Upvotes: 2

Cristian Iosub
Cristian Iosub

Reputation: 157

If you are looking into something like Application Insights I think Azure SQL Database Query Performance Insight is what you're looking for. Here is a link to the docs:

https://learn.microsoft.com/en-us/azure/sql-database/sql-database-query-performance

You can also do that within SQL server using some queries, but this seems to be what you're looking for.

Upvotes: 0

Related Questions