DBA- Interview Questions & Answers

Hi Friends,

This blog is based on the interview based questions.  I have collected, summarized & prepared the answers and presenting in front of you.

These questions are based on Page & Extents in SQL Server.

1. What is the fundamental unit of data storage in SQL server?

Ans. Page

     2. What is the page size?

Ans. 8 kB

3. What is the page header size?

Ans. 96 byte and it is used to store system info about the page

4. Name the different types of pages in SQL server?

Ans.

• Data
• Index
• Text/Image
• GAM (Global Allocation Map)
• PFS (Page Free Space)
• IAM (Index Allocation Map)
• BCM (Bulk Changed Map)
• DCM (Differential Changed Map)

5. Does log files contains pages?

Ans. No, log files contains only a series of log records

6. Name the type of extents?

Ans.

1. Uniform Extents
2. Mixed extents

7. What is PLE (Page Life Expectancy)?

Ans. It is a perfmon counter, found in the SQL server buffer manager. PLE is the best   indication of how volatile your buffer pool is. Volatility is measured by taking the   average”life” of a page within the buffer pool.

So if lots of pages are being overwritten with new data very often, the average PLE will be low and our BP volatility will be high.

Formula:

PLE threshold= ((maxbp(mb)/1024)4)*300

We can find the vaule of PLE by running this command

T-SQL

[SELECT [object_name],

[counter_name],

[cntr_value] FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE ‘%Manager%’

AND [counter_name] = ‘Page life expectancy’]

8. What is PFS?

Ans. Page free space (PFS) pages record the allocation status of each page. After an          extent has been allocated to an object, the Database Engine uses the PFS pages to    record    which pages in the extent are allocated or free. This information is used when the Database Engine has to allocate a new page.

A PFS page is the first page after the file header page in a data file (page id 1) followed by GAM & SGAM pages.

9. What is DCM?

Ans. DCM stands for differential changed map. This tracks the extents that have changed since the last backup database statement. Differential backup just reads the DCM pages to determine which extents have been modified.

10. What is BCM?

Ans. BCM stands for bulk changed map. This tracks the extents that have changed since the last backup log statement. Although BCM pages appear in all databases, they are only relevant when the database is using the bulk logged recovery model.

Note

Your likes & comments are always appreciated.