SQL Server performance tuning is often considered as a complex subject and many DBAs and Developers often see SQL Server Engines as a black box. However, the reality is very different as SQL Server Performance tuning is often a simple art of balancing three of the important resources for any system – 1) CPU, 2) Memory and 3) IO. In this blog post, we will discuss how we can reduce critical waits for queries and optimize server performance.
Let us discuss the three important resources for any system. Before we continue this discussion, let’s answer the following important question:
How many times have you upgraded your CPU and Memory whenever you have encountered slow query performance? Honestly, it is pretty impractical to increase CPU or Memory once the box is set up and running. Additionally, often to replace CPU, you may have to replace your motherboard and get a new motherboard which supports the new CPU. If you look at this scenario, now we are left with only one thing which is IO. When we talk about IO, we are actually talking about IO throughput. If we can’t easily upgrade CPU or Memory, we should be considering increasing the IO throughput of our system to improve its performance.
What is Wait Statistics?
Basically, wait types are nothing but wait performed to execute any task created by SQL Server. As per BOL, there are three types of wait types, namely:
- Resource Waits. Resource waits occur when a worker requests access to a resource that is not available because that resource is either currently used by another worker, or it’s not yet available.
- Queue Waits. Queue waits occur when a worker is idle, waiting for work to be assigned.
- External Waits. External waits occur when an SQL Server worker is waiting for an external event.
To check the wait types for any server, just run the following simple statistics:
SELECT * FROM sys.dm_os_wait_stats
IO Throughput Improvements
One of the easiest solutions to improve the IO throughput would be to upgrade the disk to a faster modern disk. However, if you look at that, it is just as difficult a solution as upgrading CPU or memory. Thankfully, there is another alternative way to improve the IO throughput of the system as well. We can easily tune queries, create indexes, or tune the overall server’s health quickly to reduce IO wait for types.
Whenever I go for performance tuning consulting engagement, my primary focus is to help the customer reduce their wait statistics on various resources. Tuning query and creating indexes require an understanding of the business logic and vision, which I often do not know as I am an external consultant. I have found that fixing IO Wait Statistics are low hanging fruits that can be easily improved. The constraint of being an external consultant limits me to now focus on IO Wait Statistics which I can use to tune the SQL Server.
After fixing the server and database configuration, the first thing I do is to run the SQL Server Wait Statistics Query and check the what are the top IO Wait Statistics. Here are the top IO related Wait Statistics.
IO_COMPLETION Explanation:
Any tasks are waiting for I/O to finish. This is a good indication that IO needs to be looked over here. Here is the link to how to resolve this BACKUPIO and BACKUPBUFFER Wait Stats.
ASYNC_IO_COMPLETION Explanation:
Any tasks are waiting for I/O to finish. If by any means your application that’s connected to SQL Server is processing the data very slowly, this type of wait can occur. Several long-running database operations like BACKUP, CREATE DATABASE, ALTER DATABASE or other operations can also create this wait type. Here is the link to how to resolve this ASYNC_IO_COMPLETIONWait Stats.
BACKUPIO and BACKUPBUFFER Explanation:
This wait stats will occur when you are taking the backup on the tape or any other extremely slow backup system. Here is the link to how to resolve this BACKUPIO and BACKUPBUFFER Wait Stats.
WRITELOG Explanation:
This wait type is usually seen in the heavy transactional database. When data is modified, it is written both on the log cache and buffer cache. This wait type occurs when data in the log cache is flushing to the disk. During this time, the session has to wait due to WRITELOG. Here is the link to how to resolve this WRITELOG Wait Stats.
Well, you can see how easy we can get going with SQL Server Performance tuning with the help of SQL Wait Statistics and we can also take improve the performance of SQL Server by reducing the IO.
If you want to see a practical demonstration of How to Optimize Your Server Performance by Reducing IO Waits, you can now sign up for my webinar here.
Reference: Pinal Dave (https://blog.sqlauthority.com)
