1. What
are the different ways and methods of performance tuning and optimization ?
a.
Indexing
b.
Achieving
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 ?