This article will help you to learn and prepare SQL Server interview questions and answers. If you are looking for a job change or want to improve your interview skills, then you must go through with this article along with questions & answers given in this article. I have tried to answer each question in a very precise manner which most interviewers want to listen to these days.
You must also prepare the below questions along with improving your technical skills if you are looking for a job change. Having technical knowledge is not enough to get a job these days rather you should show your dedication, positive attitude, flexibility, and read to adopt new changes to your interviewers. Interviewers ask a lot of other non-technical questions to evaluate your qualities like I just mentioned above (dedication, positive attitude, flexibility and read to adopt new changes). Make sure to prepare their answers as well before going into the job market. I am giving a few examples of such questions.
- Tell me something about yourself and your experience in SQL Server?
- Give me an overview of your database environment like the number of instances, databases, biggest database size, etc.?
- Explain your backup and database maintenance strategy for your biggest critical OLTP database?
- How do you handle major incidents?
- Tell me something about the last issue you have faced and how did you fix that?
- Suppose there is a database issue, and you don’t have any solution. You did everything to find the solution to fix the issue, but you did not get anything. What would be your next step to handle this situation?
- Explain any of your two deliveries or solutions for which you have been recognized by your client and leaders?
Let’s discuss some technical questions and their answers in the below sections now.
SQL Server Interview Questions
What is the relation between Page and Extent in SQL Server?
Pages are basic data storage in SQL Server. The disk space allocated to a data file is logically divided into a sequence of pages. The size of a page in SQL Server is fixed to 8KB. We cannot change the size of pages in SQL Server. Extent is a collection of eight continuous pages and is used to manage the pages. The size of an extent is 64KB which is also the size of eight continuous pages.
Suppose your database log file size is 10MB. Can you calculate how many pages are allocated to that log file?
Log files do not contain any pages in SQL Server so we can say the log file does not have any pages.
How does SQL Server know about all updated pages for differential backup?
SQL Server has an internal data structure to track modified extents since the last full backup. This internal data structure is managed by Differential Changed Map (DCM) pages. DCM tracks extents and captures details about modified extents. If the bit value for an extent is 1 it means that extent is modified since the last full backup and if its value is 0 it means that extent is not modified since its full backup. SQL Server reads the DCM pages to get what all extents are modified and backed up only while running the differential backup.
How can we change the logical file name of a SQL Server database?
Logical files name can be changed using ALTER DATABASE statement. We will pass the old name and new name while running this statement. Have a look at the below example where I am changing the logical file name “SQLSHACK” to “SQLSHACK_data1” of database SQLSHACK.
|
1 2 3 4 |
USE [master]; GO ALTER DATABASE [SQLSHACK] MODIFY FILE ( NAME = SQLSHACK, NEWNAME = SQLSHACK_Data1 ); GO |
Logical file names can also be modified using GUI by accessing the database properties page. We just need to launch the database properties page for the identified database then click on the “Files” tab from the left side pane. Modify file names as per your desired naming convention and click at OK button to submit the change to apply to the database.
What would be the impact on SQL Server performance if we add multiple log files to a database?
Adding multiple log files to a database will not help in optimizing the database performance as SQL Server writes log records serially in the log file.
What is Instant File Initialization in SQL Server?
Instant File Initialization allows SQL Server data files to skip their zeroing process and speed up their space allocation process quickly. This is very useful while creating very large databases or adding or extending data files to their bigger sizes.
I need to increase the data file size to 200GB and log file size to 50 GB. Space allocation to the data file was very quick within few seconds but the log file is taking a very long time while allocating the space despite having Instant File Initialization enabled for the instance. Can you tell me the reason behind taking log file increase so long?
Instant File Initialization does not work for log files that are the reason behind taking it so long and the data file has quickly allocated all 200GB. Read more about Instant File Initialization in SQL Server in the attached article.
How can you secure your SQL Server instance?
Security is one of the major concerns these days. We must take the below steps to secure a SQL Server instance.
- Ensure the operating system on which SQL Server is running is hardened and has followed all best practices
- Never miss applying any security patches released for SQL Server or OS
- Fix vulnerabilities on time by applying their fixes or patches
- Install only those features which are required
- Change SQL Server default port no
- Disable sa account and remove built-in administrator account from SQL Server
- Always use windows authentication mode
- Disable SQL browser service
- Use encryption to protect data
- Regular audit of your logins
There are other best practices like assign appropriate permission to everybody and do not give unwanted authorization to any login, remove logins immediately who has left the company or team, always use strong passwords for your logins, etc.
What are the options available for data protection and business continuity in SQL Server?
SQL Server provides various options for business continuity.
- Backup Restore
- Always on Availability Group
- Always on Failover Cluster Instance
- Log shipping
- Replication
What is the negative side of enabling TDE (Transparent Data Encryption) in SQL Server?
TDE encrypts data to secure it from unauthorized access. One side it protects data then another side it has some negative implications on SQL Server instance.
- Enabling TDE for any database will also encrypt the tempdb database
- There is some performance overhead on the server, even non-encrypted databases hosted on the same instance would have some performance overhead because of tempdb encryption
- Data is not fully encrypted using TDE. Data in transit and data in buffer cache are still unencrypted
- Backup and secure certificate\keys regularly
You have upgraded SQL Server databases to their newer version. Your application users have started complaining about slow performance. How do you address this issue?
We need to investigate the below area if users have started complaining about performance issues post upgrading the database to a newer version.
- Change compatibility level of the database to the latest
- If the compatibility level is already set to the latest, then you should review the query plan and if there is any query regression found then we should use a legacy cardinality estimator as a workaround. Parallelly, enable query store to identify query regression and fix them by choosing a good plan
- Parameter sniffing could be one of the reasons behind the slow performance. Recompile query plan to fix the issue
- Find missing indexes and create them
- Run update stats on identified objects
What is Always on in SQL Server?
Always on is a term that is used for business continuity features in SQL Server. It covers both availability groups and failover cluster instances. If you deploy availability group, you can call it always on availability group or only availability group, if you are saying it always on only then it is not the right term to be used for your availability group deployments.
Can we configure the availability group between Windows-based SQL Server and Linux-based SQL Server instances?
Yes, we can configure availability groups between SQL Server instances hosted on Windows and Linux machines. Cluster type will be changed from WSFC to EXTERNAL because windows failover cluster will not be used for such deployments. We must use pacemaker cluster with cluster type setting as EXTERNAL to configure always on availability group between windows and Linux based SQL Server instances.
Explain some details about distributed availability group?
Distributed Availability Group was added in SQL Server 2016. It spans multiple available groups. Distributed availability group is created on top of existing 2 or more separate availability groups. Availability groups participating in distributed availability groups can be hosted on multiple locations, it can be in the cloud or on-prem, in the same domain or different domains, etc. Each availability group has its own WSFC and nodes participating in that availability group will be part of only that Windows Server Failover Cluster group.
Can we configure Availability groups between two SQL Server failover cluster instances?
Yes, the availability group can be configured between SQL Server cluster instances, but automatic failover cannot be configured between replicas running on SQL Server cluster instances.
How can you patch the Always on Availability group with minimum downtime?
We can follow the rolling upgrade approach to patch availability group instances. First, we will patch the DR replica hosted in another data center. Once the DR replica will be patched, next we will change failover mode to manual in case it is automatic, and patch the secondary replica hosted in the primary datacenter. Reboot this server and once the secondary replica will come online, initiate failover of primary replica to newly patched secondary replica to make this replica primary. Now your previous primary replica is secondary, go ahead and patch this replica. Once patching will complete, reboot the server and fail back SQL Server instance to this instance to make it primary as per your initial design.
What are the steps you need to follow to restore an availability database (a database participating in an availability group)?
We cannot restore an availability database without evicting it from AOAG. We need to evict the availability database from the availability group and then restore and then add it back to the availability group.
Users have started complaining that they are not getting the latest data in their reports that is running from the secondary replica. Can you tell me 3 reasons for such latency issues between your primary and secondary replica?
There are multiple reasons which can slow down the data movement between primary and secondary replicas. Some of the reasons are:
- Long-running transactions can prevent users to see updates from secondary replica
- Resource contention in secondary replicas can slow down the data movement
- Poor network throughput can cause the log to build up on the primary replica
How would long running transactions be one reason for not seeing updates from secondary replica as we know all transactions are first committed on secondary replica and then on the primary replica in synchronous commit mode of always on the availability group configuration? Please explain this.
We need to understand how to read operations are being performed on the secondary replica. All read-only operations use snapshot isolation levels on the secondary replica. Read-only transactions do not see any updates from open transactions while using snapshot isolations level. They read the beginning point of active transactions in the log on the secondary replica. Long-running transactions are open and not committed yet so read-only transactions will be blocked to see new updates until this open transaction will be committed.
What are the tools you use to troubleshoot an always on availability group issue?
We have plenty of tools available to address any issue in the availability group. Some of them are:
- SQL Server Error logs
- Cluster log file
- Availability group extended events
- Always on availability group related wait types
- Availability group health detection logs
- DMVs and system catalog views
Suppose you have configured Log Shipping with Replication for your database. What would be the impact on Replication configuration if you failed over log shipping to the secondary server?
Replication will stop working post log shipping failover. Transactions will not replicate to subscribers as replication agents do not connect to secondary databases. Replication will resume in case a failback will occur and all pending transactions will be replicated to respective subscribers.
Do you know what is RESOURCE SEMAPHORE wait type?
RESOURCE SEMAPHORE is an internal algorithm in SQL Server that grants requested memory to SQL Server queries based on their compiled execution plan to run and execute on the instance. If there is memory pressure or the query does not get their requested memory to execute then that query will wait with RESOURCE SEMAPHORE wait type until memory will be granted to execute the query. RESOURCE SEMAPHORE works on a first come first serve basis so all new queries will be in the queue and waiting queries will get their request memory based on their arrival in the queue.
How can you get requested and granted memory details for a specific SQL Server query?
Getting information like requested memory and granted memory is very crucial when we are dealing with performance issues. There is DMV called sys.dm_exec_query_memory_grants in SQL Server which will display information about these memory details.
Conclusion
Stay tuned for my next article which will be the continuation of this article based on SQL Server interview questions and answers. Please share this article and let us know your feedback in the comment section.
He loves to talk and write about database technologies. He has lead and delivered many projects from designing to deployments on Migrations to the cloud, heterogeneous migrations, Database consolidations, upgrades, heterogeneous replication, HA / DR solutions, automation, and major performance tuning projects.
You can also find him on LinkedIn
View all posts by Manvendra Singh
- Difference between SQL SELECT UNIQUE and SELECT DISTINCT - January 16, 2024
- How to do a group by clause - April 12, 2023
- PostgreSQL vs MySQL: Understanding their differences - March 13, 2023