SQL Server 2K5 and memory assignment

Question

Two Part Question:

1. What kind of actions does SQL Server process in RAM? Of those that I know are as follows: table variables and CTE's. My colleague also mentioned COUNTS and indexes? I'm not sure how accurate is this.

2. How do I control what kind of data is stored in RAM. I know this is dynamically assigned by SQL Server and it probably does a good job of it. But for academic reasons, does anyhow know the guidelines governing this?

Answer 1

Roughly speaking (and this is hiding some of the details), there are two types of memory use: one is for data pages and the other is for cached query plans. It's obviously more complicated than that but you start to need to know quite a bit about SQL Server's internals.

You don't control what is stored in RAM. The system does it on your behalf.

In an ideal setup, all of the active Databases's hot data pages should be in RAM.

For details:

• Dynamic Memory Management

• Memory Management Architecture

• Memory Used by SQL Server Objects Specifications

Answer 2

You can force a table to be in the cache using DBCC PINTABLE. This command tells SQL Server not to flush the pages for the table from memory.

http://msdn.microsoft.com/en-us/library/ms178015%28SQL.90%29.aspx