This article is half-done without your Comment! *** Please share your thoughts via Comment ***
How SQL Server virtual address space for the buffer pool?
When SQL Server starts, it computes the size of virtual address space for the buffer pool based on a number of parameters such as amount of physical memory on the system, number of server threads and various startup parameters. SQL Server reserves the computed amount of its process virtual address space for the buffer pool, but it acquires only the required amount of physical memory for the current load.
What is min server memory and max server memory?
The min server memory and max server memory configuration options establish upper and lower limits to the amount of memory used by the buffer pool of the Microsoft SQL Server Database Engine.
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?
Named Pipes is a protocol developed for local area networks. A part of memory is used by one process to pass information to another process, so that the output of one is the input of the other. The second process can be local or remote.
What is a Page Header?
Page number 0- the file header (page type 15). It holds the file header information. There will be only one header page for each data file and that reside in the 0th position. The header page store the file level information like file size, minimum size, max size and file growth.
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.
How many GAM pages will be there in a 7GB data file?
A GAM page can hold information of around 64000 extents. That is, a GAM page can hold information of (64000X8X8)/1024 = 4000 MB approximately. In short, a data file of size 7 GB will have two GAM pages.
How many SGAM pages will be there in a 7GB data file?
A SGAM page can hold information of 64000 extents. That is, a SGAM page can hold information of (64000X8X8)/1024 = 4000 MB. In short, a data file of size 7 GB will have two SGAM page.
How SQL Server engine will decide which extents need to be added into a differential backup?
A differential backup process scans through DCP page to identify the extents which are modified after the last full backup and add those extents in the Differential backup.
What is Boot Page?
There is one special data page that exists only once per database. It is the database boot page. The database boot page is always stored in page 9 of file 1, the first file in the primary file group.
The database boot page contains a broad variety of data about the database itself. Here you can find the internal database version, the compatibility level, the database name and the date of the last log backup.
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?
The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth.
What are Virtual Log files? How these are created?
SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. The Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. The Database Engine tries to maintain a small number of virtual files.
Important DMV to find memory related information. (From SQL Server 2008)
|
1 2 3 4 5 6 7 |
select (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB, (locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB, (total_virtual_address_space_kb/1024 )Total_VAS_in_MB, process_physical_memory_low, process_virtual_memory_low from sys. dm_os_process_memory; |
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.
