Case Study¶

Production database performance drop¶

Production database experienced severe performance degradation, impacting our customer-facing application…

Fix:

Notify the stakeholders (anyone who is involved in the project or relevant people) and set up a bridge call to keep communication open
Access the databases and use a scoped Extended Events session to identify long-running queries and resource-intensive processes — Microsoft has deprecated SQL Server Profiler and warns it can be resource-intensive on production servers
After being identified, it turns out that the query was causing a deadlock due to a missing index. → Fix by adding the appropriate index
Review the query execution plan and restructure the SQL queries to optimize performance further. Schedule a maintenance window to thoroughly analyze and optimize the database without impacting users
Document the issue, solution steps, and the lessons learned to improve our incident response process for future scenarios. (E.g., the importance of having a systematic approach to troubleshooting and the need for proactive performance monitoring)

Clearly understand the priorities and deadlines for each project
Collaborate with stakeholders to identify critical tasks and use PM tools like Linear, Jira
Prioritize based on their impact on business, potential risks, and dependencies. For example, task involving security patches would take precedence over routine maintenance. I also allocate dedicated time slots for each project to ensure steady progress without context switching.
Regular communication: keep stakeholders informed of the progress and any potential delays. I also prepare for unforeseen issues by building buffer time into my schedule. If a high-priority issue arises, such as a database outage, I can quickly pivot to address it while keeping other projects on track.

How to improve?

Analyze the query execution plan to spot any bottlenecks causing delays
Look for things like full table scans, missing indexes, inefficient joins
A full table scan isn't automatically a bottleneck — check table size, predicate selectivity, and the actual execution plan first. Adding an index to a small table or a low-selectivity column can make writes slower and cost storage without improving the plan.
If the scan is confirmed as the bottleneck and the query filters on a selective column, adding an index on that column reduces data retrieval time.
Also consider rewriting the query to simplify or break it down using subqueries or a CTE (Common Table Expression) — a CTE is a named query expression, not a temporary table; its results aren't materialized by definition.

Identify the root cause by reviewing the database logs and deadlock graphs (It will provide detailed information about the involved transactions and the resources they are fighting for.)

Once identified, here are some strategies you can try to resolve and prevent deadlocks

Ensure that all transaction access resources are in a consistent order, which reduces the chance of circular wait conditions. Additionally, keeping transactions short and reducing the amount of time locks are held can minimize the likelihood of deadlocks
Use appropriate isolation level for transactions; for example, using READ COMMITTED instead of SERIALIZABLE when full isolation isn't necessary can reduce the lock contention.
If deadlocks are frequent → Implementing a deadlock retry mechanism in the application logic. (This would catch the deadlock exception and automatically retry the transaction after a short delay).