Changing gears

You haven’t heard from me in while and there is a reason. I moved from SQL Server’s Storage Engine team to a new incubation team that is involved in exploring new directions in operating systems. This subject area has been always very fascinating for me as you easily might deduce from my previous posts. The new team is…

3

Q & A: I am running on 64 bit and all of my tasks are waiting on SOS_RESERVEDMEMBLOCKLIST, What is going on?

This wait type represents waiting on multi page allocations inside of SQLOS’s memory manager. As you remember from my description of memory manager’s working internals, it has two major allocators single page allocator, SPA and multi page allocator, MPA. Majority of the time SQL Server internal code uses SPA, however in some cases it will…

3

Q & A: Does SQL Server always respond to memory pressure?

Q: I thought I’ve seen reference recently to problems with SQL2005 not releasing memory when under pressure, and this was possibly due to the Lock Pages In Memory option. We have a server where this seems to be happening, i.e. SQL2005 uses all available memory and the server basically dies because there’s no memory left…

11

Q & A: I can’t make SQL Server 2005 32 bit version to use all memory on the machine

I’m currently using the RTM+SP1 x86-SQL2005 build on a server which has 32GB of physical memory.  I notice that during ramp-up the memory usage (as seen under task manager) increases to about 28GB (i.e. “Available memory” reduces to 4GB) and stays there.      Is there a way to get around this and make SQL…

7

Q and A: Enabling AWE settings for SQL Server 32 bit edition in WOW

Q.  A customer wants to run SQL 2000 SP4 in a Windows Server 2003 SP1 x64 edition. They did the installation without problems but SQL Server is not using more than 4GB of memory.  Is it possible to enable AWE in this configuration? A.  The answer is yes, you should follow exactly the same procedure…

1

Q and A: Some questions about output from dbcc memorystatus

Recently I have recieved following question:  A customer of mine is getting this output on an Itanium with 16GB of memory: Memory Manager                      KB —————————— ——————– VM Reserved                           16979888 VM Committed                         217928 AWE Allocated                         14116272 Reserved Memory                     1024 Reserved Memory In Use            0   Q. How can VM Committed be so much less than AWE Allocated, or is this counter…

4

Q and A: SQL Server memory allocated outside of Buffer Pool, formerly known as MemToLeave

Q. Under SQL Server 2000 Is the memtoleave concept still valid under 64-bit?  A. Yes. If SQL Server’s component requests memory larger than 8 KB block, the block will be allocated outside of Buffer Pool. Remember, inside of SQL Server, Buffer Pool is preferable memory manager for dynamic allocations. However, it can only serve allocations…

4

Q and A: Do I need to run "sp_configure ‘awe enabled’, 1" to make 64 bit version of SQL Server use locked pages?

The answer is No.  The source of possible confusion came from one of my previous posts: http://blogs.msdn.com/slavao/archive/2005/11/15/493019.aspx. I apologize for the confusion. In order to make 64 bit version of SQL Server leverage locked pages all you need to do is to give lock pages in memory privilege to an account under which SQL Server process runs. You…

2

Q and A: Virtual Address Space, VAS, reservation – why bother?

Seems like today is a question day :-). I will attempt to answer several questions about VAS I got today: Q1.  There is one aspect of Virtual Memory that is just not clear yet. I think I understand about VAS, but maybe not as much as I could, but I don’t understand about ‘reserving memory’. When we talk about SQL…

4