'How to find cause for high Data IO spikes in SQL Azure?
this problem has been keeping me up at night. I seem to have intermittent issues that are causing data IO spikes in one of my databases. It's a high write, high read scenario. We are reading in live market data every 5 minutes for every single ticker in the US market so think along the lines of >8K record inserts every 5 minutes.
According to sql query performance insights I've been getting 90%+ Data IO spikes (seems it occurs in the morning as soon as our job starts running and populating our DB) but yet none of the queries running in that time frame seem to be causing the spikes.
My question is: what can be done to get to the root cause of what is causing these spikes? I would like to decrease the data tier of the db to save some money but these spikes cause lock ups on lower tiers for all of my reads so I would need to find out what's causing them first to eliminate them.
Solution 1:[1]
To monitor the performance of a database in Azure SQL Database and Azure SQL Managed Instance, start by monitoring the CPU and IO resources used by your workload relative to the level of database performance you chose in selecting a particular service tier and performance level.
Azure SQL Database provides several Database Advisors to provide intelligent performance tuning. Additionally, Query Performance Insight shows you details about the queries responsible for the most CPU and IO usage for single and pooled databases.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | PratikLad-MT |