Some SQL Server Interview Question .

SQL Server Interview Qustions [1-5]:

Interview question 1:

List down 3 SQL Server DMVs related to Troubleshooting Memory Issues

1. sys.dm_os_process_memory
2. sys.dm_os_memory_clerks
3. sys.dm_os_ring_buffers

Interview question 2:
Important DMVs for troubleshooting CPU pressure
1. sys.dm_os_schedulers
2. sys.dm_os_performance_counters
3. sys.dm_os_wait_stats
4. sys.dm_os_ring_buffers

Interview question 3:
How to improve efficiency of plan cache?

Enable "optimize for ad hoc workloads", a server level setting.
This will prevent huge number of one time ad-hoc query plans occupy plan cache.
Only plan stub will be created for first time ad-hoc queries. When the ad-hoc query is executed for the second time, actual execution plan will be created and stored in plan cache.

Interview question 4:
What is the use of Tempdb?

1. Temp Tables and Table Variables
2. Online index operations
3. Triggers
4. Version Store (ex: snapshot isolation)
5. SORT/HASH JOIN/UNION/DISTINCT/ORDER BY
6. Work tables for some DBCC commands
etc.

Interview question 5:
What is the difference between RTO and RPO?

Recovery Time Objective (RTO) is the duration of time and a service level within which a business process must be restored after a disaster/failure

Recovery Point Objective (RPO) is the maximum acceptable amount of data loss (measured in time) after disaster/failure