SQL server – not enough memory scenario

Asked By Jackie_Griffith 0 points N/A Posted on - 11/08/2011

Can anyone brief me what will happen if there is not enough memory space in an SQL server processing a query as hash match? Will it spill to the hard-disc?

• Status: Open
• Question Views: 935
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS SQL Server

Best Answer by seemee

Go To Solution

Best Answer

Best Answer

Answered By seemee 0 points N/A #107806

SQL server – not enough memory scenario

What happens when an SQL server experiences low memory space largely depends on the architecture on which it’s running. Different computer architectures have different ways of handling computer memory. In most architectures, the server creates an area in memory called MemToLeave during start up and uses this to hand out the small bits of work. It then allocates some of the remaining memory to the Buffer pool to handle data input and output. When the server experiences low memory, a process called the LazyWriter runs and flushes the buffer to release memory from unwanted data or asks for more memory from the operating system. If memory is not available, it pages some data into the virtual memory to create some free memory in the RAM.

About seemee

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 11/15/2011
• Question Category: MS SQL Server

Answered By seemee 0 points N/A #107807

SQL server – not enough memory scenario

Generally, the physical memory is subdivided into sections called pages. When the server runs out of physical memory, it is swaps part of the work into a section of the hard disk called a page file. This also happens whenever the information in a certain page is not requested for in a transaction for a long time. This process is slow due to the read and writes cycles in the page file which is slow as it is located on the hard disk. This is no different to when an SQL server is processing a query as hash match as it is the OS that manages the memory and not the server.

About seemee

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 11/15/2011
• Question Category: MS SQL Server