SQL Server 2005 and 2008 versions may not detect all available processors on a machine with more than 64 logical processors

Scenario: I came across this scenario recently where a SQL Server 2005 instance installed on Windows Server 2008 R2, wasn’t detecting all available logical processors after Hyperthreading was enabled on the server. The machine in question had 4 sockets with each socket representing a NUMA node and each socket had 10 cores on it –…


Considerations with using Mount Points in SQL Server 2008/R2 Failover Clusters

Recently I was testing with using Mount Point in SQL Server 2008 failover cluster installation and came across some interesting issues, which I wanted to share for the benefits of my customers. Installation Failure: Scenario: The first time I tried the installation with mount points, the installation failed with the following error – “Wait on…


How to use SQLDiag, SQLNexus and PAL tools to analyze performance issues in SQL Server

SQLDiag, SQLNexus and PAL (Performance Analysis of Logs) tools can collectively be used to analyze performance problems in SQL Server. SQLDiag is a data collection utility, while SQLNexus and PAL tools can be used to analyze data collected by the SQLDiag utility. Ever used the PSSDiag utility that was (and is still) used by Microsoft…


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,…