SQL Server Tuning Blog

Are you experiencing performance bottlenecks? Is this query taking forever to complete? Most DBAs would start their resolution attempts with one of SQL Server’s most powerful features – Indexes. Indexes are such an effective mean that a small change could mean a world of a difference in performance. Most DBAs are also aware that adding indexes will cause some degradation of data modification operations as more indexes need to be updated to reflect the changes. However, few are aware that sometimes adding indexes can be a double-edged sword, with far reaching implications on performance, and not always for the best… This article will help you understand a hidden danger of adding more indexes, that can result in surprising effects on your workload. It tells a story of a production server that was brought to its knees with the addition of a single, innocent looking index.

The SQL Server query optimizer is considered magical by many, even the top professionals. It is an extremely complicated piece of software whose intricate paths and logic are hard to perceive. In this article, the first in a series, We will try to tackle some of the query optimizer’s finer tricks to remove the magical aura and reveal the science underneath.

Writing a query that joins several tables is easy, right? There are just a few major join types and their syntax is pretty straightforward and clear. So how come such a simple join be the source of so many headaches? Why does one query with a simple JOIN take forever and another, similar one a fraction of a second? If you’ve ever wondered what goes on under the covers and how SQL Server implements your joins, this article will help you get started.

If you’ve ever used SQL Trace, STATISTICS IO or any other SQL Server monitoring, you should know what logical reads mean. Or do you? Logical reads server as one of the primary counters for evaluating a Query’s performance. The rule of thumb says “The lower the number of reads, the better the query performs”. I’m sure you’ve heard it before. But… did you notice that in some cases the exact opposite is true? Did you ever see a query improve dramatically in terms of duration and CPU but have it’s logical reads sky-rocket? If you want to understand the TRUE essence of logical reads and be able to tell when it is really a good indicator of performance, I highly recommend you to read this article.