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 a 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 throughput.
[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 [Part 1] of this series, we explored the locking and blocking of SQL Server — what they are, why they occur, and their detection and prevention mechanism. In Part 2, we will focus on how DBAs can detect, analyze, and resolve deadlock scenarios in real-world workloads. You will learn how to leverage SQL Server’s built-in tools, interpret key system views, capture deadlock graphs, and apply strategies to minimize performance impact. Along the way, we will also discuss best practices and preventive measures to help ensure smooth concurrency handling in your databases.
[More]
In the world of SQL Server, ensuring data consistency, and concurrency is critical — but it often comes with challenges like locking, blocking and deadlocking. These negatively impact on performance. As databases grow in size and usage, understanding how SQL Server handles concurrent data access becomes essential for any DBA.
In this first part of the series, we will dive deep into the core concepts of locks and blocks, explore how they work, and examine why they occur. Through real-world examples and practical scripts, this article aims to equip you with the knowledge to identify, analyze, and handle common blocking scenarios. Mastering these fundamentals will prepare you for the more complex topic of deadlocks, which we will cover in Part 2.
[More]
In the ever-evolving world of database administration, visibility is power. Understanding what is happening inside SQL Server is crucial for diagnosing problems, optimizing performance, and ensuring stability. That is where Extended Events come in. Extended Events is a lightweight and highly customizable replacement of SQL Trace and Profiler which offer deep insights into SQL Server’s internal behavior without imposing significant overhead. These make Extended Events a perfect choice for troubleshooting deadlocks, monitoring long-running queries, analyzing I/O bottlenecks, trace, filter, and respond to specific database events with precision.
In this guide, we will walk through how to set up Extended Events in SQL Server Management Studio (SSMS), explain the key configuration options, and explore real-world use cases that make Extended Events an essential tool in every DBA’s toolbox.
[More]
That day, one of my team members asked me how he could generate a table creation script and a data insert script of that table. I replied that "Table creation script is pretty straightforward. You can tweak creation script option to include the insert script as well." Here goes the steps.
[More]
As a human being, your SQL Server tries to express its pain point with you — but not in words. It whispers through wait stats, sending subtle signals when something is off. But, you have to learn how to decode these signals.
In this article, we will decode the most common SQL Server wait types—like CXPACKET, PAGEIOLATCH, and SOS_SCHEDULER_YIELD—and understand what they are quietly telling you about your server’s workload, health, and bottlenecks. If you have ever wondered why your queries slow down despite healthy hardware, the answer might just be in these whispers.
[More]
As a DBA, you are often alert to the usual suspects—blocking, deadlocks, or long-running queries. But sometimes, performance issues creep in silently, and one of the quietest yet most impactful culprits is latch contention. Unlike locks, latches are lightweight synchronization primitives that protect internal memory structures. When these get overwhelmed, even the most well-tuned queries can slow to a crawl.
In this guide, you will explore what latch contention is, how it differs from locks, when it occurs, how to detect it, and most importantly—how to tune your system to minimize its impact. Whether you are running a high-throughput OLTP workload or managing a reporting environment, understanding latches could be the missing piece in your performance puzzle.
[More]
Recently, Mark Varnas posted a client's experience in his LinkedIn profile. The client has 16 core virtual processors however, SQL Server was using only 4 cores and performance was terrible. My favorite SQL Server expert Brent Ozar also commented and shared his relevant blog's link in the post. I was curious how Mark Varnas identified and resolved this. Robert Concepcion's comments and Brent's blog showed me the path. Let's go through the problem!
[More]
In the world of SQL Server performance tuning, intuition is helpful—but data is essential. Without a solid performance baseline, DBAs are left guessing whether a system slowdown is an anomaly or just another busy working day. Creating a baseline gives you a point of comparison to understand what "normal" looks like for your SQL Server environment. This article will guide you through what to capture, how to capture it, and how to turn that information into actionable insight—so the next time someone says, "the database is slow", you will have data to prove or disprove it.
[More]