I explained how SQL Server can use an index to efficiently locate rows that qualify for a predicate. When deciding whether to use an index, SQL Server considers several factors. These factors include checking whether the index covers all of…
Scans and Seeks are the iterators that SQL Server uses to read data from tables and indexes. These iterators are among the most fundamental ones that we support. They appear in nearly every query plan. What is the difference between…
In this post, I’ll give an overview of three interesting properties of iterators that can affect query performance: Memory All iterators require some small fixed amount of memory to store state, perform calculations, and so forth. We do not track…
In my last post, I explained that SQL Server executes a query by compiling it into an iterator tree which we call a query plan. So, if we want to understand iterators and query execution better, we need a way…
What are iterators? SQL Server breaks queries down into a set of fundamental building blocks that we call operators or iterators. Each iterator implements a single basic operation such as scanning data from a table, updating data in a table,…
About two years ago I (read: Craig Freedman) presented a talk to a couple of SQL Server user groups where I explained in general terms how SQL Server executes queries. I explained how to read showplans and how some of…
The foundation of tuning queries is understanding the fundamental basics, and it all started with the blogs of Craig Friedman explaining executions plan. Originally this was a Microsoft Blog (blogs.msdn.microsoft.com) but it was moved several times. I have copied all…
Note that Read Committed Snapshot is not an Isolation Level just an added behavior of using a snapshot of a record in case of an DML statement, here is a query to check it… good luck! Readers don’t block Writers…
Switch to RCSI the focus is often on the server, can tempdb handle the rowversion load. But is the application also suitable for RSCI? Advise: always keep the update in one phase. The code is in the end. So here…
The log file of Ola’s IndexOptimize ( SQL Server Index and Statistics Maintenance ) can get quite big if there are many tables with a lot of statistics. If there is locking and a timeout occurs the jobs continues but…
