Notes – SQL Server Index Fragmentation, Types and Solutions

This is the first in the series of blog posts that I plan on writing to summarize various topics of interest in SQL Server. The goal of these “Notes” posts is to collect important information from multiple sources such as whitepapers, KB articles, MSDN/TechNet articles, credible blogs etc., and publish them in a single post…


Attention events can cause open transactions and blocking in SQL Server

Wondering what causes Attention events in a SQL server Profiler trace and how these events can result into severe blocking problems? I will attempt to provide some answers in this post. First of all let’s talk about what Attentions events are and what may cause them. According to BOL – “The Attention event class indicates…


SQL Server 2008 and R2 Cluster Best Practices

I have been working with several of my customers recently helping them install and/or review a new SQL Server 2008/R2 cluster environment on Windows Server 2008/R2 for best practices and gotchas. The Cluster Validation wizard in Windows Server 2008/R2 checks for many clustering best practices and reports them as warnings in the cluster validation report….


Queries with UPDLOCK hints on small tables causing blocking

While working with one our ISV customer’s in Microsoft performance testing labs recently, we discovered an unexpected blocking issue with the SELECT queries using the UPDLOCK hints on a small table and in explicitly defined user transactions. I said ‘unexpected blocking issue’ because from our perspective, we were processing two different rows in a table,…