SQL Server Catalog Views & Dynamic Management Views (DMVs) take an administrator to the very heart of SQL Server. DMVs expose vital diagnostic information which are not only useful to indicate the overall health of the SQL Server, but also of the databases hosted by the given SQL Server instance. The list of DMVs that changed in SQL Server 2008 R2/2012 is available in one of my previous posts here: http://bit.ly/XobXup.

Before SQL Server 2008, there was no mechanism available within SQL Server to assign different resource “quota” to different components in order to better govern the resource utilization. Microsoft SQL Server 2008 introduced a new feature called “Resource Governor” which allows administrators to regulate (or govern) the CPU and memory consumption patterns for the given SQL Server instance. For a brief introduction to the resource governor, you can get a quick overview from Pinal Dave, a.k.a SQLAuthority (B|T) at SqlAuthority.com or the white-paper from Microsoft.

A complete list of the resource governor related DMVs and Catalog Views is available at:

• Resource Governor DMVs (Link)
• Resource Governor Catalog Views (Link)

Resource pools represent the physical resources available to the given SQL Server instance. Starting SQL Server 2012, resource pools can be affinitized to one or more schedules or NUMA nodes giving better isolation of the resources. The following new catalog view and DMV track the resource pool affinity:

• New Catalog View: sys.resource_governor_resource_pool_affinity
• New DMV: sys.dm_resource_governor_resource_pool_affinity (Link)

The above are not only similar sounding, but also appear to be similar in functionality as well (why this would be the case, I do not know – it looks like a bug to me).

The query below shows the usage and the screen-shot shows a sample result:

SELECT * FROM sys.resource_governor_resource_pool_affinity;
SELECT * FROM sys.dm_resource_governor_resource_pool_affinity;

Until we meet next time,

Be courteous. Drive responsibly.