SQL Server Optimization Q&A

1. What is SQL Server Optimization?

Answer:
SQL Server Optimization ka matlab hota hai database ka performance better banana — jaise queries fast chalna, kam memory use hona, aur server load kam rehna. Ye mainly query tuning, indexes aur configuration settings ko optimize karke hota hai.

2. What are Indexes in SQL Server? How do they improve performance?

Answer:
Indexes basically ek shortcut jaise hote hain — data fast dhoondhne ke liye.

• Clustered Index: Table ke data ko sort aur store karta hai key ke basis pe.
• Non-Clustered Index: Actual data ka pointer rakhta hai.

Indexes query ko fast banate hain kyunki SQL Server ko pura table scan nahi karna padta.

3. What is an Execution Plan?

Answer:
Execution Plan batata hai ki SQL Server ek query ko kaise execute kar raha hai — jaise kis index ka use hua, join kaise lage, aur scan ya seek hua. Ye query optimization ke liye ek powerful tool hai.

4. Table Scan vs Index Seek?

Answer:

• Table Scan: Pura table padhta hai (slow).
• Index Seek: Sirf zaruri rows tak direct pahunchta hai (fast).

Simple words me — table scan = "sab check karo", index seek = "seedha target pe jao".

5. Purpose of Statistics in SQL Server?

Answer:
Statistics SQL Server ko data distribution samjhne me help karti hai. Isse Query Optimizer best execution plan choose karta hai. Agar stats outdated ho jaayein to queries slow ho sakti hain.

6. How to find slow queries?

Answer:

• Query Store ka use karo.
• SQL Profiler / Extended Events se trace lo.
• DMVs jaise sys.dm_exec_query_stats ya sys.dm_exec_requests check karo.

7. Rebuild vs Reorganize Index?

Answer:

• Rebuild: Index ko delete karke dubara banata hai. Fragmentation fix karta hai.
• Reorganize: Leaf pages ko reorder karta hai — halka sa tuning, kam resource use karta hai.

8. Common reasons for slow queries?

Answer:

• Missing/poor indexes
• Outdated statistics
• SELECT * ka overuse
• Too many joins/subqueries
• Cursors instead of set-based logic
• Fragmented indexes

9. What is Parameter Sniffing?

Answer:
SQL Server jab query ka plan pehla parameter dekh ke banata hai, aur wahi har baar use karta hai — ye kabhi-kabhi galat results de sakta hai performance ke hisaab se.

Fixes:

• OPTION (RECOMPILE) lagao
• Local variables use karo
• Index optimize karo

10. Best practices for optimized queries?

Answer:

• Sirf required columns select karo
• Proper indexes banao
• Scalar functions ko avoid karo WHERE me
• Joins > Subqueries
• TOP ya pagination use karo
• DISTINCT tabhi lagao jab zarurat ho

⚙️ Advanced Questions

11. How do you use Query Hints?

Answer:
Query hints SQL Server ko batate hain “query kaise chalani hai”.
Examples:

• OPTION (FORCE ORDER) – join order fix karta hai
• OPTION (MAXDOP n) – threads limit karta hai
• OPTION (RECOMPILE) – har baar new plan banata hai

12. Clustered vs Non-Clustered Index?

Answer:

• Clustered Index: Data rows ko order me store karta hai. (1 per table)
• Non-Clustered Index: Data pointers store karta hai. (Multiple allowed)

13. Role of Query Optimizer?

Answer:
Query Optimizer decide karta hai ki ek query sabse efficient way me kaise chale — ye multiple plans banata hai aur sabse kam cost wala choose karta hai.

14. What are Covering Indexes?

Answer:
Aise indexes jisme query ke sabhi required columns included hote hain — isse SQL Server ko table se extra lookup nahi karna padta.

15. How to handle Locking and Blocking?

Answer:

• NOLOCK ya READ UNCOMMITTED use karo (read-only queries ke liye)
• Long transactions avoid karo
• Proper indexes lagao
• sys.dm_exec_requests se blocking monitor karo

16. Optimizing queries for large data?

Answer:

• Table & index partitioning
• Filtered indexes use karo
• Queries me batching/pagination lagao
• Parallelism (MAXDOP) samajh ke use karo

17. What is Database Engine Tuning Advisor?

Answer:
Ek tool jo workload analyze karke suggest karta hai — kaunsa index banana, kaunsa delete karna, aur query kaise optimize karni hai.

18. WITH RECOMPILE vs OPTION (RECOMPILE)?

Answer:

• WITH RECOMPILE: Stored procedure level pe har execution pe recompile hota hai.
• OPTION (RECOMPILE): Sirf ek specific query ke liye.

19. How does SQL Server handle Parallelism?

Answer:
SQL Server query ko multiple threads me tod ke execute karta hai (parallelism).
Control: MAXDOP parameter se.
Monitor: sys.dm_exec_query_parallel_workers.

20. How to optimize tempdb performance?

Answer:

• tempdb ko fast disk pe rakho
• Auto-growth avoid karne ke liye pre-size karo
• Multiple data files banao
• Contention monitor karo via sys.dm_os_wait_stats

21. What is Plan Caching & how to optimize?

Answer:
SQL Server execution plans ko cache karta hai reuse ke liye.

Tips:

• Parameter sniffing handle karo
• Cache clear test ke time: DBCC FREEPROCCACHE
• Monitor via sys.dm_exec_cached_plans

22. How to monitor & fix Deadlocks?

Answer:

• Profiler me deadlock graph enable karo
• Extended Events use karo live tracking ke liye
• Fix: Transaction scope kam karo, order same rakho, retry logic lagao

23. What are Filtered Indexes?

Answer:
Filtered index sirf ek subset of rows ko index karta hai, condition ke basis pe.
Useful jab sirf active data pe queries zyada chalti ho.

CREATE INDEX idx_active_users ON Users(UserID)
WHERE IsActive = 1;