1. What are the different ways and methods of performance tuning and optimization ?
c. Load Balancing
d. Query Refactoring
e. System/Resource Level Optimization (CPU,Memory,IO Configuration)
2. What is Index ?
3. What is Heap ?
4. What is B-Tree/Balance Tree ?
5. What are the kinds of Indexes ? – Clustered and Non-Clustered
6. What are differences between Clustered and Non-Clustered Indexes ?
7. What is execution plan / query execution plan ?
8. What is Estimated and Actual Execution Plan ?
9. How do you plan about Indexes in your database ?
10. What is difference between Table scan and Index Scan ?
11. What is Composite Index ?
12. What is Filtered Index ?
13. What is Fill Factor ?
14. What is Page Split Problem in Sql Server ?
15. What is Fragmentation , Explain ?
16. What is difference between Index Reorganize or Rebuild ?
17. What is Statistics or Index Statistics ?
18. How to check and Update in Statistics ?
19. What is Page and Extent ?
20. What is difference between Index Seek and Index Scan ?
21. How to enable and disable indexes in database ?
22. Can we create clustered index without PK ?
23. Can we create Clustered Index without Primary Key ?
24. Can we create Primary Key without Clustered Index ?
25. How to recompile Stored Procedure at Run Time ?
26. How to avoid dead locks and blocking ?
27. What is DTA (data tuning advisory) ? How to use it for Performance Tuning ?
28. Why we can create only one clustered index per table ?
29. What are the pros and cons of NOLOCK ?
30. What is Query Optimizer ?
31. What is Data Achieving ?
32. What is Data Purging ?
33. What is Data and Database Partition ?
34. What are the types of Partition ? What is Horizontal and Vertical Partition ?
35. What is Table Partitioning ? How to perform partitioning process. Explain .
36. What is Index Partitioning ? How to perform partitioning process. Explain .
37. What is Indexed View or Materialized View ? Benefits of Indexed View ?
38. How to Created Indexed View ?
39. How do you decide about Index Maintenance , which one to rebuild and reorganize ?
40. How to check index fragmentation ?
41. How do we rebuild large indexes ?
42. Where does logs get stored in SQL Server ?
43. What is Full Text Search ? how to configure ?
44. What is Two Phase Commit ?
45. Does Table or Column Alias effect performance ?