Top 50 Sql Dba Interview Questions You Must Prepare 20.Apr.2024

The Query Optimizer initially locks the required Rows for DML operations / Retrieval operations. This also get relevant Pages and Completely table to be 'INTENT' Locked. If more than 50% of rows are specifically locked then this automatically gets the complete PAGE or TABLE to be locked. Lock Escalation mechanism can be controlled by using Locking Hints.

Policy Based Management in SQL SERVER 2012 Administration allows you to define and enforce policies for configuring and managing SQL Server across the enterprise. Policy-Based Management is configured in SQL Server Management Studio (SSMS). Navigate to the Object Explorer and expand the Management node and the Policy Management node; you will see the Policies, Conditions, and Facets nodes.

I rate the following five tasks as the key responsibilities of a DBA.

1. Securing database from physical and logical integrity damage.
2. Restoring database from backup as a part of disaster management plan.
3. Optimizing queries performance by appropriate indexing and optimizing joins, where conditions, select clause etc.
4. Designing new schema, support legacy schema, and legacy database systems.
5. Helping developers improve their SQL-related code writing skill.

SQL profiler is a tool to monitor performance of various stored procedures. It is used to debug the queries and procedures. Based on performance, it identifies the slow executing queries. Capture any problems by capturing the events on production environment so that they can be solved.

Database Mirroring is to be configured with TCP Protocol and ensure that data over each endpoint is encrypted. Better to make use of TDE for more security.

Fill factor specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. Fill factor must be an integer value from 1 to 10@The default is @I prefer to keep my servers default fill factor as 90.

An unique key cant not be referenced as foreign key. And it may allow on null.

We can make use of SQL Profiler , SQL Server Log or use xp_readerrorlog extended Stored Procedure to retrieve the error log information.

TEMPDB, MSDEB, MASTER, MSDB, mssqlsystemresource.

1. The master database holds information for all databases located on the SQL Server instance. As SQL Server cannot start without a functioning master database, we must administer this database with care and monitor Startup Parameters in Configuration Manager.
2. The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
3. The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
4. The model is essentially a template database used in the creation of any new user database created in the instance.
5. The resource Database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

Yes and No. This is tricky question. If you are using repair option with CHECKDB then you have to have the DB in single user mode. Following is the method to have your DB in a single user mode.

Use master
go sp_dboption dbname, single, true

Following is the error which you get when you run the DBCC CHECKDB with repair option wo having the DB in single user mode. The same is true for DBCC CHECKDB also

Types of backups available in SQL Server:

• Complete: This creates a complete stand alone image of the database. This backup is self dependent and can be restored to either the same or a new database on same or other server.
• Differential: This backs up only the modified contents since the last backup. They do not provide much flexibility.
• Transaction log: This backs up all transaction logs since the previous transaction log backup or the complete transaction log backup if there has not been one in past.
• Files and Filegroups backup: This option is suitable when time constraints are high and one cannot afford to perform a complete database backup. It also needs transaction logs backup to take place to make it worth choosing this option. After restoring file backup, apply transaction logs to roll the file contents forward to make it consistent with the database..

BACKUP LOG TestDB WITH TRUNCATE_ONLY is gone. SQL server does not allow you to truncate the log now otherwise whole purpose of a DB is defeated. You have to make sure whether you need log or not. If you do not need log then have the recovery model simple instead of full. If you do not want the log to be accumulated in some particular bulk logging then change the recovery model BULK LOGGED for that duration and take one TLog Backup just before and after this change. I shall discuss this later in my later blog. BACKUP LOG command backs up the t-log and frees the space in the log file.

SQL services will be in manual and stopped. Cluster service will be in automatic and started mode on both the nodes.

Yes resources can be moved after pausing the node. But we can't move them back till the node is paused.

A Table/view SCAN occurs when no useful indexes exist. A TABLE SCAN reads all data, row by row, to find the match.

understand that whole table would be locked for the processing thenn this is better to use TABLOCK hint and get complete table blocked. This is a nice way to avoid the wastage of sql server DB engine processing for lock escalation. Somewhere you may also need to use TABLOCKX when you want an exclusive lock on the table in the query.

Database mirroring can be used with replication to provide availability for the publication database. Database mirroring involves two copies of a single database that typically reside on different computers. At any given time, only one copy of the database is currently available to clients which are known as the principal database. Updates made by clients to the principal database are applied on the other copy of the database, known as the mirror database. Mirroring involves applying the transaction log from every insertion, update, or deletion made on the principal database onto the mirror database.

Service Broker is a message-queuing technology in SQL Server that allows developers to integrate SQL Server fully into distributed applications. Service Broker is feature which provides facility to SQL Server to send an asynchronous, transactional message. it allows a database to send a message to another database without waiting for the response, so the application will continue to function if the remote database is temporarily unavailable.

In SQL SERVER 2008 R2, Data Compression comes in two types viz., Row Compression where It minimizes the metadata (column information, length, offsets, etc.) associated with each record. Numeric data types and fixed length strings are stored in variable-length storage format, just like Varchar. Page compression uses the Row compression technique internally and also applies prefix level compression.For every column in a page, duplicate prefixes are identified. These prefixes are saved in compression information headers (CI) which reside after page headers. A reference number is assigned to these prefixes and that reference number is replaced wherever those prefixes are being used.

This is a very generic question. I would like to describe my generic database testing method as well as stored procedure testing methods.

Testing Databases:

Table Column data type and data value validation.
Index implementation and performance improvement.
Constraints and Rules should be validated for data integrity.
Application field length and type should match the corresponding database field.
Database objects like stored procedures, triggers, functions should be tested using different kinds of input values and checking the expected output variables.

Testing Stored Procedures:

Understand the requirements in terms of Business Logic.
Check if the code follows all the coding standards.
Compare the fields’ requirements of application to the fields retrieved by a stored procedure. They must match.
Repeatedly run the stored procedures several times with different input parameters and then compare the output with the expected results.
Pass invalid input parameters and see if a stored procedure has good error handling.

There are many ways but I prefer following method. Take a scenario when you want to find the error log when the DB was put in a single user mode.

CREATE TABLE #Errorlog (Logdate Datetime, Processinfo
VARCHAR(20),Text VARCHAR(2000))
INSERT INTO #Errorlog
EXEC xp_readerrorlog
SELECT * FROM #Errorlog
WHERE Text Like ‘%SINGLE%USER%’

Server’s configurations and logins.

Plan Freezing is a new feature I never thought of. I find it very interesting! It is included in SQL Server 2008 CTP@SQL Server 2008 enables greater query performance stability and predictability by providing new functionality to lock down query plans. This empowers organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.

SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later.

You cannot restore the database without having a full database backup. However, if you have the copy of all the data files (.mdf and .ndf) and logs (.ldf) when database was in working condition (or your desired state) it is possible to attach the database using sp_attach_db.

Inner Join, Outer (Left Outer & Right Outer) Joins and Cross join.

Select * from sys.dm_db_missing_index_details

The primary job of DBAs is to secure the data. They should be able to keep it safe as well as reproduce it efficiently, whenever required. So as per my view, a Database Administrator who can fulfill the requirements of Securing Data and Retrieving Data is the best DBA.

When I hire a DBA I always ask them questions about backup strategies and efficient restoring methodologies.

They get stored in System Catalog Views sys.server_principals and sys.sql_logins.

Definition of sys objects, which logically shows into all database and DMVs.

Well, your question is very difficult to answer without looking at the code, application and physical server. In such situations, there are a few things that must be paid attention to right away.

Restart Server
Upgrade Hardware

Check Indexes on Tables and Create Indexes if necessary Make sure SQL Server has priority over other operating system processes in SQL Server settings.

Update statistics on the database tables.

Blocking occurs when a process has acquired lock on a set of rows, and another process is trying to acquire a lock on the same set of rows. In such a case, the other process has to wait until the first process finishes its job and releases the lock on the above said rows.

• Use sp_lock procedure to see type of locks acquired by various sessions on the server to find the cause of blocking.
• Problem is hinted by the WAIT status is a lot of rows that are returned as an output of sp_lock stored procedure execution.
• Use sp_who and sp_who2 to return more columns to get more information around the blocking.
• Use DBCC INPUTBUFFER (spid).This will show the last 128 characters of the last T-SQL statement executed from connection referred through spid. This way one can identify the stored procedure or application module that caused blocking.
• To resolve blocking, you can disconnect the connection causing the blocking using KILL command. If this does not solve the problem permanently, then rewrite the stored procedure/module causing the block more efficiently.

NO

It’s stored into root folder SQL server, LOG folder.

Templates of new database objects, like tables and column.

Row versions, cursor, temp objects.

Dynamic Management Views (DMV) return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

create clustered index index_name on empmst(card)

Select @@version

Secondary server. This question is basically asked to find out whether you have a hands on work on log shipping or not.

Select @@version Or select serverproperty (‘productlevel’)

Left Outer join Retrieves the all records from LEFT table and matching from the RIGHT table, and null values where is no match. Right Outer Join just opposite.

You need to add only FAILOVER PARTNER information in your front end code. “Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=AdventureWorks;Integrated Security=True;”.

Use TRUNCATE_ONLY option for SQL Server 2005 systems while performing Backup. This option is not available in SQL Sever 2008 and R@Instead, we use ON_TRUNCATE option available in the Backup statement.

Lock: DB engine locks the rows/page/table to access the data which is worked upon according to the query.

Block: When one process blocks the resources of another process then blocking happens. Blocking can be identified by using

SELECT * FROM sys.dm_exec_requests where blocked <> 0
SELECT * FROM master..sysprocesses where blocked <> 0

Deadlock: When something happens as follows: Error 1205 is reported by SQL Server for deadlock.

Let us first see the fully qualified query name to access a table for SQL Server 2000 and SQL Server 2005.

SQL Server 2000: [DataBaseServer].[DataBaseName].[ObjectOwner].[Table]
SQL Server 2005: [DataBaseServer].[DataBaseName].[Schema].[Table]
SQL Server 2008: [DataBaseServer].[DataBaseName].[Schema].[Table]

In SQL Server 2000, prior to dropping the user who owns database objects, all the objects belonging to that user either need to be dropped or their owner has to be changed. Every time a user is dropped or modified, system admin has to undergo this inconvenient process.

In SQL Server 2005 and the later versions, instead of accessing a database through database owner, it can be accessed through a schema. Users are assigned to schemas, and by using this schema a user can access database objects. Multiple users can be assigned to a single schema, and they all can automatically receive the same permissions and credentials as the schema to which they are assigned. Because of the same reason in SQL Server 2005 and the later versions – when a user is dropped from database – there is no negative effect on the database itself.

Steps to take to improve performance of queries:

• Use indexes efficiently
• Create all primary and foreign keys and relationships among tables.
• Avoid using cursors
• Avoid using Select*, rather mention the needed columns and narrow the resultset as needed.
• Denormalize
• Use partitioned views
• Use temporary tables and table variables
• Reduce joins and heavy clauses like GROUP BY if not needed
• Implement queries as stored procedures.
• Have a WHERE Clause in all SELECT queries.
• Use data types wisely
• Instead of NULLS use string values such as N/A

Database isolation comes into play when we need to isolate the database and protect it from other things on the network. This protection is achieved using locks. The type of lock and the level of isolation level needed is referred as isolation level in SQL Server.

Types of isolation levels:

READ COMMITTED: Shared locks are held while any data is being read.

READ UNCOMMITTED: Specifies isolation level 0 locking. There are thus no shared locks or exclusive locks. Lease restrictive of all the isolation levels.

REPEATABLE READ: Locks are applied on all data being used by a query. However, new phantom rows can be inserted into the data set by another user and are included in later reads within the current transaction.

SERIALIZABLE: Issues a range lock on data set, preventing other users to update or insert data into dataset until the transaction is complete.

Scheduled jobs, Backup/Restore and DTA information.