SQL Server: Internal Storage and Memory Architecture Interview Questions and Answers (Day-4)

How SQL Server virtual address space for the buffer pool?


What is min server memory and max server memory?


buffer pool never drops its memory allocation below the level specified in min server memory, and never acquires more memory than the level specified in max server memory.

An organization requires to add more memory in the running SQL Server so is it require to restart SQL Server service?

No, it is not required to restart a SQL Server because the buffer manager supports Hot Add Memory, which allows users to add physical memory without restarting the server.

What is a dynamic management view to monitor pages in the buffer cache?

You can use the sys.dm_os_buffer_descriptors view to monitor the pages in the buffer cache.

What is a Torn Page Protection?

Torn page protection is primarily a way of detecting page corruptions due to power failures.

It is managing a 2-bit signature is placed at the end of each 512-byte sector at the page and base on this signature value it detects the torn pages.

Why Checksum Protection is a very important?

Checksum protection is a stronger data integrity checking.

A checksum is calculated for the data in each page that is written, and stored in the page header. Whenever a page with a stored checksum is read from disk, the Database Engine recalculates the checksum for the data in the page and raises error 824 if the new checksum is different from the stored checksum.

What is the role of Named Pipes protocol?


What is a Page Header?


What is PFS?

PFS page is the second page in the data file followed by file header. PFS pages are used to track page level allocation. PFS page repeat after every 8088 pages.







What is Boot Page?



What is an IAM page?

IAM stands for Index Allocation Map: To catalog all the pages that belong to a single allocation unit, SQL Server utilizes a special type of pages, the Index Allocation Map or IAM pages. Each allocation unit has at least one IAM Page and depending on the size of the table there might be many.

What is a Fill Factor?


What are Virtual Log files? How these are created?


Important DMV to find memory related information. (From SQL Server 2008)

What is Locked pages in Memory (LPIM) concept?

Locked pages in memory is a privilege given to SQL Server service account which allows SQL Server not to trim its memory excessively when SQLOS ask SQL Server to do so.

When OS will face memory pressure and in turn inform to SQLOS and then SQLOS will ask SQL Server to trim it consumption. Now if LPIM privilege is not there SQL Server will start trimming its memory consumption.

To avoid this it is better to give optimum value to SQL Server memory and leave enough RAM for OS and then give LPIM privilege.

What is WOW?

When SQL Server 32 bit runs on 64 bit version of windows it is called WOW.

How you resolved out of memory error?

Make sure you have set OPTIMUM value for MAX SERVER MEMORY SETTING. This is one which should be adopted as best practice.

Try to find out whether it is internal memory pressure or external. By that I mean if SQL Server is facing memory crunch it will be internal if there is less physical RAM on system it will be external.

Try to find if it is SQL Server VAS pressure. Generally in error message if you get like ‘Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE ‘ this points to fact that OOM error came because there was VAS pressure.

What is the difference between Bufferpool and Virtual memory (VAS)?

No buffer pool and VAS are not the same thing. In SQL Server direct physical memory access is not allowed any memory request which comes is first mapped to process VAS and then if SQL Server find memory free it would map this VAS address to physical memory and then memory becomes committed. Bufferpool is physical memory VAS is virtual memory.



Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

1 Comment on "SQL Server: Internal Storage and Memory Architecture Interview Questions and Answers (Day-4)"

Notify of
Sort by:   newest | oldest | most voted

I love what you guys are usually up too.