Library
“Far, far away in a galaxy” there were days, when one should make a strong effort to find the information about the topic of interest. Now, the problem is quite the opposite – there is so much information over the Internet, that it is hard to find the one you need, and even more hard to find the one you may trust.
Here I will enlist the most useful and relevant resources about query processing and optimization, from my point of view.
I also marked with the bold font my favorite.
Microsoft Blogs
Below you may see the list of blogs, the are the most relevant to the query processing, and were written by present or ex Microsoft employees. I consider them to be the most trusted sources of information, because of their insider nature. Some of them are not active now, and contains pretty old posts, but still useful and actual for today.
A lot of really cool and performance related blogs, like Paul Randal blog, SQL OS blog, Storage Engine team blog – are not included, just because their focus is a little bit different from query processing and optimization as I define it here.
- Bart Duncan’s SQL Weblog
- Conor Cunningham on SqlSkills
- Conor vs. SQL Blog
- Craig Freedman’s SQL Server Blog
- CSS SQL Server Engineers
- Esoteric (by Arvind Shyamsundar)
- Ian Jose’s WebLog
- Jimmy May’s Blog
- Joe Sack on SQLSkills
- Joe Sack’s SQL Server Blog
- Microsoft SQL Server Support Blog
- Microsoft SQL Server Tips & Tricks
- Microsoft SQLCAT ISV Program Management Team
- Remus Rusanu Blog
- Running SAP on SQL Server Blog
- SQL Programmability & API Development Team Blog
- SQL Server Blog
- SQL Server Customer Advisory Team
- SQL Server Engine Tips
- SQL Server Premier Field Engineer Blog
- SQL Workshops by Ramesh Meyyappan
- Srgolla (by Krishna Golla)
- Tim Chapman Blog
- Tips, Tricks, and Advice from the SQL Server Query Optimization Team
- Tips, Tricks, and Advice from the SQL Server Query Processing Team
Microsoft Whitepapers and Documentation
Below you may see the list of Whitepapers or other documents from Microsoft, somehow related to the query processing. I’ll not provide links to them, rather I’ll provide the name, so you may find it using your favorite search engine with a few extra clicks.
BOL (Books online), also contains a lot of useful information, but not included here.
- Microsoft SQL Server 7.0 Resource Guide (Chapter 14) Query Processor
- Bitmaps in Microsoft SQL Server 2000
- Query Recompilation in SQL Server 2000
- Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
- Improving Performance with SQL Server 2000 Indexed Views
- Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
- Forcing Query Plans
- Improving Performance with SQL Server 2005 Indexed Views
- Multiple Active Result Sets(MARS) in SQL Server 2005
- Processing XML Showplans Using SQLCLR in SQL Server 2005
- SQL Server Optimization
- SQLCAT: SQL Server Best Practices
- SQLCAT: The Impact of Changing Collations and of Changing Data Types from Non-Unicode to Unicode
- SQLCAT: Troubleshooting Performance Problems in SQL Server 2005
- Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
- Top 10 Hidden Gems in SQL Server 2005
- Improving Performance with SQL Server 2008 Indexed Views
- SQLCAT: Troubleshooting Performance Problems in SQL Server 2008
- Statistics Used by the Query Optimizer in Microsoft SQL Server 2008
- Bitmap Showplan Operator
- Interpreting Execution Plans Containing Bitmap Filter
- Optimizing Data WarehouseQuery Performance Through Bitmap Filtering
- SQLCAT: Using Star Join and Few-Outer-Row Optimizations to Improve Data Warehousing Queries
- Understanding and Controlling Parallel Query Processing in SQL Server
- Understanding Hash Joins
- Using Star Join and Few-Outer-Row Optimizations to Improve Data Warehousing Queries
- Plan Caching and Recompilation in SQL Server 2012
- Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator
Academical papers
Below you may see the list of documents that intended not for RDBMS users (like DBA, DBD or any other person who use RDBMS, SQL Server in our case), but for RDBMS developers (people, who develop database systems). Most of the documents is a product of various database conferences, like SIGMOD, VLDB, or special journals.
These documents are particularly interesting as they describe some internals from the insiders, that you would never find in any other place. I have more than 200 of them on my PC, but here I’ll enlist the most relevant and interesting, from my point of view.
- Access Path Selection in a Relational Database Management System (IBM Research)
- The EXODUS optimizer generator (Goetz Graefe, David J. DeWitt)
- The EXODUS Extensible DBMS Project An Overview (Michael J. Carey, David J. DeWitt, Goetz Graefe, … )
- Extensible Query Optimization and Parallel Execution in Volcano (Goetz Graefe)
- The Volcano Optimizer Generator Extensibility and Efficient Search (Goetz Graefe, William J. McKenna)
- Volcano An Extensible and Parallel Query Evaluation System (Goetz Graefe)
- The Cascades Framework for Query Optimization (Goetz Graefe)
- The Microsoft Relational Engine (Goetz Graefe)
- An Efficient Cost-Driven Index Selection Tool for Microsoft SQL Server (Surajit Chaudhuri, Vivek R. Narasayya)
- Hash Joins and Hash Teams in Microsoft SQL Server (Goetz Graefe, Ross Bunker, Shaun Cooper)
- Microsoft index tuning wizard for SQL Server 7.0 (Surajit Chaudhuri, Vivek Narasayya)
- Random Sampling For Histogram Construction How much is Enough (Chaudhuri, S., Motwani R., Narasayya V.)
- The Value of Merge-Join and Hash-Join in SQL Server (Goetz Graefe)
- Automated Selection of Materialized Views and Indexes in SQL Databases (Sanjay Agrawal, Surajit Chaudhuri, Vivek R. Narasayya)
- Counting, Enumerating, and Sampling of Execution Plans (Florian Waas, Cesar Galindo-Legaria)
- Orthogonal optimization of subqueries and aggregation (Cesar Galindo-Legaria, Milind Joshi)
- PIVOT and UNPIVOT optimization and execution strategies in an RDBMS (Conor Cunningham, César A. Galindo-Legaria, Goetz Graefe)
- Query processing for SQL updates (Cesar Galindo-Legaria, Stefano Stefani, Florian Waas)
- Distributed-Heterogeneous Query Processing in Microsoft SQL Server (Conor Cunningham)
- B-tree indexes, interpolation search, and skew (Goetz Grafe)
- Testing Cardinality Estimation Models in SQL Server (Campbel Frazer)
There are a lot of other interesting documents left behind. I let your curiosity to drive you to find them. The best starting point for this search might be the author’s name.
Community Blogs
Below you may find non-Microsoft, but community blogs. A lot of very smart people blogs every day, but, however, I’ll enlist only the most relevant blogs to the query optimization. That means, a lot of my favorite blogs, that I read every day left behind, even if they contains some pieces of related information.
- Aaron Bertrand at SQLPerformance.com
- About Sql Server by Dmitri Korotkevitch
- Adam Machanic at SQLBlog.com
- Alexander Gladchenko blog
- Benjamin Nevarez – personal blog
- Benjamin Nevarez at SQLBlog.com
- Blog – Fabiano Neves Amorim
- Brent Ozar Team blog
- Erin Stellato at SQLPerformance.com
- Erin Stellato at SQLSkills
- Erland Sommarskog’s home page
- Fabiano Amorim at SimpleTalk
- Itzik Ben-Gan blog
- Home Of The Scary DBA by Grant Fritchey
- Kalen Delaney at SQLBlog.com
- Kimberly Tripp at SQL Skills
- Paul Randal at SQLSkills
- Paul White at SimpleTalk.com
- Paul White at SQLPerformance.com
- Paul White at SQLServerCentral.com
- Paul White: Page Free Space at SQLblog.com
- QDPMA – SQL Server Performance Consultant – Joe Chang
- Rob Farley at SQLBlog.com
- SELECT Blog FROM Brad.Schulz CROSS APPLY SQL.Server
- SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = ‘SQL Server’
- SQL Rockstar by Thomas Larock
- Thomas Kejser
Books
A book is a product of a very hard work of several people. I respect people who are persistent enough to write a book. But, unfortunately, not all of the authors are responsible enough to check every single statement of what they write. I saw plenty of examples on forums, where the newbie asks a question, quoting the book, and the book says things, that are not true.
Naturally, the question is, what book should I trust? Here I collected the books, that I’ve read, and that contains minimum to zero mistakes and misconceptions. They might be not directly related to the query optimization, but I consider them to be a “must read” material to have a background for understanding what happens inside SQL Server.
It is also worth saying, that things change from one version of SQL Server to another, and during the time pass, some of the statements related to the specific version is not true for the new one. However a lot other stuff might be still applicable. For example, my favorite book ” SQL Server Gurus Guide – Structure and realization” has lost its actuality as the things about SQL OS changed, but, when I’m asked, how I find the undocumented stuff in SQL server – I refer to this book. It contains a fully functional guide and instructions how to dig internals, and if you will be persistent enough, you’ll succeed.
- Kalen Delaney – Inside Microsoft SQL Server 2000
- SQL Server Gurus Guide – Stored Procedures, XML, HTML (Ken Henderson)
- SQL Server Gurus Guide – Structure and realization (Ken Henderson)
- SQL Server Gurus Guide – Transact-SQL (Ken Henderson)
- Inside Microsoft SQL Server 2005 – Query Tuning and Optimization(Kalen Delany, Craig Freedman)
- Inside Microsoft SQL Server 2005 – T-SQL Querying (Itzik Ben-Gan, Lubor Kollar, Dejan Sarka)
- SQL Server 2005 Practical Trouble Sooting
- Complete Showplan Operators (Fabiano Amorim)
- Inside Microsoft SQL Server 2008 – TSQL Programming (Itzik Ben Gan)
- Inside Microsoft SQL Server 2008 – TSQL Querying (Itzik Ben Gan)
- Inside the SQL Server Query Optimizer (Benjamin Nevarez)
- Microsoft SQL Server 2008 Internals (Kalen Delany)