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…

8

Talking points around SQL Server 2005 scalability

Couple of weeks ago I did a talk on SQL Server 2005 scalability. The actual talk was not about the enormous amount of features that you can leverage in SQL Serve 2005 to make your application scale but rather how SQL 2005 achieves its scalability. In this post I present a set of talking points…

10

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…

6

Set of new features in SQL Server 2005 enabled by SQLOS

Couple of weeks ago I had a chance to visit set of our customers and talk to them directly. It was an unforgettable experience. As a part of the visit I made set of presentations related to SQLOS and new features it enables in SQL Server 2005. I thought that some of you might be…

4

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: 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…

2

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…

0

Be Aware: 4GB of VAS under WOW, does it really worth it?

By now you have heard a lot about the fact that under WOW a 32 bit process can get 4GB of VAS.   I agree this is great, but I would like you to be aware of something that might hit you really hard.  As it turns out enabling 4GB VAS can be problematic due to…

7

Q and A: Clock Hands - what are they for?

Q1 I read in your blog about the new algorithm with two clock hands to control memory consumption, but I really would like to understand exactly what the hands are doing and if they relate to the lazywriter. A1. In SQL Server 2005 we have separated database cache, Buffer Pool, and the rest of the…

0