Microsoft Azure SQL Database performance metrics running slowly

Data and Databases
1

I’m having trouble with the performance of Azure SQL Database metrics. The monitoring queries are taking a long time to run and using up a lot of CPU resources. Sometimes it goes up to 100%!

Here’s what I’ve tried:

-- This query takes about 4 minutes and maxes out the CPU
SELECT *
FROM sys.resource_stats
WHERE database_name = 'mydb'
AND start_time > DATEADD(day, -7, GETDATE())

-- This one is faster but still uses 80% CPU
SELECT end_time, avg_cpu_percent
FROM sys.dm_db_resource_stats

I need to keep an eye on my database pretty often but these queries are messing up all my stats. Does anyone know why they might be so slow? Are there better ways to get Azure SQL stats without killing performance?

I’d really appreciate any tips or advice. Thanks!

2

hey man, have u tried using azure monitor instead? it’s way less taxing on ur db and gives pretty much the same info. i had similar issues and switching to that helped a ton. also, try narrowing down ur queries - select only the columns u actually need instead of everything. that shud help with the cpu usage too

3

Have you considered using Azure SQL Insights? It’s a built-in monitoring solution that provides comprehensive performance metrics without the overhead of running resource-intensive queries directly on your database. I’ve found it to be incredibly useful for monitoring database performance without impacting workloads.

Another approach is to optimize your existing queries. Instead of selecting all columns, try narrowing down to specific metrics you need. For example:

SELECT end_time, avg_cpu_percent, avg_data_io_percent
FROM sys.dm_db_resource_stats
WHERE end_time > DATEADD(hour, -1, GETUTCDATE())

This query focuses on recent data and specific metrics, which should be less taxing on your system. Additionally, consider implementing a sliding window approach for historical data, storing aggregated metrics in a separate table that you can query more efficiently.

Lastly, review your database’s overall performance tuning. Proper indexing and query optimization can significantly reduce the impact of monitoring queries on your system resources.

4

hey there! have u tried azure monitor or sql insights? they’re way less resource-heavy and give u great metrics without slowing things down. also, maybe try tweaking ur queries to grab only the specific data u need? that could help reduce the cpu load. what kinda monitoring schedule are u aiming for?