A SQL Server DBA myth a day: (22/30) resource governor allows IO governing

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)

Resource governor is a great feature in SQL Server 2008, but there are some misconceptions about what it can do…

Myth #22: resource governor allows governing of IO activity.

FALSE (Edit 8/9/2014: I/O governing was added in SQL Server 2014)

Resource governor does not govern IO activity in any way – hopefully that will be something that’s added in the next major release of SQL Server. It’ll be a lot more useful once you can use to prevent run-away queries doing huge table scans, or spills into tempdb.

Some other things that resource governor does *not* do in the first version:

  • Allow governing of buffer pool memory. The memory governing it performs is for query execution memory grants only – not for how much space is used in the buffer pool by pages being processed by a query.
  • Allow two instances of SQL Server to cooperatively govern CPU and memory resources. Multi-instance governing has to be done with Windows Server Resource Manager, and then with resource governor for each instance.
  • Allow a connection to be notified that it has been governed in some way.

Don’t get me wrong – it’s great, but it will be a lot better with these additions too.

 Our friend and fellow-MVP Aaron Bertrand (twitter|blog) and SQL PM Boris Baryshnikov wrote a comprehensive whitepaper that you should read for more details: Using the Resource Governor.

 

2 thoughts on “A SQL Server DBA myth a day: (22/30) resource governor allows IO governing

  1. Thanks GOD its now possible… as you said… and previewed

    “hopefully that will be something that’s added in the next major release of SQL Server. It’ll be a lot more useful once you can use to prevent run-away queries doing huge table scans, or spills into tempdb.”

Leave a Reply

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

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.