Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  SQL Server Administration (2005)  Tons of Memory Grant waits

Author  Topic  TJTodd Starting Member 10 Posts Posted - 2011-03-24 : 14:34:23 Hi, We have an environment where we are running SQL2005 and we are getting several incidents where we see many processes that have a Resource Semaphore wait. I started capturing some of the Memory Manager Perfmon counters at the time and found the following values: Connection Memory (KB) 26440 Granted Workspace Memory (KB) 68384 Lock Memory (KB) 7048 Lock Blocks Allocated 69505 Lock Owner Blocks Allocated 72633 Lock Blocks 5895 Lock Owner Blocks 7557 Maximum Workspace Memory (KB) 210480 Memory Grants Outstanding 5 Memory Grants Pending 38 Optimizer Memory (KB) 688 SQL Cache Memory (KB) 2736 Target Server Memory (KB) 32768000 Total Server Memory (KB) 32768000 The one thing that troubles me is the huge discrepancy between "Total Server Memory" and "Maximum Workspace Memory". I've been reading through many other blogs and see postings where these numbers are close (at least the same order of magnitude). With such a small Total Server Memory - we are often getting waits. A few questions: 1) Is such a huge difference in "Total Server Memory" and "Maximum Workspace Memory" expected? 2) If this Maximum Workspace Memory value is unexpectedly low - is there anything that I could do to help me find out why it's so low (like looking for something specifically in DBCC MEMORYSTATUS?) Thanks in advance, Tom