Normally, the query Optimiser won't consider 'bushy' plans, where both operands to a join operator could be intermediate results from other joins. This means that it won't produce good query plans for some types of query. Hints, by themselves won't do it. More powerful magic is required.… Read more
SQL Server's Query Optimiser relies on statistics for providing a decent query plan. If the statistics are wrong, outdated or missing then you have little realistic hope of having queries that perform well. It is therefore important to appreciate how SQL Server maintains distribution statistics.… Read more
The Query Optimiser needs a good estimate of the number of rows likely to be returned by each physical operator in order to select the best query plan from the most likely alternatives. Sometimes these estimates can go so wildly wrong as to result in a very slow query. Joe Sack shows how it can happen with SQL Queries on a data warehouse with a star schema.… Read more
To understand how to write SQL code for SQL Server that performs well, it is important to appreciate how the query optimizer works. Ben Nevarez explains the essentials, in a broad sweep through a complex subject, in an article taken from his new book 'Inside the SQL Server Query Optimizer'.… Read more
Fabiano launches into a sound technical explanation of the way that the query optimiser works in SQL Server with a mention of Brazilian Soccer stars and young ladies on Copacabana beach. You'll never quite think of statistics, execution plans, and the query optimiser the same way again after reading this, but we think you'll understand them better.… Read more
In his continuing quest to bring a deeper understanding of Query Optimizer to the world at large, Fabiano takes a moment to point out a potential pitfall you may encounter. A light read, but one worth perusing.… Read more
Having adroitly introduced us, in his previous article, to the Date Correlation ability of the Query Optimizer, Fabiano discusses the inner workings of this little-known feature in order to explain exactly how Date Correlation works.… Read more