SQL Server DBA Interview Questions and Answers

SQL Server is a relational database management system (RDBMS) from Microsoft designed for the enterprise environment. SQL Server adds a number of features to standard SQL, including transaction control, exception and error handling, row processing, and declared variables.

• Installation, Administration and Maintenance of SQL Server Instances.
• Setup Test, Dev, Staging and Production Environments.
• Create Users and assign permissions based on the level of database access the user would need.
• Create Linked Servers to SQL Servers and other databases such as Oracle, Access, and Informix etc.
• Design database Backup and Restoration Strategy.
• Once created the database Backups, monitor those backups are being performed regularly.
• From time to time recover the databases to a specific point of time, as per the requests.
• Setup High-Availability as part Disaster Recovery Strategy for the Databases.
• Troubleshoot various problems that arise in a day-to-day work and fix the issues. Monitoring and Performance tuning; Physical Server Level, Database level and query tuning.
• Documenting major changes to the SQL Servers
• Apply Service Packs.

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.

Indexes are used to speed-up query process in SQL Server, resulting in high performance. They are similar to textbook indexes. In textbooks, if you need to go to a particular chapter, you go to the index, find the page number of the chapter and go directly to that page. Without indexes, the process of finding your desired chapter would have been very slow. There are two types of indexes.

Clustered

Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.

The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

Nonclustered

Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.

The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.

You can add non-key columns to the leaf level of the nonclustered index to by-pass existing index key limits, and execute fully covered, indexed, queries. For more information, see Create Indexes with Included Columns. For details about index key limits see Maximum Capacity Specifications for SQL Server.

Both clustered and nonclustered indexes can be unique. This means no two rows can have the same value for the index key. Otherwise, the index is not unique and multiple rows can share the same key value. For more information, see Create Unique Indexes.

Indexes are automatically maintained for a table or view whenever the table data is modified.

I am not going to say one is better than other, but it depends on the requirements. We have number of products in market. But if I have the chance to choose one of them I will choose SQL SERVER because….

• According to the 2005 Survey of Wintercorp, the largest SQL Server DW database is the 19.5 terabytes. It is a database of a European Bank
• High Security. It is offering high level of security.
• Speed and Concurrency, SQL Server 2005 system is able to handles 5,000 transactions per second and 100,000 queries a day and can scale up to 8 million new rows of data per day,

• Finally, more technical peoples are available for SQL SERVER when we compare to any other database.

So that we can say SQL SERVER is more than enough for any type of application.

When SQL Server is crashed or in hung state due to a Memory/Disk/CPU problems it creates a SQL DUMP file. A DUMP files is a file containing a snapshot of the running process (in this case SQL Server) that includes all of the memory space of that process and the call stack of every thread the process has created. There are two major types of DUMP files:

Full DUMP: It contains entire process space and takes lot of time and space

Mini DUMP: It’s a smaller file contains the memory for the call stack of all threads, the CPU registers and information about which modules are loaded.

• Let me explain what happens when we create a database snapshot
• It creates an empty file known as sparse file for each source database data file
• Uncommitted transactions are rolled back, thus having a consistent copy of the database
• All dirty pages will be returned to the disk
• The user can query the database snapshot
• Initially the sparse file contains an empty copy of source database data file
• Snapshot data points to the pages from source database data file
• When any modification occurred (INSERT/DELETE/UPDATE) on source database, all modified pages are copied to the sparse file before the actual modification. That means the sparse file contains the old/point in time data (when the time the snapshot taken).
• Now if you query the snapshot all modified pages are read from sparse file and remaining all unchanged pages are read from the original (source database) data file.

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

• Data
• Index
• Text/Image (LOB, ROW_OVERFLOW, XML)
• GAM (Global Allocation Map)
• SGAM (Shared Global Allocation Map)
• PFS (Page Free Space)
• IAM (Index Allocation Map)
• BCM (Bulk Change Map)
• DCM (Differential Change Map)

All system objects are physically stored in resource database and logically available on every database. Resource database can faster the service packs or upgrades.

Trace flag in SQL Server is to change certain behavior. You can think of them as an “IF” condition in SQL Server. One of the most common heard/used trace flag is 1222 – used for deadlock graph printing in ERROLROG. You can imagine it as below.

If (Trace_flag_1222_enabled == TRUE)

{

Print_Deadlock_graph_in_ERRORLOG()

}

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. For example, if trace flag 3205 is set when an instance of SQL Server starts, hardware compression for tape drivers is disabled. Trace flags are frequently used to diagnose performance issues or to debug stored procedures or complex computer systems.

RAID is a disk system that contains multiple disk drives, called an array, to provide greater performance, fault tolerance, storage capacity, at a moderate cost. While configuring your server system, you typically have to make a choice between hardware RAID and software RAID for the server’s internal disk drives.

RAID systems are widely used as storage solutions to get the best I/O performance, depending whether the application is write intensive or read intensive. DBA RayRankins mentions in his book that for database-related applications, in order to minimize disk head movement and maximize I/O performance, it’s a good practice to spread random I/O’s (data changes) and sequential I/O’s (for the transaction log) across different disk subsystems.

I agree and endorse his view, as SQL Server, or for that matter any other database, is very much an I/O intensive system. RAID is not a part of a database like SQL Server, implementing RAID can directly affect the way SQL Server performs. There are many RAID arrays available such as RAID 0, RAID 1, RAID 3, RAID 4, RAID 5, RAID 6, RAID 10 and RAID 01.

Memory and disk storage both refer to internal storage space in a computer. The term “memory” usually means RAM (Random Access Memory). The terms “disk space” and “storage” usually refer to hard drive storage.

Hotfixs are software patches that were applied to live i.e. still running systems. A hotfix a single, cumulative package that includes one or more files that are used to address a problem in a software product (i.e. a software bug).

In a Microsoft SQL SERVER context, hotfixes are small patches designed to address specific issues, most commonly to freshly-discovered security holes.

SQL Server has 2 Authentication modes; Windows Authentication and SQL Server and Windows Authentication mode also referred as Mixed Mode. To change the Authentication mode, read one of my blogs Changing SQL Server Authentication Mode.

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 order tables etc. Merge Replication is more useful in case of remote / distributed systems where the data flow can be from multiple sites, for example sales done at a promotional event which might not be connected to the central servers always.

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.

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, 2008 and 2012. If you have worked only some version be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.

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.

• T-SQL; Create Database command.
• Using Management Studio
• Restoring a database backup
• Copy Database wizard

Introduced in SQL Server 2008 Transparent Data Encryption (TDE) is a mechanism through which you can protect the SQL Server Database files from unauthorized access through encryption. Also, TDE can protect the database backups of the instance on which TDE was setup.

Database Mirroring runs in 2 operating modes High-Safety Mode and High-Performance Mode.

High-Safety Mode is to ensure that the Principal and Mirrored database are synchronized state, that is the transactions are committed at the same time on both servers to ensure consistency, but there is/might be a time lag.

High-Performance Mode is to ensure that the Principal database run faster, by not waiting for the Mirrored database to commit the transactions. There is a slight chance of data loss and also the Mirrored database can be lagging behind (in terms being up to date with Principal database) if there is a heavy load on the Mirrored Server.

Yes! Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning, or virtually through a virtualization layer. Starting with SQL Server 2008, SQL Server supports hot add CPU.

• Requires hardware that supports hot add CPU.
• Requires the 64-bit edition of Windows Server 2008 Datacenter or the Windows Server 2008 Enterprise Edition for Itanium-Based Systems operating system.
• Requires SQL Server Enterprise.
• SQL Server cannot be configured to use soft NUMA
• Once the CPU is added just run RECONFIGURE then sql server recognizes the newly added CPU.

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 livelock 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 livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

Connect using DAC via CMD or SSMS

Connect via CMD

SQLCMD -A –U myadminlogin –P mypassword -SMyServer –dmaster

Once you connect to the master database run the diagnostic quires to find the problem

Correct the issue and restart the server

In the SHRINKFILE command, SQL Server isn’t especially careful about where it puts the pages being moved from the end of the file to open pages towards the beginning of the file.

The data becomes fragmented, potentially up to 100% fragmentation, this is a performance killer for your database;

The operation is slow – all pointers to / from the page / rows being moved have to be fixed up, and the SHRINKFILE operation is single-threaded, so it can be really slow (the single-threaded nature of SHRINKFILE is not going to change any time soon)

Recommendations:

• Shrink the file by using Truncate Only: First it removes the inactive part of the log and then perform shrink operation
• Rebuild / Reorganize the indexes once the shrink is done so the Fragmentation level is decreased.

• SQL Server Error Log: The Error Log, the most important log file, is used to troubleshoot system problems. SQL Server retains backups of the previous six logs, naming each archived log file sequentially. The current error log file is named ERRORLOG. To view the error log, which is located in the %Program-Files%\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG directory, open SSMS, expand a server node, expand Management, and click SQL Server Logs
• SQL Server Agent Log: SQL Server’s job scheduling subsystem, SQL Server Agent, maintains a set of log files with warning and error messages about the jobs it has run, written to the %Program Files%\Microsoft SQL Server\MSSQL.1\MSSQL\LOG directory. SQL Server will maintain up to nine SQL Server Agent error log files. The current log file is named SQLAGENT.OUT, whereas archived files are numbered sequentially. You can view SQL Server Agent logs by using SQL Server Management Studio (SSMS). Expand a server node, expand Management, click SQL Server Logs, and select the check box for SQL Server Agent.
• Windows Event Log: An important source of information for troubleshooting SQL Server errors, the Windows Event log contains three useful logs. The application log records events in SQL Server and SQL Server Agent and can be used by SQL Server Integration Services (SSIS) packages. The security log records authentication information, and the system log records service startup and shutdown information. To view the Windows Event log, go to Administrative Tools, Event Viewer.
• SQL Server Setup Log: You might already be familiar with the SQL Server Setup log, which is located at %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt. If the summary.txt log file shows a component failure, you can investigate the root cause by looking at the component’s log, which you’ll find in the %Program-Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files directory.
• SQL Server Profiler Log: SQL Server Profiler, the primary application-tracing tool in SQL Server, captures the system’s current database activity and writes it to a file for later analysis. You can find the Profiler logs in the log .trc file in the %Program Files%\Microsoft SQL Server\MSSQL.1\MSSQL\LOG directory.

Run DBCC LOGINFO; Number of rows returned = Total number of VLF. If it is more than 50 means we need to control the Auto-growth rate. Number of times Auto Grow happens means it increases the number of VLF’s.

On SQL Server 2005, installing SQL Server failover cluster is a single step process whereas on SQL Server 2008 or above it is a multi-step process. That is, in SQL Server 2005, the Installation process itself installs on all of the nodes (be it 2 nodes or 3 nodes). In 2008 or above this has changed, we would need to install separately on all the nodes. 2 times if it is a 2-node cluster or 3 times in a 3 node cluster and so on.

DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

Examples: SELECT, UPDATE, and INSERT statements

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, and DROP statements

DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Examples: GRANT, REVOKE statements

TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

Examples: COMMIT, ROLLBACK statements

Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In an SQL Server, the smallest unit is a page, which is made of Page with size 8K. Every page can store one or more rows based on the size of the row. The default value of the Fill Factor is 100, which is same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit. There will be no or very little empty space left in the page, when the fill factor is 100.

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.

SQL Server provides several options for creating high availability for a server or database. The high-availability options include the following:

• Availability groups
• Database mirroring
• Log shipping
• Failover Cluster Instances

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.

A relational database management system (RDBMS) is a program that lets you create, update, and administer a relational database. Most commercial RDBMS’s use the Structured Query Language (SQL) to access the database, although SQL was invented after the development of the relational model and is not necessary for its use.

• The default SQL Server port is 1433 but only if it’s a default install. Named instances get a random port number.
• The browser service runs on port UDP 1434.
• Reporting services is a web service – so its port 80, or 443 if it’s SSL enabled.
• Analysis service is on 2382 but only if it’s a default install. Named instances get a random port number.

I have used some of the 3rd Party tools:

• SQL CHECK – Idera – Monitoring server activities and memory levels
• SQL DOC 2 – RedGate – Documenting the databases
• SQL Backup 5 – RedGate – Automating the Backup Process
• SQL Prompt – RedGate – Provides IntelliSense for SQL SERVER 2005/2000,
• Lite Speed 5.0 – Quest Soft – Backup and Restore

Benefits using Third Party Tools:

• Faster backups and restores
• Flexible backup and recovery options
• Secure backups with encryption
• Enterprise view of your backup and recovery environment
• Easily identify optimal backup settings
• Visibility into the transaction log and transaction log backups
• Timeline view of backup history and schedules
• Recover individual database objects
• Encapsulate a complete database restore into a single file to speed up restore time
• When we need to improve upon the functionality that SQL Server offers natively
• Save time, better information or notification