SQL Server DBA Interview Questions

1) What purpose does the model database?

Ans. The model database, as its name implies, serves as the model (or template) for all databases created on the same instance. If the model database is modified, all subsequent databases created on that instance will pick up those changes, but earlier created databases will not. Note that TEMPDB is also created from the model every time SQL Server starts up.

2) Why would you use SQL Agent?
Ans. 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.

3) What happens at the checkpoint?
Ans. 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.

SQLServerBootcamp

Learn how to become 

SQL Server DBA

SQL Server Developer (Power BI and SSIS)

Learn also how to use SQL Server on Azure and AWS

Join Now

 

 

4) What is DBCC?
Ans. DBCC statements are Database Console Commands and come in four flavours: Maintenance, Informational, Validation, and Miscellaneous. Maintenance commands are those commands that allow the DBA to perform maintenance activities on the database such as shrinking a file. Informational commands provide feedback regarding the database such as providing information about the procedure cache. Validation commands include commands that validate the database such as the ever-popular CHECKDB. Finally, miscellaneous commands are those that obviously don’t fit in the other three categories. This includes statements like DBCC HELP, which provides the syntax for a given DBCC command.

5) How can you control the amount of free space in your index pages?
Ans. 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 into the index.

6) Why would you call Update Statistics?
Ans. Update Statistics is used to force a recalculation of query optimization statistics for a table or indexed view. Query optimization statistics are automatically recomputed, but in some cases, a query may benefit from updating those statistics more frequently. Beware though that re-computing the query statistics causes queries to be recompiled. This may or may not negate all performance gains you might have achieved by calling update statistics. In fact, it could have a negative impact on performance depending on the characteristics of the system.

7) What authentication modes does SQL Server support?
Ans. SQL Server supports Windows Authentication and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server. It’s important to note that if you use Windows Authentication, you will not be able to log in as sa.

8) Explain about your SQL Server DBA Experience.
Ans. 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 would be of significance to the potential employer. This is the answer that lets the interviewer know how suitable are you for the position to which you are being interviewed.

9) What are the different types of Indexes available in SQL Server?
Ans. The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes that can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.

10) What is the difference between Clustered and Non-Clustered Index?
Ans. 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.

11) What are the High-Availability solutions in SQL Server and differentiate them briefly.
Ans. Failover Clustering, Database Mirroring, Log Shipping, and Replication are the High-Availability features available in SQL Server.

 

SQLServerBootcamp

Learn how to become 

SQL Server DBA

SQL Server Developer (Power BI and SSIS)

Learn also how to use SQL Server on Azure and AWS

Join Now

 

12) How do you troubleshoot errors in a SQL Server Agent Job?
Ans. 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 a particular job that 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 that occurred.

13) How do you troubleshoot errors in a SQL Server Agent Job?
Ans. 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 a particular job that 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 that occurred.

14) What is the default Port No on which SQL Server listens?
Ans. 1433

15) What are the commands used in DCL?
Ans. GRANT, DENY, and REVOKE.

16) What is Fill Factor?
Ans. 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.

17) What is the default fill factor value?
Ans. By default, the fill factor value is set to 0.

18) Where do you find the default Index fill factor and how to change it?
Ans. 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 the desired value there and click OK to save the changes.

 

SQLServerBootcamp

Learn how to become 

SQL Server DBA

SQL Server Developer (Power BI and SSIS)

Learn also how to use SQL Server on Azure and AWS

Join Now

 

19) What is a system database and what is a user database?
Ans. 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 the smooth functioning of the SQL System.
A user database is a database that we create to store data and start working with the data.

20) What are the recovery models for a database?
Ans. There are 3 recovery models available for a database. Full, Bulk-Logged, and Simple are the three recovery models available.

21) What is the importance of a recovery model?
Ans. Primarily, the recovery model is chosen keeping in view the amount of data loss one can afford. 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 behaviour of the database log file changes. I would recommend you read more material on log backups and log file behaviour and so on to understand in depth.

22) If you are given access to a SQL Server, how do you find if the SQL Instance is a named instance or a default instance?
Ans. I would go to the SQL Server Configuration Manager. In the left pane of the tool, I would select SQL Server Services, the right side pane displays all of the SQL Server Services/components that are installed on that machine. If the Service is displayed as (MSSQLSERVER), then it indicates it is a default instance, else there will be the Instance name displayed.

23) What are the different authentication modes in SQL Server and how can you change authentication mode?
Ans. SQL Server has 2 Authentication modes; Windows Authentication and SQL Server and Windows Authentication mode also referred to as Mixed Mode.

 

SQLServerBootcamp

Learn how to become 

SQL Server DBA

SQL Server Developer (Power BI and SSIS)

Learn also how to use SQL Server on Azure and AWS

Join Now

 

24) What are the different types of backups?
Answer: The SQL server offers 4 types of backups to suit the need of the administrator.

Complete backup: The complete back up is just zipping the content of the entire database in terms of the different tables and procedures etc. This backup can server as an independent entity that can be restored in different systems with just the base SQL server installed.

Transaction log backup: This is the mechanism of backing up the transaction logs that have been maintained in the server. This way the details of the database getting updated is obtained. This cannot be a stand-alone backup mechanism. But can save a lot of time if we already have the file system related to the DB backed up on the new deployment server.

Differential backup: This is a subset of the complete backup, where only the modified datasets are backed up. This can save time when we are just trying to maintain a backup server to main server.

File backup: This is the quickest way to take the backup of the entire database. Instead of taking in the data actually stored in DB, the files are backed up and the file system thus obtained when combined with the transaction logs of the original system will render the database that we are trying to back up.

25) What's the difference between a primary key and a unique key?
Answer: 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.

26) What is bit datatype and what's the information that can be stored inside a bit column?
Answer: Bit datatype is used to store boolean information like 1 or 0 (true or false). Until SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.

27) What are defaults? Is there a column to which a default can't be bound?
Answer: A default is a value that will be used by a column if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them. Q.What is a transaction and what are ACID properties? Answer: A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals textbook.

28) What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
Answer: DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead, it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

29) What are constraints? Explain different types of constraints.
Answer: Constraints enable the RDBMS to enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults. Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

30) What are statistics, under what circumstances they go out of date, how do you update them?
Answer: 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 a 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

 

SQLServerBootcamp

Learn how to become 

SQL Server DBA

SQL Server Developer (Power BI and SSIS)

Learn also how to use SQL Server on Azure and AWS

Join Now

 

Leave a Reply

Your email address will not be published. Required fields are marked *