DBAs can’t ignore disk operations when working on query performance tuning. When talking about databases, ‘disk’ may be called by one of its many names, such as ‘storage’, ‘I\O’, ‘Reads’, or disk operations.
Although database professionals know all these terms mean the same thing, these might confuse those outside this field. When referring to one of these terms, they usually mean the number of disk operations required to fetch the data from the Disk resource.
Why You Can’t Ignore Disk Operations During Query Performance Tuning
The fact remains, however, that an overwhelming majority of SQL Server databases face the bottleneck issue when it comes to the disk resource. This doesn’t change, regardless of whether you have old-school hard drives or the latest flash storage arrays. Given below are some major reasons behind this, and how these can be affected with MySQL query optimization:
- Most slow queries are slow because they have to scan a large amount of data. A lot of the time, this is unnecessary and it’s making your SQL Server perform a lot of unneeded and really sluggish read operations.
- When the database reads data, it requires a place to store that information- which it does in the RAM. However, since the RAM has a limited capacity, older information starts getting removed with newer data coming in.
- Because RAM is never enough, it is often unable to store all the data that SQL Server fetches. Therefore, the remaining data has to be kept on the disk, which is far slower than the RAM. The information that isn’t present in the RAM has to be fetched from the disk- an operation that is known as the slowest in all of the database operations. Some DBAs even compare data fetching from the RAM and the disk to sprinting and tip-toeing.
- So, if we tune a query to read less data than it did before, such as twenty rows instead of twenty thousand, it will help in two ways. Not only will it reduce the workload on the database in terms of disk operations, but it will also require far fewer resources, including CPU and RAM, to process all the data. That said, the end-user is unaware of all these operations- all they know and appreciate is the speed or the time it takes for the query to fetch information. To put it simply, they just want the screen on the app to return as quickly as possible. This is why query performance tuning focuses on decreasing disk reads.
- DBAs also perform tuning to lower the other resources, such as CPU or RAM usage. But they only do this in certain special situations where such resources are consistently being overused at dangerous levels. For instance, if the CPU is in constant use of 90% or above, then the DBA will consider CPU tuning.
- Tuning queries that fetch large volumes of data to fetch much smaller volumes instead improves SQL Server capacity. This is because when a query takes up fewer resources, it leaves room for more users and queries. This allows the same server to take greater loads than it could. Performing MySQL query optimization also improves the lifespan of the same server, delaying the requirement for a hardware upgrade.
The above-mentioned reasons shed light on the fact that disk operations play a major role in enabling efficient database query performance. You can’t always blame the CPU; in fact, you can rarely do so since 95% of bottlenecks occur on the disk resource.
The CPU, on the other hand, is only a lagging indicator whose use can decrease if the storage reads differ.