SQL Server DBA Interview Questions Part 3

By: Chad Boyd | Read Comments (3) | Print Chad is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server. Related Tips: More

Problem
In the third part of the SQL Server Administration interview series we will outline questions suitable for a DBA interview to assess the candidates skills. These questions primarily relate to database backups, internal database storage, index rebuilds and database extent modification.  In this tip, the questions are there to read, but the answers are intentionally hidden to really test your skills. Once you read the question and have determined your answer, then highlight the answer to see how you did. Good luck!

Solution

Question Difficulty = Easy

• Question 1: What can you do to guarantee any backup is valid?
  • Answer:
    • The only way to guarantee a backup is valid is by restoring the backup.
    • SQL Server 2005 includes a new CHECKSUM option with the backup that will store a checksum value of the entire backup stream or  multiple checksums if multiple backup streams/devices are in the backup set.  The checksum can be optionally validated by a RESTORE operation, but all the backup operation does is calculate the checksum value on the backup stream as it is pushed to disk and writes the final value in each stream/file.
• Question 2: If a database goes down for whatever reason (assume data file is corrupt) and the database cannot be brought back online (i.e. you are in a recovery situation), what must be done first to ensure you can retrieve the latest data modifications assuming the database is set to the FULL recovery model?
  • Answer: Backup the tail of the active transaction log.
• Question 3: What special option/argument must be given to the BACKUP LOG command to perform this action?
  • Answer: NO_TRUNCATE
• Question 4: What options/arguments can be specified in a BACKUP LOG statement to keep inactive log records from being truncated?
  • Answer (SQL Server 2000):
    • NO_TRUNCATE
  • Answer (SQL Server 2005):
    • NO_TRUNCATE
    • COPY_ONLY

Question Difficulty = Moderate

• Question 1: Is the sequence of data rows on given data/index page guaranteed to physically match the logical order of rows (i.e. by index key, row id, etc.)?
  • Answer: No, not guaranteed.  Data rows are put on the page serially, starting immediately after the header. A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page.  Each entry records how far the first byte of the row is from the start of the page.  The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.
• Question 2: What is the fundamental unit in which space and allocation is managed within SQL Server and what is it’s size?
  • Answer: An extent which is 64kb or 8 pages.
• Question 3: If you need to REBUILD a non-clustered index that is 10GB in size and have 5GB of free data-file space available with no room to grow the data file(s), how can you accomplish the task?
  • Answer: When rebuilding an existing non-clustered index, you typically require free space that is approximately equivalent to 2.2 times the size of the existing index, since during a rebuild operation, the existing index is kept until the rebuilt structure is complete (which requires 2x the space) and an additional approximately 20% free space for temporary sorting structures used during the rebuild operation (this is variable depending on the data and cache available). 
  • In this case, you would require at least an additional 10+ GB of free space for the rebuild operation to succeed, since the index itself is 10GB in size.
  • NOTE: Using SORT_IN_TEMPDB would not suffice in this case, since only the temporary sort tables are stored in tempdb in this case, and at least 10 GB of free space would still be required in the database data files.
  • Your possibilities are different for SQL Server2000 vs. SQL Server 2005:
    • In SQL Server 2000, you only have 1 option in this case.  Drop the index and recreate it.  Dropping the index would free the 10gb of space it currently consumes, leaving 15gb of total free space. At that point, the index could be re-created.
    • In SQL Server 2005, you can do the same as you did with SQL Server 2000 (drop, recreate), but you also have another option that would not require knowing the structure of the index in question. If you first disable the index (via the ALTER INDEX...DISABLE statement) the existing space consumed by the index will be freed. Then running a simple ALTER INDEX...REBUILD command will allow the build operation to use the now 15gb of free space to build the index.

Question Difficulty = Difficult

• Question 1: What special type of page within SQL Server that is responsible for tracking (via a simple bitmap) extents that have been modified since the last "BACKUP DATABASE" statement?
  • Answer:
    • "DCM" (Differential-Changed-Map) pages. 
    • Differential backups read just the DCM pages to determine which extents have been modified.  This greatly reduces the number of pages that a differential backup must scan.  The length of time that a differential backup runs is proportional to the number of extents modified since the last BACKUP DATABASE statement and not the overall size of the database.
    • The interval between DCM pages and BCM pages is the same as the interval between GAM and SGAM page, 64,000 extents. The DCM and BCM pages are located behind the GAM and SGAM pages in a physical file.

Next Steps

• Check out the previous SQL Server interview  tips:
  • Interview Questions - SQL Server Concurrency and Locking
  • Interview Questions - SQL Server Administration (Tip 1 of 3)
  • Interview Questions - SQL Server Administration (Tip 2 of 3)
• Check out all of the Professional Development tips on MSSQLTips.com. If you have a question that you ask when you interview DBAs that is a real stumper, send it to tips@mssqltips.com and we will include it in one of our future tips.

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

Tuesday, March 15, 2011 - 9:35:14 AM - Suresh
Question 1: What can you do to guarantee any backup is valid? You can use RESOTRE VERIFYONLY to verify your backup. This verifies your backup but does not restore the database.  This is one of the answers for your question.   Thanks, Suresh

Tuesday, March 15, 2011 - 10:36:05 AM - tcstl

Only thing that disturbs me is lack of questions about data and process analysis.  Your questions work great in a large generic shop, which is not reality.  Most of the questions you are asking, if a DBA doesn't know it but can find the answer I am good with that.  I'll take a DBA who can understand processes and be able to analyze data, and data requirements any day over someone who can answer these questions.    In most medium / small companies these are probably the most essential traits required for the job.

Tuesday, March 15, 2011 - 5:37:00 PM - Joe
RESOTRE VERIFYONLY verifies that the backup file is valid, but not the actual data structure. Nothing short of an actual restore gives positive proof that you can recover from a complete loss of the db.

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code