If you’ve been managing databases for any length of time, you’ve probably had to contend with the “noisy neighbors” scenario. In this scenario, you have several applications, each with their own database residing on a single instance of SQL Server, which don’t share the resources of the server nicely. Perhaps one of the applications is constantly consuming the lion’s share of CPU or memory on that instance, when you really wanted every application to get an even split of resources.
There are lots of ways to manage noisy neighbors. For example, you could spin up additional instances of SQL Server on a single physical or virtual machine (VM), and then segregate the applications to a distinct instance. You could also follow the old adage of “one application, one SQL Server” by putting the SQL Server onto its own machine, either physical or virtual. But that can get very expensive very quickly, depending on your licensing methodology.
If you’re running SQL Server 2008 or later, you might want to investigate Resource Governor as an alternative. Resource Governor lets you create limits on the amount of system resources a database and application can consume. On versions 2008 to 2012, Resource Governor can explicitly limit CPU and memory and, starting with version 2014, limit I/O consumption as well. This is powerful medicine for multi-tenant instances with noisy neighbors!
How Resource Governor Works
Resource Governor allows you to define one or more resource pools, which represent a pre-allocated share of system resources, composed of a mix of CPU, memory, and I/O. For example, on a heavily-OLTP database, you might create a high-priority resource pool called OLTPResourcePool for the OLTP users and a low-priority pool called AnalysisResourcePool for reporting users.
In addition, you must configure workload groups, which define the way you assign incoming user sessions to a given resource pool. For example, you might have two Active Directory groups, Operators and Analysts, whom you want to assign to their respective resource pools, OLTPResourcePool and AnalysisResourcePool. Or, you might instead check to see which application they connect with, and assign the web service connections to the OLTP-ResourcePool, while the web analytics connection go to the AnalysisResourcePool.
Finally, Resource Governor needs a way to connect the resource pools to the workload groups, which you do by specifying a classification function. The classification function is a user-defined function (UDF) written in good ol’ Transact-SQL whose logic enables Resource Governor to classify sessions into the existing workload groups defined in SQL Server. As each user connects to the database and opens a session, the classification function makes a quick determination about which resource pool they go into. From then on, their resource consumption is governed according to the resource pool allocations.
Restrictions on SQL Server
Resource Governor does not put limits on any SQL Server engine outside of the relational database engine. That means it cannot limit Analysis Services, Reporting Services, or Integration Services. It doesn’t govern resources between SQL Server instances running side-by-side on a single machine. And, it cannot govern I/O for system processes, including the log writer (for transaction log writes) and the lazy writer (for plan cache writes). That means that the I/O governor limits usually apply more to read operations than write operations. Finally, very brief queries might go so quickly that the workload monitor doesn’t see them. In those cases, the Resource Governor may permit a slight skew on CPU limitations.
Read more about Resource Governor on SQL Server 2014 at https://msdn.microsoft.com/en-us/library/bb933866.aspx.
To find out how to monitor and interrogate Resource Governor activity on an instance of SQL Server, check out https://msdn.microsoft.com/en-us/library/bb895320.aspx.