This article is half-done without your Comment! *** Please share your thoughts via Comment ***
How to find data page information?
- Execute DBCC IND to check allocated pages of a Table.
- Execute DBCC PAGE to find more information about the allocated pages.
What is a Global Allocation Map (GAM)?
GAM has a bit for every extent and it records what extents have been allocated for any use.
It stores information about both Uniform and Mixed extents.
If the bit value is 0, extent is in use and If the bit value is 1, extent is free.
It can hold information about the total 64000 extents and approximately size is 4000 MB.
What is a Shared Global Allocation Map (SGAM)?
SGAM has a bit for every extent and it records what extents are currently being used as mixed extent and have at least one unused page.
If the bit value is 0, either extent is not a mixed extent or it is a mixed extent and all its pages being used.
If the bit value is 1, it is a mixed extent and having an at least one unused page.
What is a Differential Changed Map (DCM)?
This tracks the extents that have changed since the last BACKUP DATABASE statement. If the bit for an extent is 1, the extent has been modified since the last BACKUP DATABASE statement. If the bit is 0, the extent has not been modified.
What is a Bulk Changed Map (BCM)?
This tracks the extents that have been modified by bulk logged operations since the last BACKUP LOG statement. If the bit for an extent is 1, the extent has been modified by a bulk logged operation after the last BACKUP LOG statement. If the bit is 0, the extent has not been modified by bulk logged operations.
What is RAID and what are different types of RAID configurations?
RAID stands for Redundant Array of Independent Disks. RAID defines data storage schemes to divide and replicate data among various disks so that data reliability and I/O performance can be increased.
What is a Connection in SQL Server?
A connection is established when the user is successfully logged in. The user can then submit one or more Transact-SQL statements for execution. A connection is closed when the user explicitly logs out, or the connection is terminated.
What is a SQL Batch?
A SQL batch is a set of one or more Transact-SQL statements sent from a client to an instance of SQL Server for execution. It represents a unit of work submitted to the Database Engine by users.
What is a Fiber?
A fiber is a lightweight thread that requires fewer resources than a Windows thread and can switch context when in user mode. One Windows thread can be mapped to many fibers.
What is a Worker Thread?
The worker thread represents a logical thread in SQL Server that is internally mapped (1:1) to either a Windows thread or, if lightweight pooling is turned ON, to a fiber.
What is the use of lightweight pooling parameter?
The server configuration option lightweight pooling controls whether an instance of SQL Server uses Windows threads or fibers.
The default for this option is 0. This indicates that the instance of SQL Server schedules a Windows thread per worker thread.
If lightweight pooling is set to 1, SQL Server uses fibers instead of Windows threads.
What is the priority boost option?
By default, each instance of SQL Server is a priority of 7, which is referred to as the normal priority. The priority boost configuration option can be used to increase the priority of the threads from an instance of SQL Server to 13. This is referred to as high priority.
What is the importance of Buffer in SQL Server?
The primary purpose of a SQL Server database is to store and retrieve data.
The disk I/O operations can consume many resources and take a relatively long time to finish, SQL Server focuses on making I/O highly efficient.
Buffer management is a key component in achieving this efficiency.
It caches the repeated data and execution plan in the buffer pool to reduce the disk I/O.
What is the size of the buffer in a memory?
A buffer is an 8-KB page in memory, the same size as a data or index page.
How an internal buffer manager manages the buffer cache?
The buffer manager manages the functions for reading data or index pages from the database disk files into the buffer cache and writing modified pages back to disk.
A page remains in the buffer cache until it has either not been referenced for some time or the buffer manager needs the buffer area to read in more data. Data is written back to disk only if it is modified. Data in the buffer cache can be modified multiple times before being written back to disk.
What is a ramp-up?
The interval between SQL Server startup and when the buffer cache obtains its memory target is called ramp-up.
The ramp-up depends on the number and type of client requests and it is transforming single-page read requests into aligned eight-page requests.
What is ROW LOCATOR?
If you define a NON CLUSTERED index on a table ,then the index row of a nonclustered index contains a pointer to the corresponding data row of the table. This pointer is called a row locator.
