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?

7. What are the reason that leads to Internal, logical and extent fragmentation?

8. While creating an index if I mention “PAD INDEX” what does it mean?

9. I have an indexed view, now base table data has been modified, does the modified data reflected in view automatically?

10. Why clustered index shouldn’t be created on a column when it’s having duplicates?

11. In “SQL Server” do we have Statistics on Table and Statistics on Index? Or both are same?

12. Can you be able to take an example and explain how histogram works in cardinality estimates?

13. In your system AUTO UPDATE STATISTICS is on and can you tell me on which basis “UPDATE STATISTICS” triggered?

14. You are assigned to handle a very large database (6.5 TB). What is your approach in updating statistics?

15. What are all the parameters we need to check when a stored procedure is running slow?

16. On which basis you would determine the Max Server Memory Setting for SQL Server?

17. You have given a new environment with SQL Server installed with default values. Your manager asked you to check the configuration settings and do require changes as per your business requirement. What are the top 5 settings that you would first look into?

18. Window’s task manager is not showing the correct memory usage by SQL Server. How to identify the exact memory usage from SQL Server?

19. How to read the graphical execution plan? What are the key elements that can help us in finding the bottlenecks?

20. If I apply both FORCESCAN and FORCESEEK on the same query what will happen?

21. What is the alternative to “Like” operator?

22. What is the alternative way for NOT IN/NOT EXISTS/EXCEPT?

23. Can you explain few reasons and convince why SQL Server scalar functions are bad?

24. We have a table called Tab1 (Col1, col2, col3), Now Col1 is the primary key and there is a non-clustered index defined on Col3. Now tell me what are the steps taken when you update a value on Col3?

25. Have you ever seen “Sort Warning” on execution plan?

26. What are the various ways to handle the bad parameter sniffing?

27. Can you explain the top 3 wait stats which should be considered while tuning the system?

28. While designing a new execution plan what are all the elements considered by database engine? Can you quickly list out as much as components you remember?

29. Does query plans generated only for stored procedures? If No, what are the various database objects for which Query Plans get generated?

30. As we know that dynamic SQL should always be executed using “sp_executesql” so that it can reuse the execution plan from cache. I believe you aware of this. Here is my question, how SQL Server can identify plan from cache in-case of dynamic SQL? We know that stored procedure can be identified by its name in the cache but how this dynamic SQL submitted through SP_EXECUTESQL is identified from cache?

31. Here is a scenario: When we are monitoring memory usage by one of the SQL Server instance, surprisingly SQL Server is using more memory than Max Server Memory configuration. Any idea why it’s happing?

32. We recently had to cancel a long-running update. After the operation rolled back, the next daily transaction log backup was huge. My question is the operation got roll backed and nothing had changed in the database. But why we were getting huge log backups?

33. You are writing a query and as per the requirement you need to use “IN” clause. But your manager suggested using INNER JOIN instead of IN clause. What is your choice? Why?

34. See we have a simple table with 1000 rows and a simple select statement is taking longer time. Just say “SELECT COL1, COL2, COL3 from Tab” is taking more than a minute. What might be the reason and how do you troubleshoot it?

35. What are the most common problems/limitations in SWITCHING-IN and SWITCHING-OUT a partition?

36. What is TEMPDB PAGELATCH Contention and how to resolve it?

37. What are the performance improvements in SQL Server 2014 / 2016?

38. Does all dirty pages written to disk when an automatic checkpoint occurs?

39. What is the recommended setting for MAXDOP?

40. We need to perform a DELETE operation on various tables on a VLDB. What are your suggestions to optimize the delete process on huge tables?

Summary:

These Top 40 SQL Server Performance Tuning Interview Questions are the sample questions randomly collected from the book “SQL THE ONE”.

Please share your feedback and comments on below comments section or reach me on sqltheone@gmail.com. We wish you all the best for the next interview.

Here you can find more Details about the book “SQL THE ONE”.