How to calculate the memory size of resource pool ?

    know variable "max_memory_percent" control size of resource pool. document says " max_memory_percent specifies total server memory can used requests in resource pool."(http://msdn.microsoft.com/en-us/library/bb895329.aspx).

   my question  that "the total server memory" mean total memory of os ? if yes, resource pool may consume 100 percent of total os memory, may larger buffer pool.  actually, never happens.  when watch memory size of sqlsrver process showed windows task manager,  i find littler bigger sqlserver buffer pool .  but can't figure out is.

   there dmv view "dm_exec_query_resource_semaphores", column total_memory_kb describes memory held resource semaphore in kilobytes. has relation max_memory_percent ?

    application reports "there insufficient system memory in resource pool 'respool' run query". there way calculate maximum query memory?

 

it execution memory grant memory, @ plan information , see requested memory grant size query.  then i'd take @ why has high memory grant requirement, sorts , hashes starting points.

---

jonathan kehayias | principal consultant, sqlskills.com
sql server mvp | microsoft certified master: sql server 2008
author of troubleshooting sql server: guide accidental dbas
feel free contact me through blog or twitter. become sqlskills insider!
please click mark answer button if post solves problem!

SQL Server  >  SQL Server Database Engine