The Waiting Game: Understanding SQL Server Wait Statistics Part-1

When SQL Server slows down, it is not always easy to figure out why. That is where Wait Statistics come into play — they act like a performance stethoscope, helping DBAs listen to what SQL Server is waiting on. Every query, every process, leaves behind clues in the form of waits. By understanding these waits, you can diagnose bottlenecks, uncover hidden issues, and fine-tune your server for peak performance.

In this series, I will tell you some relevant concepts, show you how to read them, and help you decide what actions to take — so you are not just guessing anymore. [More]

Behind the Scenes: How SQL Server Rebuilds an Online Index

When you rebuild an index in SQL Server, it might seem like a simple one-click operation—but behind the scenes, a lot is happening to ensure data integrity and performance. From acquiring locks to versioning scans and index swapping, SQL Server carefully orchestrates the entire process without disrupting your workload. Here goes the basic operations that SQL Server performs to rebuilds an index. [More]

Query Performance Tuning: Removing Key Lookup from Execution Plan

When it comes to query performance tuning in SQL Server, key lookups are often the silent culprits that slow things down. This seemingly small operation can become expensive when repeated over thousands—or millions—of rows. In this article, we will break down what a key lookup is, why it happens, and how you can eliminate it with smarter indexing strategies. By addressing key lookups, you can significantly reduce I/O and boost query speed. So grab your execution plan and let’s uncover the hidden costs! [More]

Deep Drive into Columnstore Index

In today's data-driven world, performance is everything—especially when working with large volumes of data. Traditional row-based indexes can struggle to keep up with modern analytics workloads. That’s where Columnstore Indexes come in. Designed for high-performance querying on massive datasets, Columnstore Indexes reshape the way SQL Server handles storage and retrieval, particularly for OLAP-style reporting and data warehousing.

In my last Columnstore Index article, I discussed about some concepts of Columnstore Index. In this write-up, we will go deeper. [More]

Scripts for Identifying Missing Indexes, Unused Indexes and Heaps

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]