Monitoring real-time activity inside SQL Server is one of the most essential tasks for a DBA. Whether users are complaining about slow performance, reports are taking longer than usual, or your CPU suddenly spikes to 90%, the first question you ask yourself is — "What is running right now?". SQL Server offers several built-in tools such as Activity Monitor, sp_who2, and Dynamic Management Views (DMVs). During such critical moment, often these tools fall short to provide quick, accurate, and detailed insights.
To fill this gap, sp_WhoIsActive is emerged, which is opensource, lightweight, feature-rich, and incredibly efficient at showing what SQL Server is doing at any given moment. Thus, it becomes one of the most powerful diagnostic tools in a DBA’s toolkit.
In this series, I will cover installation, important output columns, diagnosing blocking, deadlocks, wait statistics, memory grants & TempDB usage, logging activity and advanced parameters. 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]
Below query will reset the Index Usage, Wait Types, and Plan cache
[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]
Last page insert contention is common in a busy SQL Server environment where multiple users are inserting data simultaneously in a table and that table has clustered index on an identity column. When multiple sessions try to add data in the same data page file, the data page becomes "hotspot". As a result, processes queue up to acquire access to the last page, which leads to latch contention, high wait times, and overall reduced performance. Understanding why last page insert contention arises and its mitigation techniques are crucial for handling workloads with rapid inserts.
[More]
Did you ever face any situation where your SQL Server completely stuck in the middle? The CPU rocketed to the sky and SSMS not responding. You cannot execute any query. What did you do to overcome this critical moment? Did you restart the SQL Server? You are not alone. Many DBAs unaware that there is a lifeline in this scenario and they could still access to their SQL Servers. In this article, we will walk through how to enable, configure and access of this lifeline. Let's start.
[More]
In SQL Server, choosing the right transaction isolation level is critical for balancing consistency, concurrency, and performance. While higher isolation levels reduce concurrency issues like dirty reads and phantom rows, they also increase locking and blocking. On the other hand, lower isolation levels improve performance but may allow data anomalies. As a DBA or developer, knowing when to use each isolation type and understanding it. This will help you design stable, efficient systems that match your workload needs.
[More]