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]
Recently, there was performance issue in one of our systems and the client asked us to find the root cause. In brief, there are two different processes in that system, some processes are generating some jobs and adding into a job table with unprocessed status, and other processes are competing to pick and update the unprocessed rows. Long time, the system was working fine without raising any concern. I was curious about what went wrong and how to fix it. So, I spent couple of hours to investigate it. In this article, I am going to explain what actually happened and how I resolved it. Let's start.
[More]
Below query will reset the Index Usage, Wait Types, and Plan cache
[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]
When it comes to SQL Server performance tuning, few settings spark as much debate as MAXDOP—Maximum Degree of Parallelism. This small but powerful configuration controls how many processors SQL Server can use to execute a single query in parallel. While parallelism can speed up queries, it can also lead to unexpected issues like CPU pressure, query blocking, or inconsistent performance across workloads.
In this article, we will explore what MAXDOP really does, why it matters, and how to configure it wisely to strike the right balance between speed and stability in your SQL Server environment.
[More]
Let's have a little fun today. SQL Server stores data in MDF file which is a binary file. We will open a MDF file and search for any content. Let's start.
[More]
Recently, I was surfing web for some SQL Server resources and explored some gems for identified missing indexes, unused indexes and heaps. Though, sp_BlitzIndex provides comprehensive information about index optimization. However, these popular scripts can be alternatives where you cannot deploy First Responder Kits. Moreover, you can easily customize them to fit your requirements. Let's dive in!
[More]
Indexes play a crucial role in optimizing database performance by allowing faster data retrieval. SQL Server provides various types of indexes, each designed to improve query performance based on different scenarios. In this article, we will explore the key index types, their characteristics, and how they help enhance query execution.
[More]
In a casual conversation with team, one of my junior team members asked, "If I execute the same query changing the parameter, will the execution plan change?" I thought for some moments and explained him.
[More]
Hi everyone, I will share a recent experience. How my team resolved sudden CPU spike!
[More]