Understand local variable estimates with skewed data, density calculations, parameter comparisons, and targeted OPTION RECOMPILE tests.
Optimal Value for Max Worker Threads in SQL Server
The optimal value for Max Worker Threads is the default, 0, which tells SQL Server to calculate the limit from the number of logical processors. This post shows how to read the real limit, how the formula works, and which wait and queue counters prove a thread shortage before you change anything.
SQL SERVER – Performance Comparison IN vs OR
Which query runs faster, one with IN or one with OR? Here is my performance comparison IN vs OR on AdventureWorks, and both turn out equal.
SQL SERVER – How to Fix CONVERT_IMPLICIT Warnings?
CONVERT_IMPLICIT warnings are one of the most common performance issues I find in health checks. Here is how to find them in your queries and how to fix them.
The Tipping Point: Why SQL Server Skips Your Nonclustered Index
Find the tipping point where key lookups cost more than a scan, then compare covering and filtered indexes with measured reads.
DBCC CHECKDB Running Slow? Check the MAXDOP Option
DBCC CHECKDB running slow can trace back to the MAXDOP option. A CHECKDB with no MAXDOP option uses the server’s max degree of parallelism, and one with its own option overrides it. This post times CHECKDB on a 2 GB database with four settings, adds PHYSICAL_ONLY, and lists the other causes to rule out.
Azure – Which One to Get – Standard Disks or Premium Disks
Many customers get a slower SQL Server after moving to Azure. Should you pick Standard or Premium Disks? My short answer is Premium, and here is why.