Improve I/O throughput of intelligent subsystems to favor SQL Server

Improve I/O throughput of intelligent subsystems to favor SQL Server

One of the peculiar features of Microsoft SQL Server is its capability to keep alive multiple threads at the same time for performing I/O operations. The configuration options that affects how many threads available for these operations is max async IO. The default value for this option is 32, which is adequate for most cases. However, when the server computer can leverage advanced sub-systems for managing disk arrays, this value can and should be increased because more threads can work at the same without affecting negatively disk performance. On the other hand, it is important not to use too large a value, which would increase lazy writes and checkpoints and decrease the number of read requests.

There are several strategies to fine-tune the performance in this case. One of the simplest techniques is to increase the option value and use the system Performance Monitor to check the most significant SQL Server counters, such as the “Batch requests/sec” of the “SQLServer:SQL Statistics”. If the option setting is optimal, this counter should maintain a large value. If this isn’t the case, it is evident that write operations tend to exhaust the I/O capabilities of the disk sub-system. For more information about advanced performance tuning techniques, see Chapter 15 of “Inside Microsoft SQL Server 7”, by Ron Soukup and Kalen Delaney (Microsoft Press).########################################################

This tip has been originally published on Microsoft Italia’s web site.
It has been translated and re-published on VB2TheMax with the permission of Microsoft Italia.
You can find more tips like this one (in Italian) at http://www.microsoft.com/italy/sql/articoli

########################################################

Share the Post:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as