June 18, 2010 at 8:37 am
After stepping in as DBA, our OpManager software is sending alerts about our Granted Workspace Memory threshold being exceeded. If our instance consistently uses above the default threshold, is it bad to simply adjust it up?
June 21, 2010 at 7:06 am
Well from my reading:-D, workspace memory (or query memory) is used by SQL when doing hash/sort operations. This memory is also pulled out of the buffer pool. So I would think you can check your Max/Min memory settings to see if those are optimally set. As well workspace memory has its own memory clerk that you can view the sizing information for any outstanding grants by checking sys.dm_exec_query_memory_grants DMV, check for type = 'MEMORYCLERK_SQLRESERVATIONS'.
However, along with any other configuration that you can change in SQL Server you should make sure you monitor all major parts of SQL, especially since memory changes can have an effect on other operations in SQL.
To give credit were due, I got this information from Christian Bolton's book Professional SQL Server 2008 Internals and Troubleshooting.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
June 22, 2010 at 7:59 am
Thanks for the information, it really helps alot. Unfortunately, this is still a little over my head but the dmv that you gave me puts me on the right path. Thanks again!
-James
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply