udayarumilli.com

Top 40 SQL Server Performance Tuning Interview Questions

SQL THE ONE

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 →