SQL Server Interview Topic 35: Performance Troubleshooting Checklist
This topic gives a clear process for answering slow query and performance questions.
You should be able to explain a slow query investigation in a calm step-by-step way.
Q210. What is your first step for a slow query?
Quick interview answer:
First understand the expected output and confirm the query is logically correct. Then check row count, filters, joins, selected columns, and whether the query returns more data than needed.
Study in detail: Troubleshooting and Optimization - This topic explains investigation steps.
Q211. When do you check indexes?
Quick interview answer:
Check indexes after understanding the query pattern. Look at columns used in filters, joins, and sorting. Add or change indexes only when they support real queries.
Study in detail: Index Deep Dive - This topic explains index choices.
Q212. Why is pagination important for performance?
Quick interview answer:
Pagination prevents the API and database from returning too many rows at once. It makes list screens faster and reduces memory, network, and UI load.
Study in detail: SQL in Backend APIs - This topic explains API list screens.
Q213. How can blocking affect performance?
Quick interview answer:
Blocking makes one query wait for another transaction to release a lock. Long blocking can make the application feel slow even when the query itself is not badly written.
Study in detail: Isolation, Locking, and Deadlocks - This topic explains locking behavior.
Q214. What is a good final answer for slow query troubleshooting?
Quick interview answer:
I check the requirement, row count, filters, joins, selected columns, indexes, execution plan, blocking, and pagination. Then I test the fix with realistic data before changing production.
Study in detail: Monitoring and Performance and Performance Case Studies - These lessons explain practical performance checks.
Performance answers should sound like investigation, not guessing. Give your checklist in order.