Tuesday, May 13, 2014

SQL Server Resource Governor Resource Pools and Perfmon

Wow... add some Resource Governor Resource Pools with memory partitioning, and there can be some wild swing in "Query exec memory target (KB)" values.  So I'll stumble around until I understand the relationships among some of these perfmon counters and the stuff reported by the DMVs.

SQL Server, Resource Pool Stats Object

SQL Server 2008 R2
For Resource Governor resource pool ssmypool, if neither of the counters are changing due to broker  grow/shrink hints, "SQLServer:Resource Pool Stats(ssmypool)\Query exec memory target (KB)":"SQLServer:Resource Pool Stats(ssmypool)\Cache memory target (KB)" will settle to a .9375 ratio.

"SQLServer:Resource Pool Stats(ssmypool)\Query exec memory target (KB)":"SQLServer:Resource Pool Stats(ssmypool)\Target memory (KB)" ratio will be nearly 80%* and stable when the previous ratio is .9375.

*I was actually observing a 0.791015625 ratio while stable.  Go figure.

No comments:

Post a Comment