Q&A: Database Administration

1. What is RAID and what are different types of RAID configurations?

RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are four popular RAID levels 0, 1, 5 and 10, offering different levels of performance and fault tolerance. MSDN has some information about RAID levels.

2. What are the steps you will take to improve performance of a poor performing query?

There are a lot of reasons behind the poor performance of a query like: no indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables. Some of the tools that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer. Download the white paper on performance tuning SQL Server from Microsoft web site.


3. What are the steps you will take, if you are tasked with securing an SQL Server?

Preferring NT authentication, using server, database and application roles to control access to the data, securing the physical database files using NTFS permissions, using an unguessable SA password, restricting physical access to the SQL Server, renaming the Administrator account on the SQL Server computer, disabling the Guest account, enabling auditing, using multiprotocol encryption, setting up SSL, setting up firewalls, isolating SQL Server from the web server and so on. Read the white paper on SQL Server security from Microsoft website.

4. What is a deadlock and what is a live lock? How will you go about resolving deadlocks?

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user’s process. A live lock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A live lock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely. Check out SET DEADLOCK_PRIORITY and “Minimizing Deadlocks” in SQL Server books online. Also check out the article Q169960 from Microsoft knowledge base.

5. What is blocking and how would you troubleshoot it?

Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first. Read up the following topics in SQL Server books online: Understanding and avoiding blocking, Coding efficient transactions.

6. Explain CREATE DATABASE syntax.

Many of us are used to creating databases from the Enterprise Manager or by just issuing the command: CREATE DATABAE MyDB. But what if you have to create a database with two filegroups, one on drive C and the other on drive D with log on drive E with an initial size of 600 MB and with a growth factor of 15%? Check this out.


7. How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?

SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal configuration mode. Check out SQL Server books online for more parameters and their explanations.

8. As a part of your job, what are the DBCC commands that you commonly use for database maintenance?

DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC, DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there are a whole load of DBCC commands which are very useful for DBAs. Check out SQL Server books online for more information.

9. What are statistics, under what circumstances they go out of date, how do you update them?

Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query. Some situations under which you should update statistics: 1) If there is significant change in the key values in the index 2) If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated 3) Database is upgraded from a previous version. Look up SQL Server books online for the following commands: UPDATE STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP STATISTICS, sp_autostats, sp_createstats, sp_updatestats

10. What are the different ways of moving data/databases between servers and databases in SQL Server?

There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, dettaching and attaching databases, replication, DTS, BCP, logshipping, INSERT…SELECT, SELECT…INTO, creating INSERT scripts to generate data.

11. Explain different types of BACKUPs avaialabe in SQL Server? Given a particular scenario, how would you go about choosing a backup plan?

Types of backups you can create in SQL Sever 7.0+ are Full database backup, differential database backup, transaction log backup, filegroup backup. Check out the BACKUP and RESTORE commands in SQL Server books online. Be prepared to write the commands in your interview. Books online also has information on detailed backup/restore architecture and when one should go for a particular kind of backup.

12. What are different options for importing data into SQL server?

Normally we use the Import / Export wizards, DTS or SSIS to move data into SQL Server. But there are a few other built in options for doing this like bcp, BULK INSERT, and OPENROWSET.

BULK INSERT AdventureWorks.dbo.ImportText FROM ‘C:\peace.txt’ WITH (FIELDTERMINATOR=‘,’, FIRSTROW=2)

13. What is database replication? What are the different types of replication you can set up in SQL Server?

Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios: (a) Snapshot replication (b) Transactional replication (with immediate updating subscribers, with queued updating subscribers) (c) Merge replication. See SQL Server books online for in depth coverage on replication. Be prepared to explain how different replication agents function, what are the main system tables used in replication etc.

14. How to determine the service pack currently installed on SQL Server?

The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed. To know more about this process visit SQL Server service packs and versions.

15. How do you find the list of tables in a database?

  • To view all the objects (tables, view, triggers and stored procedures) in a database, use: sp_help
  • To view only the tables in the database, execute: SELECT name FROM <database name>..sysobjects WHERE type=’U’

16. How to list the databases on your SQL Server?

SELECT name FROM master..sysdatabases

17. Why do I get an unordered resultset, although my table has a clustered index?

Although the presence of a clustered index often seems to produce an ordered resultset, there is no guarantee that this will work in all cases. If you execute a SELECT statement without an explicit ORDER BY, SQL Server will try to return the information in the fastest possible way. This might be or might be not equal to the order of the clustered index keys.For example, there might already be data pages in the buffer pool present and SQL Server might opt to return those first before reading the remaining data from disk. Another example might be when parallelism is involved and more than one CPU is used to return the rows.

If you need to rely on the return of an sorted resultset, you need to have an explicit ORDER BY criteria.

18. Why can it take so long to drop a clustered index?

Generally speaking, indexes can speed up queries tremendously. This comes at the cost, as changes to the underlying data have to be reflected in the indexes when the index column(s) are modified.

Before we get into the reasons why dropping a clustered index can be time-consuming, we need to take a short look at the different index structures in SQL Server.

Every table can have one, and only one clustered index. A clustered index sorts the data physically according to its index key. And since there can only be one physically sort order on a table at a time, this sounds pretty obvious. If a table does not have a clustered index, it is called a heap.

 The second index structure is called a non-clustered index. You can create non-clustered indexes on tables with clustered indexes, heaps, and indexed views. The difference between both index structures is at the leaf level of the index. While the leaf level of a clustered index actually is the table’s data itself, you only find pointers to the data at the leaf level of a non-clustered index. Now we need to understand an important difference:

  • When a table has a clustered index created, the pointers contain the clustered index keys for that row. 
  • When a table does not have a clustered index, the pointers consist of the so-called RowID, which is a combination of FileNumber:PageNumber:Slot.

When you understand this distinction, you can derive the answer to the original question yourself. When you drop a clustered index, SQL Server will have to recreate all non-clustered indexes on that table (assuming there are any). During this recreation, the clustered index keys are replaced by the RowID. This is a time-consuming operation, especially on larger tables or tables with many indexes.

19. Is it possible to keep the database in memory?

In a very real sense, SQL Server automatically attempts to keep as much of the database in memory as it can.By default, when SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free physical memory available. If there is more memory free, SQL Server recommits memory to the buffer cache, which SQL Server uses to store data for ready access. SQL Server adds memory to the buffer cache only when its workload requires more memory; a server at rest does not grow its buffer cache. SQL Server allocates much of its virtual memory to a buffer cache and uses the cache to reduce physical I/O. Each instance of SQL Server automatically caches execution plans in memory based upon available memory. Data is read from the database disk files into the buffer cache. Multiple logical reads of the data can be satisfied without requiring that the data be physically read again. By maintaining a relatively large buffer cache in memory, an instance of SQL Server can significantly reduce the number of physical disk reads it requires. Another method of providing performance improvement is some cases it to use DBCC PINTABLE, which is used to store specific tables in memory on a more or less permanent basis. It works best for small tables that are frequently accessed. The pages for the small table are read into memory one time, and then all future references to their data do not require a disk read. SQL Server keeps a copy of the page available in the buffer cache until the table is unpinned using the DBCC UNPINTABLE statement. This option should be used sparingly as it can reduce the amount of overall buffer cache available for SQL Server to use dynamically.Other than what has been described above, there is no command to tell SQL Server to store an entire database in memory all the time. The reason for this is because for most databases, there are portions of it that are rarely used, and there is no point in storing data in memory if it is rarely or never accessed. It is more efficient to only store in memory that data that is accessed frequently, and get rarely used data only upon specific request.

20. How can I determine if a table is pinned in SQL Server memory or not?

First, the following code pins the “authors” table in pubs:

DECLARE @db_id int, @tbl_id int
USE pubs
SET @db_id = DB_ID(‘pubs’)
SET @tbl_id = OBJECT_ID(‘pubs..authors’)
DBCC PINTABLE (@db_id, @tbl_id)
SELECT @tbl_id

Next, a table is not really pinned until after it is retrieved from disk into the buffer, so you need to do this with the following command:

SELECT * FROM authors

Now to see if the authors table is pinned or not, you can run the following code. If the table is pinned, you will get a message that it is, if it is not pinned, then you won’t get any return information about pinned tables.

IF OBJECTPROPERTY (object_id(‘authors’),’TableIsPinned’) = 1
PRINT ‘TableIsPinned’

When you are done, you will want to unpin your table. To do so, run this example code:

DECLARE @db_id int, @tbl_id int
USE pubs
SET @db_id = DB_ID(‘pubs’)
SET @tbl_id = OBJECT_ID(‘pubs..authors’)
DBCC UNPINTABLE (@db_id, @tbl_id)

Of course, this example code only will check one table at a time. To check all your tables, run the following code:



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