Click here to monitor SSC

Author Profile

Fabiano Amorim

Fabiano is fascinated by the SQL Server Query Processor and the way it works to optimize queries, procedures and functions. He graduated as a Technical Processor from Col├ęgio Bezerra de Menezes, SP- Brazil, and has worked for several years with SQL Server, focusing in creating Data Warehouses and optimizing T-SQL codes for many companies in Brazil and Argentina. Fabiano is a SQL Server MVP, MCP for SQL Server 2000, MCTS and MCITP Data Base Developer for SQL Server 2005 and 2008. He also is actively involved in SQL Server community though forums such as MSDN and TechNet Brazil, writes articles for Simple-Talk and SQL Server Magazine Brazil, and he also presents online Webcasts and In-Person events for Microsoft Brazil. His blog is on http://blogfabiano.com

Fixing Cache Bloat Problems With Guide Plans and Forced Parameterization

Imagine it. You've been asked to fix a dire performance problem with a SQL Server database. You find a severe case of 'Cache Bloat' due to ad-hoc queries, but you can't fix the code itself. What should you do? Specify forced parameterization? Perhaps a better idea would be to use guide plans. Read more...

Join Reordering and Bushy Plans

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...

Using Optimizer_WhatIF and StatsStream to Simulate a Production Environment

SQL Server's Query optimiser judges the best query plan from the data in the relevant tables and the server's hardware. How, then, can you investigate the query plans being generated for slow-running queries on a customer's production server when you can neither access the server, nor recreate the database from a backup? Read more...

Hypothetical Indexes on SQL Server

Sometimes, you have to test out alternative indexing strategies, but the task of creating the candidate indexes would just take too long. Is there another way? Well, yes, why not use the same method the DTA (Database Tuning Advisor) uses, and take the tedium out of the job. Read more...

SQL Server Prefetch and Query Performance

Prefetching can make a surprising difference to SQL Server query execution times where there is a high incidence of waiting for disk i/o operations, but the benefits come at a cost. Mostly, the Query Optimizer gets it right, but occasionally there are queries that would benefit from tuning. Read more...

Window Functions in SQL Server: Part 3: Questions of Performance

A SQL expression may look elegant on the page but it is only valuable if its performance is good. Do window functions run quicker than their conventional equivalent code? Fabiano gives a definitive answer. Read more...

Window Functions in SQL Server: Part 2-The Frame

Fabiano continues his introduction to SQL Server's implementation of the window functions by giving a history of what got implemented and when, and explaining the concept of the 'Frame' in a window function, with plenty of examples. Read more...

Window Functions in SQL Server

When SQL Server introduced Window Functions in SQL Server 2005, it was done in a rather tentative way, with only a handful of functions being introduced. This was frustrating, as they remove the last excuse for cursor-based operations by providing aggregations over a partition of the result set, and imposing an ordered sequence over a partition. Now, with SQL Server 2012, we are soon to enjoy a full range of Window Functions. They are going to make for some much simpler SQL queries. Read more...

Complete Showplan Operators

Fabiano Amorim has taken the time to reallv drill into the behavior of a small set of execution plan operators in an effort to explain the optimizer's behavior. He's explored why things happen, how you can change them, positively or negatively, and he's done it all in an approachable style. Read more...

Statistics on Ascending Columns

It comes as rather a shock to find out that one of the commonest circumstances in an OLTP database, an ascending primary key with most querying on the latest records, can throw the judgement of the Query Optimiser to the extent that perfomance nose-dives. Fabiano once again puts on snorkel and goggles to explore the murky depths of execution plans to find out why. Read more...

Questions about SQL Server Distribution Statistics

Fabiano was asked a couple of questions about SQL Server Distribution Statistics. Having given an answer based on his current knowledge, he then decided to find out for himself whether what he'd said was right, and started an epic journey of exploration into Distribution Statistics and the way that the Query Optimiser uses them. Read more...

ShowPlan Operator of the Week - Split, Sort, Collapse

'Split, Sort & Collapse' is, happily, not a description of the intrepid Fabiano after his epic series of articles about the ShowPlan operators. With renewed stamina, Fabiano continues his mission to describe all the major operators by explaining the Trivial Plan and the power of unique indices. Read more...

Lipoaspiration in your SQL Server Database

Once, when disk space was at a premium, DBAs fought hard to keep the size of their database down. Now there seems less motivation to 'fight the flab' of a database. Fabiano Amorim was watching television recently when the subject matter, cosmetic surgery, gave him the theme and inspiration for this guide to keeping your database fit and trim. Read more...

Showplan Operator of the Week - Merge Interval

When Fabiano agreed to undertake the epic task of describing each showplan operator, none of us quite predicted the interesting ways that the series helps to understand how the query optimiser works. With the Merge Interval, Fabiano comes up with some insights about the way that the Query optimiser handles overlapping ranges efficiently Read more...

ShowPlan Operator of the Week - Merge Join

Did you ever wonder how and why your indexes affect the performance of joins? Once you've read Fabiano's unforgettable explanation, you'll learn to love the MERGE operator, and plan your indexes so as to allow the Query Optimiser to use it. Read more...

Showplan Operator of the Week - SORT

Fabiano introduces another ShowPlan operator that is used to build a query plan, or perform an operation specified in the DML. Once again, Fabiano demonstrates why it is important to be aware of these operators when getting queries to perform well. Read more...

Showplan Operator of the Week - Stream Aggregate

Fabiano continues his mission to explain the Query Optimiser in practical terms, describing, one week at a time, all the major Showplan Operators used by SQL Server's Query Optimiser to build the Query Plan. He discusses the Stream Aggregate function and shows how easy it is to get performance gains in GROUP BY clauses by choosing the right index. Read more...

Showplan Operator of the Week - Row Count Spool

The Row Count Spool operator is a simple component in an execution plan that can make an enormous difference to SQL query plans that would otherwise read a table on every row of the inner input to a nested loop. As this is a common type of query, it illustrates the power that a knowledge of your showplan operators can give you. Read more...

Operator of the Week - Index Spool

If you've ever wondered what happens to your queries if you're not conscientious about creating indexes, then Fabiano Amorim has yet another nugget of Execution Plan wisdom for you. The Query Optimizer's solution is to use the Nonclustered Index Spool, and we're about to learn why. Read more...

Showplan Operator of the Week - Lazy Spool

Continuing to illuminate the depths of SQL Server's Query Optimizer, Fabiano shines a light on the sixth major Showplan Operator on his list: the Lazy Spool. What does the Lazy Spool do that's so special, how does the Query Optimizer use it, and why is it so Lazy? Fabiano explains all... Read more...

Operator of the Week - Spools, Eager Spool

For the fifth part of Fabiano's mission to describe the major Showplan Operators used by SQL Server's Query Optimiser, he introduces the spool operators and particularly the Eager Spool, explains blocking and non-blocking and then describes how the Halloween Problem is avoided. Read more...

Showplan Operator of the Week - BookMark/Key Lookup

Fabiano continues in his mission to describe the major Showplan Operators used by SQL Server's Query Optimiser. This week he meets a star, the Key Lookup, a stalwart performer, but most famous for its role in ill-performing queries where an index does not 'cover' the data required to execute the query. If you understand why, and in what circumstances, key lookups are slow, it helps greatly with optimising query performance. Read more...

Showplan Operator of the Week - Compute Scalar

The third part of Fabiano's mission to describe the major Showplan Operators used by SQL Server's Query Optimiser continues with the 'Compute Scalar' operator. Fabiano shows how a tweak to SQL to avoid a 'Compute Scalar' step can improve its performance. Read more...

Showplan Operator of the Week - Concatenation

Fabiano continues in his mission to describe, one week at a time, all the major Showplan Operators used by SQL Server's Query Optimiser to build the Query Plan. This week he gets the Concatenation operator . Read more...

Showplan Operator of the week - Assert

As part of his mission to explain the Query Optimiser in practical terms, Fabiano attempts the feat of describing, one week at a time, all the major Showplan Operators used by SQL Server's Query Optimiser to build the Query Plan. He starts with Assert Read more...

SQL Server Intellisense VS. Red Gate SQL Prompt

Fabiano Amorim is hooked on today's Integrated Development Environments with built-in Intellisense, so he looked forward keenly to SQL Server 2008's native intellisense. He was disappointed at how it turned out, so turned instead to SQL Prompt. Fabiano explains why he prefers to SQL Prompt, why he reckons it fits in with the way that database developers work, and goes on to describe some of the features he'd like to see in it. Read more...

13 Things You Should Know About Statistics and the Query Optimizer

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...

Query Optimizer and Cartesian Products

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...

Data Correlation Optimization Internals

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...

The Query Optimizer: Date Correlation Optimisation

In SQL Server 2005, a feature was introduced that was hardly noticed, but which might make a great difference to anyone doing queries involving temporal data. For anyone doing Data Warehousing, timetabling, or time-based pricing, this could speed up your queries considerably. Who better to introduce this than Query Optimizer expert, Fabiano Amorim? Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.