Sometimes SQL Server feels slow even when CPU and memory look perfectly fine. In many such cases, the hidden culprit is TempDB contention. Since TempDB is heavily used for internal operations, any bottleneck there can impact overall performance. The real challenge is identifying this contention quickly. In this part of the article series, we will use sp_WhoIsActive to detect TempDB related waits. Let's start.
[More]
In SQL Server, performance issues are usually the result of waiting. It is not due to CPU or memory shortages. Every query, at some point, waits for resources like CPU, disk I/O, locks, or memory. The real challenge for a DBA is identifying which wait actually matters. While SQL Server provides DMVs like sys.dm_os_wait_stats, they show aggregated data. What we often need instead is a real-time view of waits at the session level. In this part, we will explore how to use sp_WhoIsActive to identify top waits those really matters. Let's start.
[More]
In my team, there are three types of people.
One group is always excited about new things—latest devices, new OS versions, new tools, or AI models. The moment a new SQL Server version is released, their first question is, "When are we upgrading?"
Another group prefers stability over everything. Their philosophy is simple: "If something works, do not touch it until it breaks."
And then third group is quiet observers, who usually support others.
So whenever the topic of SQL Server migration or version update comes up, it turns into a familiar dilemma—should we move forward or stay with older version?
Over time, I have realized that this is not just a team discussion. It is a common challenge for every DBA, and the answer requires more than just preference or instinct.
[More]
Blocking and locking issues are among the most common—and frustrating—challenges a DBA faces in SQL Server. A single blocked session can quickly escalate into widespread performance degradation, leaving users complaining while the root cause remains hidden. In such situations, having the right tool is critical. In this article, we will explore how sp_WhoIsActive helps you quickly identify blocking chains, understand locking behavior, and take effective action to resolve the issue with confidence.
[More]
Long-running queries are one of the most common causes of SQL Server performance issues. When a query runs for an unusually long time, it can consume excessive CPU, memory, or IO resources and may also block other sessions.
In such situations, quickly identifying the problematic query becomes critical for DBAs. In this series of article on sp_WhoIsActive, we will explore how sp_WhoIsActive can help you to detect long-running queries and understand what they are doing inside SQL Server. Let's start.
[More]
Memory grant is like a container for a query where it flourishes, sorts, joins and stores temporary result sets. Too small grant makes a query slow whereas too large suffers other queries. Understanding how SQL Server calculates memory grants, how to monitor them, and fix common issues are essential knowledge for every DBA. This guide breaks down the key concepts, shows how to identify problematic queries, and offers practical steps to troubleshoot and optimize memory grants in real workloads. Let's start.
[More]
SOS_SCHEDULER_YIELD is a common wait type which often puzzles DBAs. It does not necessarily mean something is "problematic", rather it tells that SQL Server workers are voluntarily yielding the CPU to let other threads run. However, when this wait becomes dominant, it is usually a signal that the CPU is under pressure or certain queries are consuming more CPU cycles than they should.
In this guide, we will walk through what SOS_SCHEDULER_YIELD means, why it occurs, how to identify the queries contributing to it, and practical steps to mitigate CPU contention in your SQL Server environment. Let's start.
[More]
WRITELOG is one of the common wait types found in the SQL Server. It is directly related to the speed and efficiency of writing transaction log stored in the file system. This ensures durability and data integrity. When WRITELOG waits are high, it usually indicates that SQL Server is spending too much time waiting for in-memory transaction log cache to be flushed to the transaction log file. This can slow down transactions and ripple across the entire workload. In this guide, we will explore what causes WRITELOG waits, how to identify the root bottlenecks, and practical ways to reduce their impact through configuration, hardware optimization, and workload tuning.
[More]
Execution Plan is like a blueprint. SQL Server uses it to execute a DML query. As a DBA, understanding execution plans is like reading the X-ray of query performance. It exposes where SQL Server spent its time and resources, which indexes used or ignored, and area of performance bottlenecks.
In this article, you will go through how to analyze SQL Server execution plans, interpret key operators, and identify optimization opportunities.
[More]
Did you ever face any scenario where you found no issues in CPU, memory, Disk IO or network, still some users are facing slow response and SQL Server has high ASYNC_NETWORK_IO wait type? At first glance, it may seem like SQL Server is the problem—but in reality, this wait type usually points outside the database engine. It occurs when SQL Server has results ready to send, but the client application (or the network in between) cannot consume the data fast enough. This may result-in query slowness, blocked sessions, and frustrated users.
In this article you will walk through what ASYNC_NETWORK_IO is when it occurs, simulate it and share some techniques to reduce them. Let's start.
[More]