July 28, 2010 at 12:09 pm
We're trying to figure out some memory/performance issues and are looking at the results of:
SELECT bpool_commit_target, bpool_committed, bpool_visible FROM sys.dm_os_sys_info
We are getting
bpool_commit_target: 384000
bpool_committed: 15360
bpool_visible: 201216
This really isn't making much sense to me from the documentation in BOL. Shouldn't committed be much closer to target, or at least visible?
SQL Server Standard 9.00.4266.00 32 bit
AWE enabled, Lock Pages in Memory enabled
4095 (4294144000) Physical memory
3000 Max Server memory
Our monitoring tools are showing SQL Server as only taking < 2 GB memory. I did a restart and I could see it take the 3 GB allocated, but then it dropped to under 2 - even though I then ran a very system-intensive query.
Any ideas?
July 28, 2010 at 12:52 pm
Hi Pam,
Set Min Memory = Max Memory = 3 GB.
Thank You,
Best Regards,
SQLBuddy.
July 29, 2010 at 8:45 am
I'm not sure setting Min Memory to the same amount as Max Memory is an optimal solution, SQLBuddy. Once SQL reaches that 3GB, it won't free up memory from the buffer pool unless the Min Memory is reduced, which means if that memory is needed for other applications, it could cause performance issues.
Pam, are you actually having performance issues or are you just worried because SQL isn't using the 3GB memory?
If the later, don't worry about it. You can run a Server Side Profiler trace (or Profiler) and PerfMon to double-check everything. But SQL Server doesn't always use the allocated memory because it doesn't always need it. However, I did find a link on google.
Microsoft
bpool_committedNumber of 8-KB buffers in the buffer pool. This amount represents committed physical memory in the buffer pool. Does not include reserved memory in the buffer pool.
bpool_commit_target
Number of 8-KB buffers needed by the buffer pool. The target amount is calculated using a variety of inputs such as the current state of the system, including its load, the memory requested by current processes, the amount of memory installed on the computer, and configuration parameters. If the bpool_commit_target is larger than the bpool_committed value, the buffer pool will try to obtain additional memory. If the bpool_commit_target is smaller than the bpool_committed value, the buffer pool will shrink.
I think you're fine. I'm pretty sure the ratio is supposed to be this way.
July 29, 2010 at 9:13 am
Brandie, that's the info I was running off of. Yes, we are having performance issues, which is why I'm looking into all of this.
This part:
If the bpool_commit_target is larger than the bpool_committed value, the buffer pool will try to obtain additional memory.
is what concerns me. The way I'm reading that and the numbers posted above, SQL Server wants/needs more memory but is not taking it.
We did find one article yesterday which stated that if you run 4 GB under standard, SQL Server will only take 2 GB. According to that article, you have to go above 4 GB physical memory to see SQL Server grab anything over 2. We upped the RAM to 8 GB last night and now the numbers are closer together:
bpool_commit_target: 496771
bpool_committed: 480576
bpool_visible: 197120
We're now having other issues, unfortunately but those appear to be a Windows / VMWare problem rather than SQL Server. (The system appears to be grabbing and using all 8 GB of memory, leaving very little for SQL Server. We're also getting VMWare errors. *sigh*)
and I agree, I'd never set MIN to the same as MAX. That causes all kinds of issues.
July 29, 2010 at 10:34 am
Whoops, I must have mixed up those two fields when I read the snippet. My bad.
What is the priority on your Server Properties Processors tab? And what else do you have running on that server?
July 29, 2010 at 10:49 am
No worries. I had to read that several times to make sure I was understanding it correctly.
Processor tab: affinities automatic max threads 0, boost priority not set
Nothing else runs on this machine other than the standard server things.
July 29, 2010 at 11:38 am
Pam Brisjar (7/29/2010)
Processor tab: affinities automatic max threads 0, boost priority not setNothing else runs on this machine other than the standard server things.
But you're having performance issues still. Check the anti-virus software. Make sure it's excluding the .mdf and .ldf (and any .ndf) files. The problem could be that the software's scanning the files every time a transaction is made.
Also, try checking "Boost SQL Server priority" to see if that assists. This is a setting that can always be turned off if it causes more problems than it resolves.
Also, run a server side trace in tandem with PerfMon. If you're having a windows issue, that should suss it out.
August 4, 2010 at 11:45 am
Figured I should follow up with the resolution as I know it annoys me when I don't see it on others' posts.
First off, thanks for the reminder about anti-virus. Protocol is that it is indeed supposed to be disabled for SQL Server files but I double-checked and it was missed for these servers.
But the real issue was that, despite my telling our Ops guy to build the VM's with exclusive/non-shared memory, he built them with shared memory. This led to chaos and panic and some really odd behavior.
So lesson learned. When you're building VM SQL Servers - especially high demand ones. FOLLOW THE BEST PRACTICES* and jump and scream and holler if your Ops guy ignores you.
* http://communities.vmware.com/docs/DOC-8964
*sigh*
August 5, 2010 at 7:25 am
Pam,
Thank you for posting that resolution. It definitely is helpful as I did not know that could be a problem.
Glad you got it resolved. (Assuming you did get him to "fix" it, that is).
August 5, 2010 at 11:19 am
Oh, it's definitely fixed. Also noted up and across the chain so it can be addressed with any future related issues.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply