Top 40 SQL Server Performance Tuning Interview Questions

Top 40 SQL Server Performance Tuning Interview Questions

SQL THE ONE

Top 40 SQL Server Performance Tuning Interview Questions

There are total 254 questions are answered under SQL Server Performance Tuning category in the book “SQL THE ONE”. In this chapter we can see questions and answers on Indexes, Statistics, Performance Monitoring, Query Tuning, Scenarios, Partitions, In-Memory OLTP and TEMPDB. Below are the Top 40 SQL Server Performance Tuning Interview Questions:

1. What is the difference between composite index and covering index?

2. Do we need to create a covering index for all queries? If not on which basis we have to create covering index?

3. Any idea about filtered index? Why indexed views when we can achieve the same with filtered index?

4. Is there any difference between disabling CLUSTERED and NON-CLUSTERED Index?

5. There is a non-clustered index IX_1 available on table T. I have created a duplicate index IX_2. Now can you tell me which is the index is chosen by query optimizer and why?

6. Let’s say I have a table like: Tab (Col1, Col2, Col3, Col4, Col5, Col6, Col7). I created a clustered index and a non-clustered index like Clustered On (Col1, Col2) and Non-Clustered index on (Col1, Col2). Can you explain the B-Tree structure?

Continue reading

Posted in Interview Q&A, Performance Tuning, SQL Development, SQL Server DBA | Tagged , , , , , , , , , , , , , , , , , , , , , , , , | 5 Comments

Top 10 SQL Server Concurrency and Locking Interview Questions

Top 10 SQL Server Concurrency and Locking Interview Questions

SQL THE ONE

Top 10 SQL Server Concurrency and Locking Interview Questions

There are total 51 questions are answered under SQL Server Concurrency and Locking category in the book “SQL THE ONE”. In this chapter we can see questions and answers on Deadlocks, Transactions, Locking and Isolation Levels. Below are the Top 10 SQL Server Concurrency and Locking Interview Questions:

1. Can you be able to explain how deadlock detection algorithm works in SQL Server?

2. Let’s say we have enabled a trace flag to log deadlock information in SQL Server error log. What kind of information we can get from the log?

3. I have been using SQL Server backup statement inside an explicit transaction. If I rollback the transaction what will happen to my backup?

4. What are the different types of lock modes in SQL Server?

5. My requirement is to disable lock escalation only for a specific table. Is it possible?

Continue reading

Posted in Interview Q&A, SQL Development, SQL Server DBA | Tagged , , , , , , , , , , , , , , , | Leave a comment

Top 10 SQL Server Always On Interview Questions

Top 10 SQL Server Always On Interview Questions

SQL THE ONE

Top 10 SQL Server Always On Interview Questions

There are total 93 questions are answered under SQL Server Always On category in the book “SQL THE ONE”. In this chapter we can see questions and answers on SQL Server Always On and scenarios. Below are the Top 10 SQL Server Always On Interview Questions:

1. What are the ALWAYSON improvements added in SQL Server 2014 and 2016?

2. How many read-write and read only databases replica can be configure in SQL Server 2012 and 2014?

3. What is the Difference between Asynchronous-commit mode and Synchronous commit mode?

4. How much network bandwidth is required for a successful Always On AG setup?

5. What’s the difference between Always On AGs in SQL 2012 and SQL 2014?

Continue reading

Posted in High Availability, Interview Q&A, SQL Server DBA | Tagged , , , , , , , , , , , , , , , | 3 Comments