SQL DBA Interview questions and answers for experienced

Explain about your SQL Server DBA Experience?

Answer:   This is a generic question often asked by many interviewers. Explain what are the different SQL Server Versions you have worked on, what kind of administration of those instances has been done by you. Your role and responsibilities carried out in your earlier projects that would be of significance to the potential employer. This is the answer that lets the interviewer know how suitable you are for the position to which you are being interviewed.

What are the different SQL Server Versions you have worked on?

Answer:    The answer would be depending on the versions you have worked on, I would say I have experience working in SQL Server 7, SQL Server 2000, 2005 and 2008. If you have worked only the some version be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.

What are the different types of Indexes available in SQL Server?

Answer:   The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes what can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.

What is the difference between Clustered and Non-Clustered Index?

Answer:   In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.

In a Non-Clustered index, the leaf level pages does not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.

What are the new features in SQL Server 2005 when compared to SQL Server 2000?

Answer:   There are quite a lot of changes and enhancements in SQL Server 2005. Few of them are listed here:

Database Partitioning

Dynamic Management Views

System Catalog Views

Resource Database

Database Snapshots

SQL Server Integration Services

Support for Analysis Services on a a Failover Cluster.

Profiler being able to trace the MDX queries of the Analysis Server.

Peer-to -Peer Replication

Database Mirroring

What are the High-Availability solutions in SQL Server and differentiate them briefly?

Answer:   Failover Clustering, Database Mirroring, Log Shipping and Replication are the High-Availability features available in SQL Server. I would recommend reading this blog of mine which explains the differences between these 4 features. Comparing the High Availability Features in SQL Server 2005

How do you troubleshoot errors in a SQL Server Agent Job?

Answer:   Inside SSMS, in Object explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right click and choose view history from the drop down menu. The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day). There would information such as the time it took to execute that Job and details about the error occurred.

What is the default Port No on which SQL Server listens?

Answer:   1433

How many files can a Database contain in SQL Server? How many types of data files exists in SQL Server? How many of those files can exist for a single database?

Answer:   A Database can contain a maximum of 32,767 files.

There are Primarily 2 types of data files Primary data file and Secondary data file(s)

There can be only one Primary data file and multiple secondary data files as long as the total # of files is less than 32,767 files.

What is DCL?

Answer:   DCL stands for Data Control Language.

Question.11   what are the commands used in DCL?

Answer:   GRANT, DENY and REVOKE.

What is Fill Factor?

Answer:   Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created / rebuilt.

What is the default fill factor value?

Answer:   By default the fill factor value is set to 0.

Where do you find the default Index fill factor and how to change it?

Answer:    The easiest way to find and change the default fill factor value is from Management Studio, right-click the SQL Server and choose properties. In the Server Properties, choose Database Settings, you should see the default fill factor value in the top section. You can change to a desired value there and click OK to save the changes.

The other option of viewing and changing this value is using sp_configure.

What is a system database and what is a user database?

Answer:   System databases are the default databases that are installed when the SQL Server is installed. Basically there are 4 system databases: Master, MSDB, TempDB and Model. It is highly recommended that these databases are not modified or altered for smooth functioning of the SQL System.

A user database is a database that we create to store data and start working with the data.

What are the recovery models for a database?

Answer:  There are 3 recovery models available for a database. Full, Bulk-Logged and Simple are the three recovery models available.

What is the importance of a recovery model?

Answer:  Primarily, recovery model is chosen keeping in view the amount of data loss one can afford to. If one expects to have minimal or no data loss, choosing the Full recovery model is a good choice. Depending on the recovery model of a database, the behavior of database log file changes.

Recovery Model is one of the mechanisms which controls and manages the growth of the transaction log file.

Full Recovery Model

With Full recovery model, you can recover to an arbitrary point in time.

Full recovery model provides complete protection against data loss. In the unfortunate case of disaster or application\user error, you can restore to the point-in-time by using the available transaction log backups.

Full recovery model is recommended for OLTP databases, where you have mostly short lived transactions and you don’t want to lose data for a committed transaction. There are certain other features – like Always On, Database mirroring, Log shipping, Transaction replication, Change data capture – in SQL Server that require Full recovery model when you are using them.

Bulk-logged Recovery Model

Bulk-logged recovery model is similar to Full recovery model with the exception that bulk data modification operations (Index Creation, SELECT INTO, INSERT SELECT, BCP, BULK INSERT) are minimally logged in this case and hence it reduces the performance impact but at the same time, you might not be able to do point-in-time restore. As a recommended practice, Bulk-logged recovery model is used with full recovery model, i.e. you should generally have Full recovery model for normal operations and switch to Bulk-logged recovery model temporarily when you are starting occasional bulk operations. Finally at completion of bulk operation, reverse back to Full recovery model. It’s also recommended to take a transaction log backup after switching back to Full recovery model if point-in-time recovery is important.

When to use it – Its recommended to switch to Bulk-logged recovery model before starting any occasional bulk operations and then reverse back to Full recovery model after completion

what is Replication?

Answer:   Replication is a feature in SQL Server that helps us publish database objects and data and copy (replicate) it to one or more destinations. It is often considered as one of the High-Availability options. One of the advantages with Replication is that it can be configured on databases which are in simple recovery model.

Question.19    what the different types of Replication and why are they used?

Answer:   There are basically 3 types of replication: Snapshot, Transactional and Merge Replication. The type of Replication you choose depends on the requirements and/or the goals one is trying to achieve. For example Snapshot Replication is useful only when the data inside the tables does not change frequently and the amount of data is not too large, such as a monthly summary table or a product list table etc. Transactional Replication would useful when maintaining a copy of a transactional table such as sales

Common cause of the performance issues

Below are the most common reasons for the performance issues.

• SQL Server Configuration Issues
• Database/table/schema Design and Configuration Issues
• CPU/IO/Memory Bottleneck
• Blocking
• Network Bottleneck
• Poor Indexing Strategy (Missing Index, Fragmented Index, Un-Used Index)
• Out-of-date/missing statistics
• T-SQL Code
• Application Code

T-SQL to Check SQL ErrorLog file location

USE master

GO

xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'

GO

Difference between temporary table and table variable

Temporary Tables There are two types of temporary tables:

Local Temporary Table: Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server

You can create local temporary table adding # sign again the table name

Global Temporary Table: Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

How to kill all sessions that have open connection in a SQL Server Database?

As SQL Server DBAs, many times we need to KILL all Open Sessions against the SQL Server Database to proceed with Maintenance Task, Restore and more…

You can use below different techniques to KILL all open sessions against the database.

Technique – I
Take the database into Single User Mode and execute all the task needs to perform against the database.

ALTER DATABASE [Database Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Once you are finish with all the required task make the database accessible to everyone.

ALTER DATABASE [Database Name] SET MULTI_USER Technique – II Go to Activity Monitor, Select the desired database and right click on the database to KILL the process.

Moving tables from higher version of SQL Server to lower version

1) Right click database in SQL Server 2008 R2 "Tasks".. "Generate scripts" in the wizard, select the entire database and objects in first step. On the "Set Scripting Options" step you should see a button "Advanced" , select this and make sure you select "Script for Server Version" = SQL Server 2008" not R2 version. This is a crucial step, because "import data" by itself does not bring along all the primary keys, constraints and any other objects like stored procedures."

2) Run the SQL script generated on the new install or database instance SQL Express or SQL Server 2008 using the query window or open saved .sql script and execute and you should see the new database.

3) Now right click on the new database and select "Tasks".. "Import Data.." choose source as the R2 database and the destination as the new database. "Copy data from one or more tables or views", select the top checkbox to select all tables and then next step, run the package and you should have everything on a older version. This should work for going back to a 2005 version as well. Hope this helps someone out.

Central Management Server

Central Management Server
SQL Server Central Management Server is just a central repository that holds a list of managed servers. Microsoft has introduced CMS feature in SQL Server 2008 SSMS.

Use of Central Management Server
1. Multiple Server Query Execution, we can execute query against multiple servers and get the result at source.
2.Centralize the management and administration of a number of SQL Server instances from a single source can allow the DBA to save significant time and effort.
3.Evaluate policy against the multiple server from single source.
4.Control Services and bring up SQL Server Configuration Manager
5.Import and export the registered servers:

Pre-requisite:
You must have at least 1 SQL Server 2008 instance which can be used as CMS

Steps to Create CMS and Register Server
1. Open the “Registered Servers” from the “View” Menu in the management studio of SQL server 2008.
2. Right click on the Central Management Servers and select “Register Central Management Server” and Register the SQL Server 2008 instance as CMS.

3. Create the groups under Registered CMS servers to define the group for each server.
4. Right Click on groups and register the all the SQL Server instances as per their group. (SQL Server 2000,2005 and 2008)

 What is Stored Procedure?
A stored procedure is a collection of T-SQL statements. The stored procedure stored in the system tables of the User Database in SQL Server. The system tables used in stored procedure is sysObjects, sysDepends and sysComments.

Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data and stored procedure also returns the output parameter. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_help,sp_helpdb (Alt + F1), sp_renamedb, sp_depends etc.

Stored Procedure can takes 1024 input and returns the 1024 output parameters.

What is Trigger?
Trigger are used to enforce the business rules in the RDBMS. A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the RDBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed. The RDBMS automatically fires the trigger as a result of a data modification to the associated table.

There are two types of trigger in SQL Server 1. After Trigger 2. Instead of Trigger

What is View?
A view is one type of virtual tables which only stores the SELECT query without data. User can perform the Insert/Update/Delete operation on the view. View can give us the better security.

User can define the index on views. 

What is Index?
Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.

Indexes are of two types. Clustered indexes and non-clustered indexes. When you create a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it’s row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.

If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

What is the use of DBCC commands?
DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.
E.g. DBCC CHECKDB – Ensures that tables in the db and the indexes are correctly linked.
DBCC CHECKALLOC – To check that all pages in a db are correctly allocated.
DBCC CHECKFILEGROUP – Checks all tables file group for any damage.

What’s the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

When is the use of UPDATE_STATISTICS command?
This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

What types of Joins are possible with Sql Server?
Joins are used in queries to explain how different tables are related. Joins also let us select data from a table depending upon data from another table.
Types of joins: SELF JOINs, MERGE JOINs, INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

What is SQL Profiler?

It is a tool which help us to profiling the activities at the database level. It is the good practice to use the profiler from the different machine rather than the production machine.

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. We can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performance by executing too slowly.
Use SQL Profiler to monitor only the events in which you are interested.

Which TCP/IP port does SQL Server run on? How can it be changed?

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties –> Port number.both on client and the server.

Where are SQL server users names and passwords are stored in sql server?
They get stored in master db in the sysxlogins table.

What are the different types of replication? Explain.
The SQL Server 2000-supported replication types are as follows:
· Transactional
· Snapshot
· Merge
Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and applied to Subscribers.

Merge replication is the process of distributing data from Publisher to Subscribers, allowing the
Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when they are connected.

 To get an accurate count of the number of records in a table?

SELECT  COUNT (*)FROM  TABLE_NAME

What is the basic functions for master, msdb, model, tempdb databases?
The Master database stores the information about the sql server configuration, databases, users etc.

The msdb database stores information regarding database backups, SQL Agent information, DTS packages, backup and restore history, SQL Server jobs, and some replication information such as for log shipping.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
The model is essentially a template database used in the creation of any new user database created in the instance.

 What is BCP? When does it used?
BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

 How to know which index a table is using?
SELECT table_name,index_name FROM user_constraints

List few advantages of Stored Procedure.
· Stored procedure can reduced network traffic and latency, boosting application performance.
· Stored procedure execution plans can be reused, staying cached in SQL Server’s memory,
reducing server overhead.
· Stored procedures help promote code reuse.
· Stored procedures can encapsulate logic. You can change stored procedure code without
affecting clients.
· Stored procedures provide better security to your data.

What is an execution plan? When would you use it? How would you view the execution plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.