Slava Oks's WebLog

Our customers observed very interesting behavior on high end Hyperthreading (HT) enabled hardware. They noticed that in some cases when high load is applied SQL Server CPU usage increases significantly but SQL Server performance degrades. Occasionally...

Q: Hello Slava, I would like to confirm my understanding that on SQL 2005 64 bit edition it is recommended to grant Lock Pages in Memory right to the SQL account and then turn on the AWE setting. Thanks A: Yes, we do recommend to turn on Lock pages...

Resource Monitor and Memory Pressure When configuring SQL Server it is very important to understand how it reacts to memory pressure. I have already spent significant amount of time describing types of memory pressure. In this post you will understand...

SQLOS's memory manager consists of several components such as memory nodes, memory clerks, memory caches, and memory objects. Fig 1. depicts memory manager components and their relationship: ---------------- | Memory Object | ---------------- | | V -...

This is my first post in this category so I am really looking forward to your comments :-) Today I would like to cover a specific feature related to our implementation of C++ exception handling. Every day I realize that not many people are familiar with...

So far I touched on subjects that have been widely discussed in the industry. Today I would like to talk about a subject that you would hardly find information about: memory pressure.. On a surface the subject appears simple but in reality this is not...

As I promised last time here comes next post on memory J . Remember, my eventual goal is to reveal how memory management works in SQL Server but for you to really appreciate it, I think, you do need to get good feeling on how Windows manages memory. Understanding...

Stack subject in Windows OS is fascinating. There are so many interesting technical problems surrounding it. The moment you think you fully understand everything about stack you suddenly discover yet another mystery. And so it goes. In the last couple...

Today I received following question: Hello, We will be running Windows Server 2003 x64 with SQL Server 2005 x64. I would think AWE would not come into the picture in this scenario. I'm trying to find out more about SQL Server 2005 memory management...

Yesterday I gave a talk around SQL Server 2005's memory manager. The talk was rather chalk talk. It included memory manager changes in SQL 2005 as well as changes post CTP15. For the talk I prepared several slides - basically talking points that I thought...

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...

We have already talked about Windows AWE mechanism on 32 bit and how SQL Server utilizes it. Today I would like to go over AWE & related mechanism on 64 bit platforms. To some people it comes as a surprise that AWE mechanism is still present...

SQL Server 2005 has a large number of DMVs that you can take advantage of especially when you try to understand behavioral characteristics of your SQL Server 2005 installation. DMVs expose enormous amount of information but how can you make use of it...

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...

Yukon caching vs SQL2000 What makes Yukon memory management different from SQL2000 is elaborative caching framework. In SQL2000 there are two major caches: data page cache, called Buffer Pool and procedure cache, cache of query plans. Buffer Pool and...

Memory is a set of common resources shared by applications, their components and operating system. One needs to be very specific when referring to a given memory resource. Mistakenly, in many cases, developers, users, DBAs refer to different memory resources...

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...

In order for server side applications to run efficiently on modern hardware they have to understand hardware layout. As we are moving towards multicore and multi node NUMA systems, understanding relationships amongst cores, logical and physical CPU has...

As you would expect in the last several months SQL 2005 went through rigorous testing. During the period we have identified and put several improvements that you might be interested to hear about. Memory Broker Analyzing SQL Server's behavior...

Sometimes I am surprised how issues come up the moment you mention them J . Recently, I have discussed Soft NUMA configurations in the article http://blogs.msdn.com/slavao/articles/441058.aspx . This week one of our customers had an interesting problem...

You probably have used dbcc memory status command in SQL2000. In Yukon we have changed output of dbcc memorystatus to reflect new memory manager design, covered in my previous post. The first part of the output represents overall amount of memory consumed...

With SQL Server 2005 Beta 1 and Beta 2 you might have noticed that there is no ums.dll in the bin directory. The reason is simple it is no longer exists. In SQL2000 ums.dll provided SQL Server with user mode non-preemptive scheduling. So what happened...

sys.dm_os_schedulers Q. Do I need to by more CPUs? In order to answer this question you have to find out if your load is really CPU bounded. Your load is really CPU bounded if a number of runnable tasks per each scheduler always greater than...

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...

When we started working on the next release of SQL Server, Yukon, we realized that supportability features are keys to our success. Eventually I am planning on covering ones that are related to low level "stuff", something that many of you, I know, really...