10 SQL Server DBA Interview Questions

[UPDATE: See the updated list of 20 questions here.]

Introduction

There have been several blog posts about SQL Server Database Administrator interview questions. I have listed some examples here for your review, but the best thing you can do to prepare for an interview as someone looking for a job is to know SQL Server databases. If you are performing the interview as someone in the hiring process, think about the specific skills you are looking for and target your questions to help identify the applicants strengths and weaknesses.

interview

1. What is a four-part name?

If the candidate ever crosses database boundaries with his queries, they should know this answer. A four-part name refers to the parts of a SQL Server object name that uniquely identifies it in the SQL environment. The first part is the instance. The second part is the database. Third is the schema and fourth is the object name. So if wanted to reference a table called employee from the HumanResources schema in the AdventureWorks database on your Production instance, the four part name would look like this:

Production.AdventureWorks.HumanResources.Employee

2. How do you trace the traffic hitting a SQL Server?

SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.

3. What is a Linked Server?

Another question that can tell you if the candidate has experience with distributed databases is “What is a Linked Server?” A linked server is a reference from one SQL Server server to another. If you have databases on another SQL Server server that contains data you need for your system, you could create a link server on your server to the other SQL Server server. Then, you can use the four-part name of the remote table to use it within your local queries.

4. Why would you use SQL Agent?

SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.

5. What happens on checkpoint?

Checkpoints, whether scheduled or manually executed, cause the transaction log to be truncated up to the beginning of the oldest open transaction (the active portion of the log). That is, the dirty pages from the buffer cache are written to disk. Storing committed transactions in the cache provides a performance gain for SQL Server. However, you do not want the transaction log to get too big because it might consume too many resources and, should your database fail, take too long to process to recover the database. One important thing to note here is that SQL Server can only truncate up to the oldest open transaction. Therefore, if you are not seeing the expected relief from a checkpoint, it could very well be that someone forgot to commit or rollback their transaction. It is very important to finalize all transactions as soon as possible.

6. What are the dynamic management views and what value do they offer?

The DMV’s are a set of system views new to SQL Server 2005 and beyond to gain insights into particular portions of the engine

7. What are the primary differences between an index reorganization and an index rebuild?

  • A reorganization is an “online” operation by default; a rebuild is an “offline” operation by default
  • A reorganization only affects the leaf level of an index
  • A reorganization swaps data pages in-place by using only the pages already allocated to the index; a rebuild uses new pages/allocations
  • A reorganization is always a fully-logged operation; a rebuild can be a minimally-logged operation
  • A reorganization can be stopped mid-process and all completed work is retained; a rebuild is transactional and must be completed in entirety to keep changes

8.How can you control the amount of free space in your index pages?

You can set the fill factor on your indexes. This tells SQL Server how much free space to leave in the index pages when re-indexing. The performance benefit here is fewer page splits (where SQL Server has to copy rows from one index page to another to make room for an inserted row) because there is room for growth built in to the index.

9. Name 3 or more DBCC commands and their associated purpose.

  • DBCC CACHESTATS – Displays information about the objects currently in the buffer cache.
  • DBCC CHECKDB – This will check the allocation of all pages in the database as well as check for any integrity issues.
  • DBCC CHECKTABLE – This will check the allocation of all pages for a specific table or index as well as check for any integrity issues.
  • DBCC DBREINDEX – This command will reindex your table. If the indexname is left out then all indexes are rebuilt. If the fillfactor is set to 0 then this will use the original fillfactor when the table was created.
  • DBCC PROCCACHE – This command will show you information about the procedure cache and how much is being used.
  • DBCC MEMORYSTATUS – Displays how the SQL Server buffer cache is divided up, including buffer activity.
  • DBCC SHOWCONTIG – This command gives you information about how much space is used for a table and indexes. Information provided includes number of pages used as well as how fragmented the data is in the database.
  • DBCC SHOW_STATISTICS – This will show how statistics are laid out for an index. You can see how distributed the data is and whether the index is really a good candidate or not.
  • DBCC SHRINKFILE – This will allow you to shrink one of the database files. This is equivalent to doing a database shrink, but you can specify what file and the size to shrink it to. Use the sp_helpdb command along with the database name to see the actual file names used.
  • DBCC SQLPERF – This command will show you much of the transaction logs are being used.
  • DBCC TRACEON – This command will turn on a trace flag to capture events in the error log. Trace Flag 1204 captures Deadlock information.

10. What authentication modes does SQL Server support?

SQL Server supports Windows Authentication, SQL Server Authentication, and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server.

Conclusion

You can find scores of questions on the internet, as well as questions based on your own experiences. You can find some additional questions here, here, and here.

Advertisements

8 thoughts on “10 SQL Server DBA Interview Questions”

  1. Wonderful article. Thanx with regard to authoring this kind of informative blog post in addition to informative all of us with all your opinions. I’m hoping you will definitely continue to keep this good work in future as well.

    Like

  2. Excellent blog here! Also your site loads up very fast! What host are you using? Can I get your affiliate link to your host? I wish my website loaded up as fast as yours lol

    Like

  3. Hi superb blog! Does running a blog such as this take a massive amount work? I’ve absolutely no knowledge of coding however I was hoping to start my own blog in the near future. Anyhow, if you have any ideas or techniques for
    new blog owners please share. I understand this is off topic but I simply needed to ask. Cheers!

    Like

  4. I constantly spent my half an hour to read this web site’s articles or reviews all the time along with a cup of coffee.

    Like

  5. It’s a pity you don’t have a donate button! I’d without a doubt donate to this brilliant blog! I guess for now i’ll settle for book-marking and adding your RSS feed to my Google account. I look forward to brand new updates and will talk about this blog with my Facebook group. Chat soon!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s