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.

SQL-dba-interview-questions-answers

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

  1. What is the page header size?

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

  1. 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)
  1. Does log files contains pages?

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

  1. 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.

 

About Mirza Husain

Mirza Husain is a senior consultant-database and having more than 10+ years of experience in the IT industry with different domains. He is fond of writing and speaking about SQL Server and also keen to learn new technologies. He is holding MCA degree and having Microsoft certifications as MCP & MCTS. He is also ITIL certified. In his past years, he worked with many clients as Microsoft, Bank of America etc. Currently he is associated with a France based client and offering his best services. You may reach him on his email id -- mirza_dba@outlook.com. Thanks!
This entry was posted in Database Administration, Q & A, Sql Server, T-SQL and tagged , , , , , , , , , , , , , , , . Bookmark the permalink.

2 Responses to DBA- Interview Questions & Answers

  1. Thank you ! Not too sure I understand the PLE threshold calculation. 😦

    Liked by 1 person

Leave a Reply to rickwillemain Cancel reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s