Resource Pool – Physical I/O

Microsoft in the News

Civility Online

Did you know that the world has never been a better place than it is today? You can be forgiven for not thinking so, after all, we are inundated daily with news of how terrible things are. All that news gives us a false sense of the world. If you want to know just how much better the world has become over the past 500 years, I highly recommend a book that I picked up because Bill Gates highly recommended it: The Better Angels of our Nature by Steven Pinker. Even in the area of online civility, the trend is positive. Obviously, there was a definite downward trend in civility when people discovered they could post their trash anonymously online, but that trend may be reversing.

For those of you following certain popular tweeters and other online posters, you may be a little disgusted at the lack of civility on line. But there is hope! New research conducted by Microsoft, released on February 5, shows that the level of online civility is increasing!

Microsoft’s Digital Civility Index only started two years ago, so I would be stretching credulity to say that we have a trend. In fact, it could be argued that the two-point increase in civility isn’t even statistically relevant. But I prefer to remain optimistic and will continue to see the best in people, so, YAY!

If you want to do your own analysis on civility, and not just rely on your personal experience, follow the link where you can read the highlights, download the full report, sign up for the Digital Civility Challenge, and access more resources to promote digital civility.

Resource Pool – Physical I/O

In my last blog I dealt with the allocation of memory between resource pools, and within a single pool. Today I will look at the allocation of Physical I/O operations. Although SQL Resource Governor was introduced in 2008, the ability to manage physical I/O was only introduced in 2014.

When you execute a query, SQL must call the necessary data from the datafiles kept in permanent memory (a disk, SSD, or other form of storage). That call will copy the data from permanent memory and place it into a memory space called buffer cache. Having to rely on disks and other hardware is never a good idea if speed is of any concern.

Once read into the buffer cache, any read of that data that is required by a query uses Logical I/O.


SQL Server Resource Governor allows you to control the volume of physical I/O operations allowed by a given resource pool. This is the sort of thing that often becomes important at month or year end when large amounts of reports are being generated. At year end, when Accounting is buzzing with activity, the generation of reports can overwhelm your hardware and negatively impact higher priority work like Production.

Using MIN_IOPS_PER_VOLUME allows you to specify the minimum I/O operations per second (“IOPS”) per disk volume for a given resource pool. The default setting for this is zero and that indicates that this resource pool is not governed for I/O.

MAX_IOPS_PER_VOLUME is used to specify the maximum I/O operations per second per disk volume for a given resource pool. Setting this value to zero means that this pool will not be governed by this function.

In my next blog I will discuss the three types of resource pools.

Leave a Reply

Your email address will not be published. Required fields are marked *