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:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may