Sql-server – SQL Server 2012 Intellisense Support for Extended Events

This is a good question, because there is a lot of misleading information out there.

The changes to what memory Resource Governor can manage in SQL Server 2012 roughly coincide with the changes to what memory the Max Server Memory setting can manage. Prior to SQL Server 2012, this was just buffer pool / single-page allocations. In SQL Server 2012 this is extended to multi-page allocations. I lifted this diagram from Memory Manager surface area changes in SQL Server 2012:

And while the statement is a little over-promising IMHO, the SQL OS team also stated in New SQLOS features in SQL Server 2012:

Resource Governor governs all SQL memory consumption (other than special cases like buffer pool)

When I first started doing research on the changes to this feature, the indication I had was that it could manage all memory allocations except buffer pool and column store cache. Prior to SQL Server 2012, Resource Governor only had control over query grant memory.

There are of course other important enhancements to Resource Governor in SQL Server 2012, that I thought I'd mention for other readers who get here because of your question title. You can now:

• have 64 resource pools instead of the previous limit of 20
• use real CPU capping (even with no contention) - the previous model relied on concurrency before implementing a cap
• implement scheduler / NUMA node affinity (e.g. tie a resource pool to a specific NUMA node)

Also note there are some differences with how memory manager changes have been implemented in 32-bit vs. 64-bit. Some relevant info here.

Not sure what you are seeing and not seeing in the event log but it works as expected for me (version 11.0.3000).

The mistake you did with your extra path expressions is that you forgot Stuff in the path.

Testing this with the extended events and looking at the execution plan for operator Table Valued Function XML Reader ... (there should be none), I found that one extra path is enough for your queries to make it use only the index to fetch the data [4] = '/Root/Stuff/Forms' as SQL nvarchar(25). You can have [3] = '/Root/Stuff/Forms' as XQUERY 'node()' in there as well and perhaps it will make a difference or not depending on what your XML look like.

Note 1: You have mixed up the names of the columns in the index and your query. That might contribute to the problems you have with extended event.

Note 2: The events for missing selective XML indexes is generated when the query is compiled. If the query plan is used from the cache you will not see any events.