MAXDOP: What You Need to Know

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]

Search within MDF File

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]

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]

SQL Server Indexes Explained: Clustered, Nonclustered, and Beyond

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]

Introduction to Query Store

Do you know SQL Server keeps an inventory of your SQL queries, where you could track performance, pinpoint bottlenecks, and instantly access the history of every executed query. Even you could force a regressed query to use its previous plan! That is the power of SQL Query Store. The built-in performance tuning tool that provides deep insights into query performance over time. In this article, you will see how Query Store can be used to troubleshoot and optimize your query performance. [More]

Dropping a Principal Who Owns a schema

That day one of my junior colleague rushed to me and sought my assistance. He failed to drop an user. It showed - "The database principal owns a schema in the database, and cannot be dropped." I took him to our office kitchen, had tea, and explained the issue. [More]