Search results matching tag 'Query Optimizer'

Compute Scalars, Expressions and Execution Plan Performance

Paul White — Tue, 04 Sep 2012 22:22:00 GMT

The humble Compute Scalar is one of the least well-understood of the execution plan operators, and usually the last place people look for query performance problems. It often appears in execution plans with a very low (or even zero) cost, which goes some way to explaining why people ignore it.

Some readers will already know that a Compute Scalar can contain a call to a user-defined function, and that any T-SQL function with a BEGIN…END block in its definition can have truly disastrous consequences for performance (see this post by Rob Farley for details). This post is not about those sorts of concerns.

Compute Scalars

The Books Online description of this operator is not very detailed:

This leads people to think that Compute Scalar behaves like the majority of other operators: as rows flow through it, the results of whatever computations the Compute Scalar contains are added to the stream. This is not generally true. Despite the name, Compute Scalar does not always compute anything, and does not always contain a single scalar value (it can be a vector, an alias, or even a Boolean predicate, for example). More often than not, a Compute Scalar simply defines an expression; the actual computation is deferred until something later in the execution plan needs the result.

Compute Scalars are not the only operators that can define expressions. You can find expression definitions with labels like [Expr1008] in many query plan operators, including Constant Scans, Stream Aggregates and even Inserts.

Deferred expression evaluation was introduced in SQL Server 2005 as a performance optimization. The idea is that the number of rows tends to decrease in later parts of the plan due to the effects of filtering and joining (reducing the number of expression evaluations at runtime). To be fair to the Books Online entry, it does mention this behaviour in a note:

The take away from this first section is that a Compute Scalar is most often just a placeholder in the execution plan. It defines an expression and assigns a label to it (e.g. [Expr1000]) but no calculations are performed at that point. The defined expression may be referenced by multiple operators later in the execution plan. This makes counting the number of ‘executions’ more complex than for a regular plan operator., and goes some way to explaining why Compute Scalar operators only ever show estimated row counts (even in an ‘actual’ execution plan).

Execution Plans and the Expression Service

The execution plans you see in Management Studio (or Plan Explorer) are a highly simplified representation that is nevertheless useful for many kinds of plan analysis. The XML form of show plan has a more detail than the graphical representation, but even that is a shallow depiction of the code that actually runs inside SQL Server when a query is executed. It is easy to be seduced into thinking that what we see as an ‘execution plan’ is more complete than it actually is. As a tool for high-level analysis, it is excellent, but do not make the mistake of taking it too literally. More detail on this later.

One more thing to appreciate before we get into a concrete example: the SQL Server engine contains a component known as the Expression Service. This is used by components such as the query optimizer, query execution, and the storage engine to perform computations, conversions and comparisons. It is the call to the expression service in the execution plan that can be deferred and performed by a plan operator other than the source Compute Scalar (or other expression-defining operator).

An Example

I came across a forum thread recently which demonstrated the concepts I want to explore in this post really well. The topic of discussion was delimited string-splitting (an old favourite of a topic, for sure) but I want to emphasise that the particular code is not at all important – it is the query plan execution I want to focus on. The particular string-splitting method employed was one I first saw written about by Brad Shultz; it replaces the delimiters with XML tags, and then uses XML shredding to return the results. I should say that I am not a fan of this technique because it is (a) a strange use of XML; and (b) it does not work for all possible inputs. Anyway, I said the example was not that important in itself, so let’s get on to the first example:

Test One – XML Split

     DECLARE

        @ItemID bigint,

        @Item varchar(8000),

        @Input varchar(8000);

    SET @Input = REPLICATE('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z', 32);

    SELECT

        @ItemID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 

        @Item = x.i.value('./text()[1]', 'varchar(8000)')    

    FROM

    (

        VALUES

            (

            CONVERT

                (xml, 

                '<r>' + 

                REPLACE(@Input, ',', '</r><r>') + 

                '</r>', 0

                )

            )

    ) AS ToXML (result) 

    CROSS APPLY ToXML.result.nodes('./r') AS x(i);

The code above creates a single comma-delimited string containing the letters of the alphabet, repeated 32 times (a total string length of 1632 characters). The string-splitting SELECT statement replaces the commas with XML tags, fixes up the start and end of the string, and then uses the nodes() XML method to perform the actual splitting. There is also a ROW_NUMBER call to number the elements returned in whatever order the nodes() method spits them out. Finally, two variables are used to ‘eat’ the output so returning results to the client does not affect execution time. The execution plan is as follows (click to enlarge):

The Constant Scan operator defines an expression labelled as [Expr1000] which performs the string replacement and conversion to the XML type. There are four subsequent references to this expression in the query plan:

Two XML Reader table-valued functions (performing the nodes() shredding and value() functions)
The Filter operator (a start-up predicate checks that the expression is not NULL)
The Stream Aggregate (as part of a CASE expression that determines what the value() XML method should finally return)

The query takes 3753ms to execute on my laptop. This seems a rather long time to shred a single string containing just 801 elements.

Test Two – The Empty String

The following code makes one small modification to the test one script:

    SELECT

        @ItemID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 

        @Item = x.i.value('./text()[1]', 'varchar(8000)')    

    FROM

    (

        VALUES

            (

            CONVERT

                (xml, 

                '<r>' + 

                REPLACE(@Input, ',', '</r><r>') + 

                '</r>' +

                LEFT(@@DBTS, 0)  -- NEW

                , 0)

            )

    ) AS ToXML (result) 

    CROSS APPLY ToXML.result.nodes('./r') AS x(i);

The only difference in the execution plan is in the definition of [Expr1000] (click to enlarge):

This plan returns the same correct results as the first test, but it completes in 8ms (quite an improvement over 3753ms).

Beyond the Execution Plan – Test One

There is nothing in the query plan to explain why the second form of the query runs 500 times faster than the first. To see what is going on under the covers of the execution engine when executing the slow query, we can attach a debugger and look closer at the code SQL Server is really running. Using SQL Server 2012 and setting a breakpoint on the CXMLTypeProvider::ConvertStringToXMLForES method (as the name suggests, it converts a string to XML for the Expression Service) the first debugger break occurs with this stack trace:

I know that not everyone reading this post will routinely analyse stack traces, so for this first example I will describe the interesting calls from the bottom up (the currently executing code is on top, each line below is the method that called that routine):

There is nothing very interesting below CQueryScan::GetRow (it is just start-up code)
CQueryScan:GetRow drives the plan as we are used to seeing it. You might think of it as approximately the code behind the green SELECT query plan icon in a graphical plan. It reads a row at a time from the first plan operator to its right
The first visible plan operator to execute is the leftmost Nested Loops join (CQScanNLJoinTrivialNew::GetRow). (The Compute Scalar to its left in the graphical plan simply defines an alias for an expression computed by the Stream Aggregate – it does not even exist in the code executed by the execution engine).
The nested loops join operator asks the Sequence Project operator for a row (CQScanSeqProjectNew::GetRow)
The sequence project operator asks the Segment operator for a row (CQScanSegmentNew::GetRow)
The segment operator asks the second Nested Loops Join for a row (CQScanNLJoinTrivialNew::GetRow)
The nested loops join has obtained a row from the Constant Scan and is now preparing to fetch a row from the inner side of the join
The start-up Filter operator is executing its Open() method (CQScanStartupFilterNew::Open)
The start-up Filter calls into the general entry point for the Expression Service to evaluate expression [Expr1000] defined by the Constant Scan
CEsExec::GeneralEval4 calls an ES method to convert a string to XML (ConvertFromStringTypesAndXmlToXml)
The XML type provider executes the code that actually converts the string to XML (ConvertStringToXMLForES)

The stack trace illustrates two things nicely:

The iterative nature of plan operators (one row at a time, Open, GetRow, and Close methods)
An expression defined by one operator (the Constant Scan) being deferred for evaluation until a later operator (the start-up filter) requires the result

Continuing execution with the debugger, the next break occurs when the Table-valued Function to the right of the filter is initialised:

Another break when the Table-valued Function on the lowest branch of the plan initialises:

And finally, a break from the Stream Aggregate, when it needs to evaluate [Expr1016] (which contains a reference to [Expr1000]):

The last two breaks (lowest table-valued function and stream aggregate) execute a total of 801 times. The reason for the slow performance of this query is now clear: [Expr1000] is being computed more than 1604 times. Yes, that means the REPLACE and CONVERT to XML really does run 1604 times on the same value – no wonder performance is so poor!

If you are inclined to test it, the expression service method that performs the REPLACE is sqlTsEs!BhReplaceBhStrStr. It turns out that the string REPLACE is very much faster than conversion to XML (remember XML is a LOB type and also has complex validation rules) so the dominant performance effect is the conversion to XML.

Beyond the Execution Plan – Test Two

Running test two with the debugger attached reveals that only one call is made to CXMLTypeProvider::ConvertStringToXMLForES:

Reading down the stack trace we see that execution of the plan (as we know it) has not started yet. The call to convert the string to XML is called from InitForExecute() and SetupQueryScanAndExpression. The conversion to XML is evaluated once and cached in the execution context (CQueryExecContext::FillExprCache). Once the iterative part of plan execution begins, the cached result can be retrieved directly without calling ConvertStringToXMLForES() 1604 times.

Test Three – Column Reference

Before you go off adding LEFT(@@DBTS, 0) to all your expressions (!) look what happens when we use a table to hold the input data rather than a variable:

    DECLARE

        @ItemID bigint,

        @Item varchar(8000);

    DECLARE @Table AS TABLE (Input varchar(8000) NOT NULL);

    INSERT @Table (Input)

    VALUES (REPLICATE('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z', 32));

    SELECT

        @ItemID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 

        @Item = x.i.value('./text()[1]', 'varchar(8000)')    

    FROM @Table AS t

    CROSS APPLY

    (

        VALUES

            (

            CONVERT

                (xml, 

                '<r>' + 

                REPLACE(t.Input, ',', '</r><r>') + 

                '</r>' +

                LEFT(@@DBTS, 0)

                , 0)

            )

    ) AS ToXML (result) 

    CROSS APPLY ToXML.result.nodes('./r') AS x(i);

Instead of the Constant Scan we saw when using a variable, we now have a table scan and a Compute Scalar that defines the expression:

[Expr1003] = Scalar Operator(
CONVERT(xml,'<r>'+replace(@Table.[Input] as [t].[Input],',','</r><r>')+'</r>'+
substring(CONVERT_IMPLICIT(varchar(8),@@dbts,0),(1),(0)),0))

The other plan details are the same as before; the expression label [Expr1003] is still referenced by the Filter, two Table-valued functions and the Stream Aggregate. The test three query takes 3758ms to complete. Debugger analysis shows that CXMLTypeProvider::ConvertStringToXMLForES is again being called directly by the Filter, Table-valued Functions, and Stream Aggregate.

Test Four – CRYPT_GEN_RANDOM

This final test replaces @@DBTS with the CRYPT_GEN_RANDOM function:

    SELECT

        @ItemID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 

        @Item = x.i.value('./text()[1]', 'varchar(8000)')    

    FROM @Table AS t

    CROSS APPLY

    (

        VALUES

            (

            CONVERT

                (xml, 

                '<r>' + 

                REPLACE(t.Input, ',', '</r><r>') + 

                '</r>' +

                LEFT(CRYPT_GEN_RANDOM(1), 0)    -- CHANGED

                , 0)

            )

    ) AS ToXML (result) 

    CROSS APPLY ToXML.result.nodes('./r') AS x(i);

The execution plan shows an extra Compute Scalar next to the Table Scan:

[Expr1018] = Scalar Operator('<r>'+replace(@Table.[Input] as [t].[Input],',','</r><r>')+'</r>')

The Compute Scalar to the left of that is the same one seen previously, though it now references the new expression in its definition:

[Expr1003] = Scalar Operator(
CONVERT(xml,[Expr1018]+
substring(CONVERT_IMPLICIT(varchar(8000),Crypt_Gen_Random((1)),0),(1),(0)),0))

The remainder of the query plan is unaffected; the same operators reference [Expr1003] as before. As you might have been expecting, this query executes in 8ms again.

Explanation

In test one, there is nothing to stop the query processor deferring execution of the expression, which results in the same expensive computation being performed 1604 times (once each for the filter and first table-valued function, and 801 times each for the second function and the stream aggregate). As an interesting aside, notice that even with OPTION (RECOMPILE) specified, constant-folding only applies to the REPLACE and string concatenation; constant-folding cannot produce a LOB type (including XML).

In test two, we introduced the non-deterministic function @@DBTS. This is one of the functions (like RAND and GETDATE) that are implemented to always return the same value per query (despite being non-deterministic, see the links in the Further Reading section below for more details). These runtime constants are evaluated before the query starts processing rows and the result is cached. There are some hints (ConstExpr labels) in the execution plan that may indicate a runtime constant but I have not found this to be reliable.

In test three, we changed the game again by replacing the reference to a variable with a column reference from a table. The value of this column reference could change for each table row so the engine can no longer treat the expression as a runtime constant. As a consequence, the result is no longer cached and we see the same behaviour (and slow performance) as in test one.

In test four, we replaced the @@DBTS function with the side-effecting function CRYPT_GEN_RANDOM (the other such function is NEWID). One of the effects of this is to disable the deferred evaluation of the expression defined by the Compute Scalar. The result is a XML LOB and a second optimization occurs where handles to the same LOB (evaluated by the defining operator) can be shared by other operators that reference the LOB. This combination of effects means the expensive computation is only performed once in test four, even though the runtime constant caching mechanism is not used. By the way, there was a bug with CRYPT_GEN_RANDOM which meant it was incorrectly assessed by the optimizer in early versions of SQL Server 2008. If you encounter this issue when running the scripts above, apply a later service pack (or use the NEWID function for SQL Server 2005).

Final Thoughts

There is a lot more going on in query execution than we can see in execution plans
In general, there are no guarantees about the order of execution of expressions, or how many times they will be evaluated
Please do not start using @@DBTS or CRYPT_GEN_RANDOM for their undocumented effects
It might be nice if show plan output did indicate if a computation was deferred or cached, but it is not there today
In many cases we can work around the potential for poor performance caused by repeated evaluation by explicitly materializing the result of the expression using a variable, table, non-inline function…though not always
Please do not suggest improvements to the XML string splitter unless it handles all input characters and can accept a semicolon as a delimiter ;c)
I always use SQLCLR for string splitting

Acknowledgements

I would like to particularly thank Usman Butt and Chris Morris from SQL Server Central for their contributions and thoughts which helped me write this entry.

Query Optimizer Deep Dive - Part 4

Paul White — Mon, 30 Apr 2012 13:36:00 GMT

This is the final part in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group and the SQL Saturday events in Wellington, New Zealand and Adelaide, Australia.

Links to other parts of this series: Part 1 Part 2 Part 3

Beating the Optimizer

Our test query produces an optimized physical execution plan that is quite different from the logical form of the query. The estimated cost of the execution plan shown below is 0.0295 units.

Since we know the database schema very well, we might wonder why the optimizer did not choose to use the unique nonclustered index on names in the product table to filter rows based on the LIKE predicate. We could use an index hint to force the name index to be used:

That’s great, and no doubt the index seek is cheaper than the scan we had previously, but the optimizer has still chosen to use a merge join, and that means having both inputs sorted on Product ID. The result of the index seek is ordered by name (the index key) rather than Product ID, so a sort is required. It looks like the new sort adds a little more cost than the seek saves over the scan, because the estimated cost of the query plan with the index hint is 0.0316 units.

Naturally, these numbers are rather small since AdventureWorks is not a large database, but these differences can be important in real systems. Anyway, let’s persist with the index seek idea; why is the optimizer so keen on a merge join, even though it involves an extra sort, and we don’t have an ORDER BY Product ID clause on our query? Without a top-level ORDER BY, we are giving the optimizer the freedom to return results in any order that is convenient – perhaps we can do better by forcing the index seek and a hash join instead of a merge join?

Well the sort has gone, so the plan looks visually a little simpler, but the estimated cost has increased again, to 0.0348 units. Hash join has quite a high start-up cost (and it requires a memory workspace grant). We could try other things, but it certainly seems that the optimizer had it right to begin with in this case.

The manual exploration above shows that the optimizer does generally find a good plan quickly (and sometimes it may even find the best possible plan). The terms ‘good’ and ‘best’ here are measured in terms of the optimizer’s own cost model. Whether one particular plan shape actually executes faster on a given system is another question. I might find, for example, that the first merge join plan runs fastest for me, whereas you might find the seek and sort runs fastest for you. We might both find that which is faster depends on whether the indexes and data needed are in memory or need to be retrieved from persistent storage. All these things aside, the important thing is that we are both likely to find that the optimizer’s plans are pretty good most of the time.

Models and Limitations

There are three principal models used by the optimizer to provide a basis for its reasoning: cardinality estimation, logical relational equivalences, and physical operator costing. Good cardinality estimation (row count expectations at each node of the logical tree) is vital; if these numbers are wrong, all later decisions are suspect. Fortunately, it is relatively easy to check cardinality estimation by comparing actual and estimated row counts in query plans. There are some subtleties to be aware of – for example when interpreting the inner side of a nested loops join, at least in SSMS. If you use the free SQL Sentry Plan Explorer tool, many of these common misinterpretations are handled for you.

The model used in cardinality estimation is complex, and contains all sorts of hairy-looking formulas and calculations. Nevertheless, it is still a model, and as such will deviate from reality at least to some extent. I’ll have more to say later on about what we can do to help ensure good cardinality estimates, but for now we will just note that the model has its roots in relational theory and statistical analysis.

Relational equivalences (such as A inner join B = B inner join A) are the basis for exploration rules in the cost-based optimizer. Not all possible relational transformations are included in the product (remember the goal of the optimizer is to find a good plan quickly, not to perform an exhaustive search of all possible plans). As a consequence, the SQL syntax you use will often affect the plan produced by the optimizer, even where different SQL syntaxes express the same logical requirement. Also, the skilled query tuner will often be able to do better than the optimizer, given enough time and perhaps a better insight to the data. The downside of such manual tuning is that it will usually require manual intervention again in the future as data volumes and distributions change.

Physical operator costing is very much the simplest of the three models, using formulas that have been shown to produce good physical plan selections for a wide range of queries on a wide range of hardware. The numbers used probably do not closely model your hardware or mine, but luckily that turns out not to be too important in most cases. No doubt the model will have to be updated over time as new hardware trends continue to emerge, and there is some evidence in SQL Server 2012 show plan output that things are heading in that direction.

Assumptions

All models make simplifying assumptions, and the cardinality estimation and costing models are no different. Some things are just too hard to model, and other things just haven’t been incorporated into the model yet. Still other things could be modelled, but turn out not to add much value in practice and cost too much in terms of complexity or resource consumption. Some of the major simplifying assumptions that can affect real plan quality are:

All queries start executing with a cold cache
This isn’t as crazy as it sounds. Fetching data from disk tends to dominate the overall cost of a query, modelling the amount of data that can be expected to be in cache already is hard, and this assumption does at least affect everything equally. The optimizer does contain some logic to account for pages that might be in cache after the first access.
Statistical information is independent
Correlations do frequently exist between columns in real databases, so this assumption can be problematic. Multi-column statistics, filtered indexes, and indexed views can sometimes help with this.
Distribution is uniform
This is assumed where the system has no information to the contrary. One example: costing assumes that seeks (lookups) into an index are randomly distributed throughout the full range of the index.

Helping the Optimizer

There are three approaches to working with the optimizer:

Ignore it. It works well out of the box with default settings and automatic statistics.
Override it using syntax tricks, hints, and plan guides.
Provide it with the best information you can, and override it for just a small number of problematic queries.

All three are valid approaches in different circumstances, though the third option is probably the one to recommend as the best starting point. There is much more to helping the optimizer than just making sure statistics are up-to-date, however:

Use a relational design

This is probably the biggest single item. The various models all assume that your database has a reasonably relational design, not necessarily 3NF or higher, but the closer your structures are to relational principles, the better. Cardinality estimation works best with simpler relational operations like joins, projects, selects, unions and group by. Avoid complex expressions and non-relational query features that force cardinality estimation to guess. Also remember that the cost-based optimizer’s exploration rules are based on relational equivalences, so having a relational design and writing relational queries gives you the best chance of leveraging the hard work that has gone into those rules. Some features added in 2005 (e.g. ranking functions) operate on sequences rather than multi-sets (hence the Sequence Project physical operator); the original rules all work with multi-sets, and the seams between the two approaches often show though.

Use constraints

Use check constraints, foreign keys, unique constraints to provide the optimizer with information about your data. Simplification and exploration rules match patterns in the logical tree, and may also require certain properties to be set on the matched nodes. Constraints and keys provide some of the most powerful and fundamental logical properties – omitting these can prevent many of the optimizer’s rules from successfully transforming to an efficient physical plan. If an integer column should only contain certain values, add a check constraint for it. If a foreign key relationship exists, enforce it. If a key exists, declare it. It is not always possible to predict the benefits of providing this type of optimizer information, but my own experience is that it is much greater than most people would ever expect.

Statistics, indexes and computed columns

Provide more than just default-sampled automatically-created statistics where appropriate (for example where distribution is unusual or correlations exist). Create indexes that will provide potentially useful access paths for a wide range of queries. If you have expressions on columns in your WHERE clause, consider adding a computed column that matches the expression exactly. The computed column does not have to be persisted or indexed to be useful; the system can auto-create statistics on the computed column, avoiding cardinality guessing.

Deep Trees

Large, complex queries produce large, complex logical trees. Any errors tend to multiply (perhaps exponentially) as the size of the tree increases, the search space of possible plans expands greatly, and things just become much more difficult in general. Breaking a complex query into smaller, simpler, more relational, steps will generally get greater value out of the optimizer. A side benefit is that small intermediate results stored in a temporary table can have statistics created, which will also help in many cases. There will always be cases where a large complex query is required for ultimate performance, but very often the performance difference is relatively small and may not be worth the future maintenance costs as hand-tuned monolithic queries tend to be fragile.

Opaque operators and new features

User-defined functions (other than the in-line variety) may seem convenient, but they are almost completely opaque to the optimizer – it has to guess at the cardinality and distribution of rows produced. Newer features also tend to have much shallower support in the engine than longer-established features that have been around for multiple versions of the product. By all means use all the shiny new features, just be aware that combining them may produce plans of variable quality.

Trace Flags

To summarize the flags used in this series (all assume 3604 is also active):

7352 : Final query tree
8605 : Converted tree
8606 : Input, simplified, join-collapsed, and normalized trees
8607 : Output tree
8608 : Initial memo
8615 : Final memo
8675 : Optimization stages and times

The above were used in the presentation because they all work from 2005 to 2012. There are a large number of other optimizer-related flags (some of which only work on 2012). Some are listed below for people that like this sort of thing:

2373 : Memory before and after deriving properties and rules (verbose)
7357 : Unique hash optimization used
8609 : Task and operation type counts
8619 : Apply rule with description
8620 : Add memo arguments to 8619
8621 : Rule with resulting tree

As usual, these are undocumented, and unsupported (including by me!) and purely for educational purposes. Use with care at your own risk.

Final Thoughts

I hope you have gained some insight and intuition for the workings of the query optimizer: logical trees, simplification, cardinality estimation, logical exploration and physical implementation. The geeky internals stuff is fun, of course, but I rather hope people came away from these sessions with a better understanding of how query text is transformed to an executable plan, and how relational designs and simpler queries can help the optimizer work well for you. Taking advantage of the optimizer frees up time for more productive things – new projects, tuning the odd interesting query, whatever it is you would rather be doing than fighting the same query-tuning battles over and over again.

There are some great resources out there to learn more about SQL Server. Make full use of them to continue to improve your technical skills and just as importantly, experiment with things to build your experience level. Take a deeper look at query plans and the properties they contain; there is a wealth of information there that sometimes requires a bit of thinking and research to understand, but the insights can be invaluable.

The original slides and demo code is again attached as a zip file below. Thanks for reading, all comments and feedback are very welcome.

Finally, I would like to thank Adam Machanic (blog | twitter) for introducing me to the QUERYTRACEON syntax all that time ago, and Fabiano Amorim (blog | twitter) for his email last year that kick-started the process of developing this presentation and blog series.

Links to other parts of this series: Part 1 Part 2 Part 3

Query Optimizer Deep Dive – Part 3

Paul White — Sun, 29 Apr 2012 07:30:00 GMT

This is the third part in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group and the SQL Saturday events in Wellington, New Zealand and Adelaide, Australia.

Links to other parts of this series: Part 1 Part 2 Part 4

Storage of Alternative Plans

We saw in part 2 how optimizer rules are used to explore logical alternatives for parts of the query tree, and how implementation rules are used to find physical operations to perform each logical steps. To keep track of all these options, the cost-based part of the SQL Server query optimizer uses a structure called the Memo. This structure is part of the Cascades general optimization framework developed by Goetz Graefe. The Memo provides an efficient way to store many plan alternatives via the use of groups.

Each group in the Memo initially contains just one entry – a node from the input logical tree. As exploration and implementation phases progress, new groups may be added, and new physical and logical alternatives may be added to existing groups (all alternatives in a group share the same logical properties, but will often have quite different physical properties). Groups are also shared between plan alternatives where possible, allowing Cascades to search more plans in the same time and space compared with other optimization frameworks.

Continuing with our example query, the input tree to cost-based optimization is first copied into the Memo structure. We can see this structure using trace flag 8608:

     USE AdventureWorks2008R2;

    GO

    SET NOCOUNT ON;

    DBCC FREEPROCCACHE;

    DBCC TRACEON(3604);

    GO

    -- Initial memo contents

    SELECT

        p.Name,

        Total = SUM(inv.Quantity)

    FROM Production.Product AS p

    JOIN Production.ProductInventory AS inv ON

        inv.ProductID = p.ProductID

    WHERE

        p.Name LIKE N'[A-G]%'

    GROUP BY

        p.Name

    OPTION (QUERYTRACEON 8608);

As a reminder, this is the logical input tree to cost-based optimization shown in part 2:

This is copied to the Memo, one group per logical node, illustrated below:

The group numbers and linked structure shown are obtained directly from the trace flag output:

Each group has an entry number (all zero above since there is only one logical entry per group at this stage), with a logical operation (e.g. LogOp_Join), and the group numbers of any child groups (e.g. the logical join in group 13 references its two inputs, groups 8 and 9, and a logical comparison defined by the sub-tree starting at group 12).

Optimization Phases

One the initial Memo has been populated from the input tree, the optimizer runs up to four phases of search. There is a documented DMV, sys.dm_exec_query_optimizer_info, that contains a number of counters specific to query optimization. Four of these counters are ‘trivial plan’, search 0’, ‘search 1’, and ‘search 2’. The value for each counter keeps track of the number of times a search phase was entered. By recording the values before and after a specific optimization, we can see which phases were entered. The current counter values on one of my test SQL Server instances is shown below, as an example:

We look at each of the four phases in a bit more detail next:

Search 0 – Transaction Processing

This phase is primarily concerned with finding good plans for OLTP-type queries, which usually join a number of tables using a navigational strategy (looking up a relatively small number of rows using an index). This phase primarily considers nested-loops joins, though hash match may be used when a loops join implementation is not possible. Many of the more advanced (and costly to explore) optimizer rules are not enabled during this search phase – for example search 0 never considers indexed view matching or parallel plans.

Search 1 – Quick Plan (also known as Complex Query I)

This search phase can use most or all of the available rules, can perform limited join reordering, and may be run a second time (to consider parallel plans only) if the first run produces a plan with a high enough cost. Most queries find a final plan during one of the Search 1 runs.

Search 2 – Full Optimization

This phase uses the most comprehensive search configuration, and may result in significant compilation times in some cases. The search is either for a serial or parallel plan, depending on which type was cheaper after search 1.

The scripts accompanying this series show another way to see which phases were run, using trace flag 8675. The output provides some extra insight into how things work (for example it shows the number of optimization moves (tasks) made in each stage). The only documented and supported way to see the search phases is via the DMV, however.

Entry and Termination Conditions

Each phase has entry conditions, a set of enabled rules, and termination conditions. Entry conditions mean that a phase may be skipped altogether; for example, search 0 requires at least three joined tables in the input tree. Termination conditions help to ensure the optimizer does not spend more time optimizing than it saves – if the current lowest plan cost drops below a configured value, the search will terminate early with a ‘Good Enough Plan Found’ result.

The optimizer also sets a budget at the start of a phase for the number of optimization ‘moves’ it considers sufficient to find a pretty good plan (remember the optimizer’s goal is to find a good enough plan quickly). If the process of exploring and implementing alternatives exceeds this ‘budget’ during a phase, the phase terminates with a ‘Time Out’ message. Early termination (for whatever reason) is part of the optimizer’s design, completely normal, and not generally a cause for concern.

From time to time, we might wish that the optimizer had different goals – perhaps that we could ask it to continue searching for longer – but this is not how it works. It is all too easy to over-spend on optimization (finding transformations is not hard – finding ones that are robustly useful in a wide range of circumstances is), and full cost-based exploration is a memory and processor-intensive operation. The optimizer contains many heuristics, checks and limits to avoid exploring unpromising alternatives, to prune the search space as it goes, and ultimately produce a pretty good plan pretty quickly, most of the time, on most hardware, in most databases.

Costing

First a quick summary: The optimizer runs up to four phases, each of which performs exploration using rules (finding new logical alternatives to some part of the tree), then a round of implementation rules (finding physical implementations for a logical part of the tree). New logical or physical alternatives are added to the Memo structure – either as an alternative within an existing group, or as a completely new group. Note that the Memo allows the optimizer to consider very many alternatives at once in a compact and quick-to-search structure; the optimizer does not just work on one overall plan, performing incremental improvements (this is a common misconception).

Having found all these alternatives, the optimizer needs a way to choose between them. Costing runs after each round of implementation rules, producing a cost value for each physical alternative in each group in the memo (only physical operations can be costed, naturally). Costing considers factors like cardinality, average row size, expected sequential and random I/O operations, processor time, buffer pool memory requirements, and the effect of parallel execution.

Like many areas of optimization, the costing calculations are based on a complex mathematical model. This model generally provides a good basis on which to compare alternatives internally, regardless of the workload or particular hardware configuration. The costing numbers do not mean very much by themselves, so it is generally unwise to compare them between statements or across different queries. The numbers are perhaps best thought of as a unit-less quantity, useful only when comparing alternative plans for the same statement.

The model is just that of course: a model, albeit one that happens to produce very good results for most people most of the time. The slide deck that accompanies this series contains details of some of the simplifying assumptions made in the model.

Final Memo Contents

At the end of each search phase, the Memo may have expanded to include new logical and physical alternatives for each group, perhaps some completely new groups. We can see the contents after each phase using trace flag 8615:

    -- Final memo

    SELECT

        p.Name,

        Total = SUM(inv.Quantity)

    FROM Production.Product AS p

    JOIN Production.ProductInventory AS inv ON

        inv.ProductID = p.ProductID

    WHERE

        p.Name LIKE N'[A-G]%'

    GROUP BY

        p.Name

    OPTION (RECOMPILE, QUERYTRACEON 8615);

The final Memo (after each phase, remember) can be quite large, despite the techniques employed to constrain the search space. Our simple test query only requires a single run through search 1, and terminates early after 509 moves (tasks) with a ‘Good Enough Plan Found’ message. Despite that, the Memo contains 38 groups (up from 18) with many groups containing several alternatives. The extract below highlights just the groups that relate directly to the join implementation:

Looking at the green shaded areas, group 13 item 0 was the original (joining groups 8 and 9 and applying the condition described by the sub-tree starting with group 12). It has gained an logical alternative (via the Join Commute rule, reversing the join input groups), and two surviving physical implementations: a one-to-many merge join as item 7, with group 8 option 2 as the first input, and group 9 option 2 as the second; and a physical inner Apply driven by group 8 option 6, on group 30 option 2. Group 20 is completely new – another one-to-many merge join option, but this time with inputs from groups 19.4 and 8.2 with 12 familiar as the join condition. Again, the scripts accompanying this series can be used to explore the details further, if you wish.

The final selected plan consists of physical implementations chosen by starting with the lowest-cost physical option in the root group (18, shaded pink above). The sub-tree from that point has a total estimated cost of 0.0295655 (this is the same cost shown in the final graphical execution plan). The plan is produced by following the links in the Memo, from group 18 option 4, to group 20 option 6, and so on to the leaves:

End of Part 3

The original slides and demo code are attached below as a zip file.

Links to other parts of this series: Part 1 Part 2 Part 4

Query Optimizer Deep Dive – Part 2

Paul White — Sat, 28 Apr 2012 04:56:00 GMT

This is the second part in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group and the SQL Saturday events in Wellington, New Zealand and Adelaide, Australia.

Links to other parts of this series: Part 1 Part 3 Part 4

Cost-Based Optimization Overview

The input to cost-based optimization is a tree of logical operations produced by the previous optimization stages discussed in part one. Cost-based optimization takes this logical tree, explores logical alternatives (different logical tree shapes that produce the same results), generates physical implementations, assigns an estimated cost to each, and finally chooses the cheapest physical option overall.

The goal of cost-based optimization is not to find the best possible physical execution plan by exploring every possible alternative; rather, the goal is to find a good plan quickly. This approach gives us an optimizer that works pretty well for most workloads, most of the time. If you think about it, that’s quite an achievement – after all, my database is likely very different from yours, and we probably run on quite different hardware as well. The point about finding a good plan quickly is also important – generally, we would not want the optimizer to spend hours optimizing a query that runs for only a few seconds.

This design has a number of important consequences. First, a skilled query tuner will often be able to come up with a better plan than the optimizer does. In some cases, this will because the human can reason about the query in a way the optimizer cannot. Other times, it is simply a question of time – we might be happy spending half a day finding a great execution plan for a crucial query, whereas the optimizer places strict limits on itself to avoid spending more time on optimization than it saves on a single execution.

Perhaps a future version of SQL Server will allow us to configure the optimizer to spend extra time on a particular query – today, we have to use query hints and ‘manual optimizations’ to encourage a particular execution plan shape. The down side to circumventing the optimizer’s natural behaviour in this way is that we then have to take responsibility for ensuring that the execution plan remains good in the future, as data volumes and distributions change. In most systems, it is best to limit the number of manually-tuned queries to a minimum, and thoroughly document any tricks you use to obtain a particular plan.

Input Tree to Cost-Based Optimization

Back to the task at hand. We have a sample query (reproduced below) that has been through all the previous optimization stages, did not qualify for a trivial plan, and so needs to go through the cost-based optimization process.

     SELECT

        p.Name,

        Total = SUM(inv.Quantity)

    FROM 

        Production.Product AS p,

        Production.ProductInventory AS inv

    WHERE

        inv.ProductID = p.ProductID

        AND p.Name LIKE N'[A-G]%'

    GROUP BY

        p.Name;

The tree of logical operations passed to the optimizer looks like this:

This looks very similar to the original logical tree seen in part one, though cardinality estimates have been added to each primary node, and the tree layout has been expanded a little into a form that is easier for the system to work with. In addition to simple cardinality estimates, remember that each node also has statistics objects (histograms and frequency information) derived from the familiar statistics associated with the tables in the query.

The tree above was built from information obtained using trace flag 3604 and 8606 (see part one for more details). Trace flag 8606 shows the tree at various stages, the diagram above is built from the view after project normalization:

Properties

There are also a number of other ‘properties’ associated with each node, that have been added by previous optimization stages. These properties include logical attributes, such as:

Output columns and expressions
Uniqueness (key) information
Type information and nullability
Functional dependencies
Domain ranges for each column or expression

There are also some physical properties that may be present on each node, for example:

Sort order at each node
Partitioning information
Halloween protection

The Halloween protection information is worth explaining in a bit more detail. A query generally executes as a pipeline, with rows being requested one at a time from the top of the tree – so rows are in effect pulled up the tree one at a time. This pipeline arrangement has a number of important advantages, but a problem can arise where the query modifies data: changes made by the query can affect rows being read by the same query – the classic example is a query that adds 10% to the salaries of every employee. When processed as a pipeline, we can get stuck in an infinite loop as rows that have had 10% added re-qualify for reading. This problem happened to be first discovered on 31 October 1976 and became known as the Halloween Problem.

One solution is to separate operations that read data from those that update data by reading all rows into temporary storage before performing any updates. In SQL Server, this simple solution typically manifests as an Eager Table Spool in the query plan – all rows are written eagerly to temporary storage before any updates are performed. This is a bit of a sledgehammer solution though, so the optimizer keeps track of which columns need protection from the Halloween problem (and how much protection they need) at each node by setting properties. Some logical operations (like sorting) naturally mean that all input rows have to be read before the first output row can be produced. These operations provide Halloween protection naturally, so an explicit Eager Table Spool would not be necessary. There are a number of logical operations that can provide some degree of Halloween protection, and properties are used to ensure that just enough protection is provided, at minimum cost.

Rules

The optimizer contains rules to transform trees. There are four classes of rule:

Simplification
Exploration
Implementation
Physical property enforcement

Simplification rules transform some part of the logical tree to a simpler logical form, and are responsible for the simplification transforms we saw in part one. Exploration rules run only during cost-based optimization, generating new logical equivalents for some part of the existing logical tree. Implementation rules produce a physical operation (like a hash or merge join) for a logical operation (a logical join). Property enforcement rules introduce new elements to the logical tree to enforce some desired physical property at that point, for example to enforce a particular sorted order of rows (as might be required by a merge join or stream aggregate).

There are 395 rules in SQL Serer 2012, most of which perform quite simple operations; it is the fact that many rules can be run in various orders that accounts for the apparent complexity of optimizer behaviour. The way that simple rules can combine to produce complex behaviours reminds me of Conway’s Game of Life – a cellular automation program with only four rules that nevertheless can produce extraordinarily complex and beautiful patterns as the rules are applied over and over.

Exploration Rule Examples

One example of a simple exploration rule is SELonJN, a rule that merges a suitable relational SELECT (row filter) into a logical join operation:

Another common exploration rule that generates a new logical alternative is JoinCommute:

As the name suggests, JoinCommute explores a different logical join order, exploiting the fact that A JOIN B is equivalent to B JOIN A for inner joins. Though logically equivalent, the different join orders may have different performance characteristics once the logical alternatives are translated to a physical implementation by a later implementation rule. A third exploration rule that is relevant to our test query is GbAggBeforeJoin, a rule that explores the possibility of pushing an aggregation operation under a join:

Implementation Rule Examples

As mentioned previously, implementation rules transform part of a logical tree into a physical alternative:

The diagram shows three join implementation rules, JNtoNL (nested loops), JNtoHS (hash join), JNtoSM (sort-merge join); two implementations for Group-By Aggregate, GbAggToStrm (Stream Aggregate) and GbAggToHS (Hash Aggregate); SelectToFilter, GetToScan, and GetIdxToRng.

Which Rules Were Used?

If we want to see which rules were used when optimizing a query, one option is to use an undocumented view which shows the number of times a rule has been asked to estimate how valuable it might be in the current context (its ‘promise’ value), the number of times the rule was used to generate an alternative section of the tree (‘built’), and the number of times the output of the rule was successfully incorporated into the search space (‘succeeded’). A sample of the contents of the sys.dm_exec_query_transformation_stats DMV is shown below:

By taking a snapshot view of this information before and after optimizing a particular query, we can see which rules were run. The DMV is instance-wide however, so you would need to run these tests on a system to which you have exclusive access. The scripts that accompany this series of posts contain a complete test rig to show the rules used by each query.

End of Part 2

Part three in this series covers the Memo structure and optimization phases in detail. The original presentation slides and demo code are contained in the zip file shown below.

Links to other parts of this series: Part 1 Part 3 Part 4

Query Optimizer Deep Dive - Part 1

Paul White — Fri, 27 Apr 2012 14:38:00 GMT

This is the first in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group and the SQL Saturday events in Wellington, New Zealand and Adelaide, Australia.

Links to other parts of this series: Part 2 Part 3 Part 4

Introduction

The motivation behind writing these sessions is that I have found that relatively few people have a good intuition for the way the optimizer works, partly because the official documentation is rather sparse, and partly because what information is available is dispersed across many books and blog posts. The content presented here is very much geared to my preferred way of learning – it shows the concepts in what seems to me to be a reasonably logical sequence, and provides tools to enable the interested reader to explore further, if desired.

When we write a query, we are not writing a computer program that can be directly executed. SQL is a declarative language used to logically describe the results we want to see. SQL Server goes through a process of compilation and optimization to create a physically executable implementation of the stated logical requirement. To put it another way, a SQL query is the specification for an executable program that SQL Server writes for us. The SQL Server component responsible for finding an efficient physical execution plan for a given logical query requirement is the Query Optimizer.

The SQL Server Core Engine

The diagram below shows conceptually where the Query Optimizer sits in the core SQL Server engine:

Language Processing is concerned with things like parsing the text of the query to make sure it is syntactically valid, binding table references to real database objects, deriving types for expressions, semantic analysis (for example checking that the query is not trying to execute a table), and binding GROUP BY references to the appropriate logical scope. The Query Optimizer aims to find a good physical execution plan that matches the logical semantic of the query, and the Query Executor is responsible for running that plan to completion. Below that, the Storage Engine provides access to physical storage, together with locking and transaction services. Finally, SQL-OS is the layer that provides threading, memory management, and scheduling services.

Using the same colours for the components shown above, a very high-level overview of query processing looks like this:

This series of blog posts is mostly concerned with the “Optimize” step above. It takes as its input a tree of logical operations produced by the previous stages.

Optimization Pipeline

The diagram below shows the principal stages within query optimization, starting with the bound tree produced by the parser and algebrizer. The coloured steps are the ones we will explore in depth – the right hand side of the diagram lists a bit more detail that I talk through when presenting this material live:

Input Tree

We start by looking at the bound logical tree (input tree). To make the discussion a little less abstract, we will use a test query issued against the AdventureWorks sample database. This simple query shows products and the quantity in stock, limited to products with names that start with a letter between A and G:

     -- Test query

    SELECT

        p.Name,

        Total = SUM(inv.Quantity)

    FROM 

        Production.Product AS p,

        Production.ProductInventory AS inv

    WHERE

        inv.ProductID = p.ProductID

        AND p.Name LIKE N'[A-G]%'

    GROUP BY

        p.Name;

The input tree generated by this query is shown below, slightly simplified:

The operations in this tree are all purely logical, and have their roots in Relational Theory. The GET operation logically reads an entire table, though you should not yet be thinking in terms of physical operations such as a scan or a seek. The JOIN is a cartesian product, logically joining every row in one table with every row in the other. The SELECT is not a SQL statement, it is a relational operation: filtering rows based on some condition (known as a predicate). Here, we are applying two SELECT operations to filter on matching product IDs and rows where the product name starts with a letter between A and G. The next logical operation is a Group-By Aggregate, an extension to the basic relational algebra that logically groups rows by some common attribute, and optionally computes an aggregate expression for each group. Finally, we have a relational Project operation, which filters columns – here we only need the Name and Total columns, and those are the only two returned in the logical result set.

To see this tree directly, we can use undocumented trace flag 8605. This flag also requires the common trace flag 3604 to produce output to the SSMS messages window. We can use another undocumented syntax, the QUERYTRACEON hint, to set the trace flag just for this query:

    -- Input tree (ISO-92)

    SELECT

        p.Name,

        Total = SUM(inv.Quantity)

    FROM Production.Product AS p

    JOIN Production.ProductInventory AS inv ON

        inv.ProductID = p.ProductID

    WHERE

        p.Name LIKE N'[A-G]%'

    GROUP BY

        p.Name

    OPTION (RECOMPILE, QUERYTRACEON 8605);

Now, in the SSMS messages tab, we see a textual representation of the tree of logical operators I drew with coloured boxes before:

There are a few minor differences between this and the previous diagram. In particular, the expression computed by the aggregate is added by a separate projection (omitted for clarity before), and the SQL-92 join syntax means the join predicate is more tightly bound to the logical join. Rewriting the query using SQL-89 syntax highlights the second difference, producing a tree closer to the cartesian product followed by a relational SELECT:

    -- Input tree (ISO-89)

    SELECT

        p.Name,

        Total = SUM(inv.Quantity)

    FROM 

        Production.Product AS p,

        Production.ProductInventory AS inv

    WHERE

        inv.ProductID = p.ProductID

        AND p.Name LIKE N'[A-G]%'

    GROUP BY

        p.Name

    OPTION (RECOMPILE, QUERYTRACEON 8605);

Simplification

The first step of the optimization pipeline we will look at is simplification, where the optimizer looks to rewrite parts of the logical tree to remove redundancies and move logical operations around a bit to help later stages. Major activities that occur around the time simplification occurs (I have taken a little artistic licence here to group a few separate stages together) include:

Constant folding
Domain simplification
Predicate push-down
Join simplification
Contradiction detection

Constant folding is the process of evaluating an expression during optimization, rather than repeatedly at execution time. In the following example, the complex expression in the WHERE clause can safely be evaluated early, resulting in “WHERE p.Name LIKE ‘D%’:

    SELECT p.Name

    FROM Production.Product AS p

    WHERE p.Name LIKE SUBSTRING(LEFT(CHAR(ASCII(CHAR(68))), 1) + '%', 1, 2);

Domain simplification enables the optimizer to reason about the range of valid values a column or expression can take. In the next query, the only value for Product ID that logically matches all the predicates in the extended WHERE clause is the single value ‘400’. The query plan shows that simplification has reduced the three predicates to a single equality predicate, “WHERE p.ProductID = 400”:

    SELECT TOP (10) * 

    FROM Production.Product AS p

    WHERE p.ProductID BETWEEN 300 AND 400

    AND p.ProductID BETWEEN 200 AND 500

    AND p.ProductID BETWEEN 400 AND 600;

Predicate push-down involves pushing relational SELECTs (logically filtering rows based on a predicate) down the logical tree toward the leaves. The heuristic here is that this is always a good thing to do (at least as far as expressions that reference a column are concerned) because it eliminates rows early, reducing the number for later logical operations. Moving SELECTs closer to the logical GETs (reading tables) also helps index and computed-column matching.

Join simplification allows the optimizer to remove unnecessary joins, convert logical outer joins to simpler inner joins where the NULLs introduces by the outer join are later rejected by another feature in the query, and remove provably empty subexpressions. This is an incredibly powerful optimizer facility, with its roots again in relational theory. Rob Farley has written (SQL Server MVP Deep Dives) and presented (http://bit.ly/SimpleRob) about ways to use this feature effectively. In particular, he talks about how to write views to benefit from compile-time simplification, and avoid the views-on-views-on-views problem. The following queries illustrate some of the join simplifications:

    -- Remove unnecessary joins

    SELECT

        th.ProductID,

        SUM(th.ActualCost)

    FROM Production.TransactionHistory AS th

    JOIN Production.Product AS p ON

        p.ProductID = th.ProductID

    GROUP BY

        th.ProductID;

    GO

    -- Outer join to join (null rejection)

    SELECT * 

    FROM Production.Product AS p

    LEFT JOIN Production.TransactionHistory AS th ON

        th.ProductID = p.ProductID

    WHERE th.ProductID < 10;

    GO

    -- Complex example combining multiple simplifications

    WITH Complex AS

    (

        SELECT

            pc.ProductCategoryID, pc.Name AS CatName,

            ps.ProductSubcategoryID, ps.Name AS SubCatName,

            p.ProductID, p.Name AS ProductName,

            p.Color, p.ListPrice, p.ReorderPoint,

            pm.Name AS ModelName, pm.ModifiedDate

        FROM Production.ProductCategory AS pc

        FULL JOIN Production.ProductSubcategory AS ps ON

            ps.ProductCategoryID = pc.ProductCategoryID

        FULL JOIN Production.Product AS p ON

            p.ProductSubcategoryID = ps.ProductSubcategoryID

        FULL JOIN Production.ProductModel AS pm ON

            pm.ProductModelID = p.ProductModelID

    )

    SELECT c.ProductID, c.ProductName

    FROM Complex AS c

    WHERE c.ProductName LIKE N'G%';

That last example, with three full outer joins in a CTE (in-line view), simplifies down to a simple index seek on one table.

Cardinality Estimation

The optimizer only has direct information about table cardinality (total number of rows) for base tables. Statistics can provide additional histograms and frequency statistics, but again these are only maintained for base tables. To help the optimizer choose between competing strategies later on, it is essential to know how many rows are expected at each node in the logical tree, not just at the GET leaf nodes. In addition, cardinality estimates above the leaves can be used to choose an initial join order (assuming the query contains several joins).

Cardinality estimation computes expected cardinality and distribution statistics for each node, working up from the leaves one node at a time. The logic used to create these derived estimates and statistics uses a model that makes certain assumptions about your data. For example, where the distribution of values is unknown, it is assumed to be uniform across the whole range of potential values.

You will generally get more accurate cardinality estimates if your queries fit the model used, and, as with many things in the wider optimizer, simple and relational is best. Complex expressions, unusual or novel techniques, and using non-relational features often means cardinality estimation has to resort to guessing.

Most of the choices made by the optimizer are driven by cardinality estimation, so if these are wrong, any good execution plans you might see are highly likely to be down to pure luck.

Trivial Plan

The next stage of the optimizer pipeline is Trivial Plan, which is a fast path to avoid the time and effort involved in full cost-based optimization, and only applies to logical query trees that have a clear and obvious ‘best’ execution plan. The details of which types of query can benefit from Trivial Plan change frequently, but things like joins, subqueries, and inequality predicates generally prevent this optimization. The queries below show some examples where Trivial Plan can, and cannot be applied:

    -- Trivial plan

    SELECT p.ProductID

    FROM Production.Product AS p

    WHERE p.Name = N'Blade';

    GO

    -- Still trivial

    SELECT

        p.Name,

        RowNumber =

            ROW_NUMBER() OVER (

                ORDER BY p.Name)

    FROM Production.Product AS p

    WHERE p.Name LIKE N'[A-G]%';

    GO

    -- 'Subquery' prevents a trivial plan

    SELECT (SELECT p.ProductID)

    FROM Production.Product AS p

    WHERE p.Name = N'Blade';

    GO

    -- Inequality

    SELECT p.ProductID

    FROM Production.Product AS p

    WHERE p.Name <> N'Blade';

One interesting case where a Trivial Plan is not applied is where the estimated cost of the Trivial Plan query exceeds the configured ‘cost threshold for parallelism’. The reasoning here is that any trivial plan that would qualify for a parallel plan suddenly presents a choice, and a choice means the query is no longer ‘trivial’, and we have to go through full cost-based optimization. Taking this to the extreme, a SQL Server instance with the parallelism cost threshold set to zero will never produce a trivial plan (since all queries have a cost greater than zero). At the risk of stating the slightly obvious, note that a plan produced at this stage will always be serial. For experimentation only, the trivial plan stage can also be disabled using trace flag 8757.

You can check whether a query used a trivial plan or not by inspecting the Properties window in SSMS, and clicking on the root node of the graphical query plan. The ‘optimization level’ property will show ‘Trivial’ or ‘Full’ – the latter shows that a trip through full cost-based optimization was required. If a query results in a trivial plan, a physical execution plan is produced and the optimization process ends here.

End of Part 1

The original slides and demo code are contained in the zip file attached below. Links to other parts of this series: Part 2 Part 3 Part 4

Fun with Aggregates

Paul White — Sun, 11 Mar 2012 15:49:00 GMT

There are interesting things to be learned from even the simplest queries. For example, imagine you are given the task of writing a query to list AdventureWorks product names where the product has at least one entry in the transaction history table, but fewer than ten.

One possible query to meet that specification is:

     SELECT

        p.Name

    FROM Production.Product AS p

    JOIN Production.TransactionHistory AS th ON

        p.ProductID = th.ProductID

    GROUP BY

        p.ProductID,

        p.Name

    HAVING

        COUNT_BIG(*) < 10;

That query correctly returns 23 rows (execution plan and data sample shown below):

The execution plan looks a bit different from the written form of the query: the base tables are accessed in reverse order, and the aggregation is performed before the join. The general idea is to read all rows from the history table, compute the count of rows grouped by ProductID, merge join the results to the Product table on ProductID, and finally filter to only return rows where the count is less than ten.

This ‘fully-optimized’ plan has an estimated cost of around 0.33 units. The reason for the quote marks there is that this plan is not quite as optimal as it could be – surely it would make sense to push the Filter down past the join too? To answer that, let’s look at some other ways to formulate this query. This being SQL, there are any number of ways to write logically-equivalent query specifications, so we’ll just look at a couple of interesting ones. The first query is an attempt to reverse-engineer T-SQL from the optimized query plan shown above. It joins the result of pre-aggregating the history table to the Product table before filtering:

    SELECT p.Name

    FROM

    (

        SELECT 

            th.ProductID, 

            cnt = COUNT_BIG(*) 

        FROM Production.TransactionHistory AS th 

        GROUP BY

            th.ProductID

    ) AS q1

    JOIN Production.Product AS p 

        ON p.ProductID = q1.ProductID

    WHERE

        q1.cnt < 10;

Perhaps a little surprisingly, we get a slightly different execution plan:

The results are the same (23 rows) but this time the Filter is pushed below the join! The optimizer chooses nested loops for the join, because the cardinality estimate for rows passing the Filter is a bit low (estimate 1 versus 23 actual), though you can force a merge join with a hint and the Filter still appears below the join. In yet another variation, the < 10 predicate can be ‘manually pushed’ by specifying it in a HAVING clause in the “q1” sub-query instead of in the WHERE clause as written above.

The reason this predicate can be pushed past the join in this query form, but not in the original formulation is simply an optimizer limitation – it does make efforts (primarily during the simplification phase) to encourage logically-equivalent query specifications to produce the same execution plan, but the implementation is not completely comprehensive.

Moving on to a second example, the following query specification results from phrasing the requirement as “list the products where there exists fewer than ten correlated rows in the history table”:

    SELECT p.Name

    FROM Production.Product AS p

    WHERE EXISTS 

    (

        SELECT *

        FROM Production.TransactionHistory AS th 

        WHERE th.ProductID = p.ProductID 

        HAVING COUNT_BIG(*) < 10

    );

Unfortunately, this query produces an incorrect result (86 rows):

The problem is that it lists products with no history rows, though the reasons are interesting. The COUNT_BIG(*) in the EXISTS clause is a scalar aggregate (meaning there is no GROUP BY clause) and scalar aggregates always produce a value, even when the input is an empty set. In the case of the COUNT aggregate, the result of aggregating the empty set is zero (the other standard aggregates produce a NULL). To make the point really clear, let’s look at product 709, which happens to be one for which no history rows exist:

    -- Scalar aggregate

    SELECT COUNT_BIG(*)

    FROM Production.TransactionHistory AS th 

    WHERE th.ProductID = 709;

    -- Vector aggregate

    SELECT COUNT_BIG(*)

    FROM Production.TransactionHistory AS th 

    WHERE th.ProductID = 709

    GROUP BY th.ProductID;

The estimated execution plans for these two statements are almost identical:

You might expect the Stream Aggregate to have a Group By for the second statement, but this is not the case. The query includes an equality comparison to a constant value (709), so all qualified rows are guaranteed to have the same value for ProductID and the Group By is optimized away.

In fact there are some minor differences between the two plans (the first is auto-parameterized and qualifies for trivial plan, whereas the second is not auto-parameterized and requires cost-based optimization), but there is nothing to indicate that one is a scalar aggregate and the other is a vector aggregate. This is something I would like to see exposed in show plan so I suggested it on Connect. Anyway, the results of running the two queries show the difference at runtime:

The scalar aggregate (no GROUP BY) returns a result of zero, whereas the vector aggregate (with a GROUP BY clause) returns nothing at all. Returning to our EXISTS query, we could ‘fix’ it by changing the HAVING clause to reject rows where the scalar aggregate returns zero:

    SELECT p.Name

    FROM Production.Product AS p

    WHERE EXISTS 

    (

        SELECT *

        FROM Production.TransactionHistory AS th 

        WHERE th.ProductID = p.ProductID 

        HAVING COUNT_BIG(*) BETWEEN 1 AND 9

    );

The query now returns the correct 23 rows:

Unfortunately, the execution plan is less efficient now – it has an estimated cost of 0.78 compared to 0.33 for the earlier plans. Let’s try adding a redundant GROUP BY instead of changing the HAVING clause:

    SELECT p.Name

    FROM Production.Product AS p

    WHERE EXISTS 

    (

        SELECT *

        FROM Production.TransactionHistory AS th 

        WHERE th.ProductID = p.ProductID

        GROUP BY th.ProductID

        HAVING COUNT_BIG(*) < 10

    );

Not only do we now get correct results (23 rows), this is the execution plan:

I like to compare that plan to quantum physics: if you don’t find it shocking, you haven’t understood it properly :) The simple addition of a redundant GROUP BY has resulted in the EXISTS form of the query being transformed into exactly the same optimal plan we found earlier. What’s more, in SQL Server 2008 and later, we can replace the odd-looking GROUP BY with an explicit GROUP BY on the empty set:

    SELECT p.Name

    FROM Production.Product AS p

    WHERE EXISTS 

    (

        SELECT *

        FROM Production.TransactionHistory AS th 

        WHERE th.ProductID = p.ProductID

        GROUP BY ()

        HAVING COUNT_BIG(*) < 10

    );

I offer that as an alternative because some people find it more intuitive (and it perhaps has more geek value too). Whichever way you prefer, it’s rather satisfying to note that the result of the sub-query does not exist for a particular correlated value where a vector aggregate is used (the scalar COUNT aggregate always returns a value, even if zero, so it always ‘EXISTS’ regardless which ProductID is logically being evaluated).

The following query forms also produce the optimal plan and correct results, so long as a vector aggregate is used (you can probably find more equivalent query forms):

WHERE Clause

    SELECT

        p.Name

    FROM Production.Product AS p

    WHERE 

    (

        SELECT COUNT_BIG(*) 

        FROM Production.TransactionHistory AS th

        WHERE th.ProductID = p.ProductID

        GROUP BY ()

    ) < 10;

APPLY

    SELECT p.Name

    FROM Production.Product AS p

    CROSS APPLY

    (

        SELECT NULL

        FROM Production.TransactionHistory AS th 

        WHERE th.ProductID = p.ProductID

        GROUP BY ()

        HAVING COUNT_BIG(*) < 10

    ) AS ca (dummy);

FROM Clause

    SELECT q1.Name 

    FROM

    (

        SELECT

            p.Name, 

            cnt = 

            (

                SELECT COUNT_BIG(*) 

                FROM Production.TransactionHistory AS th 

                WHERE th.ProductID = p.ProductID 

                GROUP BY ()

            )

        FROM Production.Product AS p

    ) AS q1 

    WHERE

        q1.cnt < 10;

This last example uses SUM(1) instead of COUNT and does not require a vector aggregate…you should be able to work out why :)

    SELECT q.Name 

    FROM

    (

        SELECT

            p.Name, 

            cnt = 

            (

                SELECT SUM(1)

                FROM Production.TransactionHistory AS th 

                WHERE th.ProductID = p.ProductID

            )

        FROM Production.Product AS p

    ) AS q

    WHERE q.cnt < 10;

The semantics of SQL aggregates are rather odd in places. It definitely pays to get to know the rules, and to be careful to check whether your queries are using scalar or vector aggregates. As we have seen, query plans do not show in which ‘mode’ an aggregate is running and getting it wrong can cause poor performance, wrong results, or both.

Twitter: @SQL_Kiwi
email: SQLkiwi@gmail.com

Query Optimizer Gone Wild - Full-Text

jchang — Mon, 20 Feb 2012 01:05:00 GMT

Overall, SQL Server has become a very capable and mature product, with a very powerful engine and sophisticated query optimizer. Still, every now and then, a certain query structure throws the optimizer for a loop resulting in an execution plan that will take forever. The key to identifying this type of problem begins with the exeuction plan. First, the plan cost does not tell the whole story. It is necessary to know which execution plan operations can run well on modern server systems and which do not. Solving the problem can be a simple matter of rewriting the SQL to a different execution plan, one that uses good execution components.

Of course, when working with 3rd party applications that do not use stored procedures, it is necessary to convince the ISV, often first talking to someone who does not write code, not mention someone with any understanding of the SQL Server query optimizer.

Anyways, the topic here is Full-Text Search, in particular CONTAINS and CONSTAINSTABLE. CONTAINS is "a predicte used in a WHERE clause" per Microsoft documentation, while CONTAINSTABLE acts as a table.

Consider the two queries below, the first is an example of CONTAINS and the second an example of CONTAINSTABLE.

We might intuitively think that there should be no difference between the two, which is why in SQL Server, we should never even bother with intuition and instead always, repeat always, focus on the execution plan.

Both queries perform a Full-Text search, both the CONTAINS function must also scan a index on the source table to get the count. The CONTAINSTABLE function on the other hand, being a row source, can be summed directly. In this example, the Document table is on the order of 60GB excluding lob structures stored out of the table, the index in question is 150MB, and there are 16M rows in the table. Both queries run in about 2+ sec elapsed. The first consumes 6 CPU-sec running in 2.2 sec, while the second query consumes 2.6 CPU-sec in 2.6 sec as it the not a parallel plan. OK, so the first query runs slightly faster with parallel execution on the Stream Aggregate, while the second is single-threaded. But the Full-Text function itself is not multi-threaded, and probably the bulk of the 2.2 sec of the first query. So why is the CONTAINS operation beneficial?

Before jumping to the title topic - Query Optimizer Gone Wild, lets look at another query, shown below.

Below is the query plan. Note that neither column in the search argument is indexed, because this is an administrative query that the executive director runs once every month as a Key Performance Indicator, which is also probably related to why I am not an executive. So the execution plan is a table scan.

The IO portion of the full table (Clustered Index) scan is 5677 (1350 pages or 10.5MB has an IO cost of 1 in a scan operation). For this particular example, the Fulltext Match Table Valued Function is assessed a plan cost of 1.6. When combined with the other components, Stream Aggregate and Filter, the total plan cost of this Full-Text search is 4.14.

On this particular system, a Xeon E7-48xx, with max degree of parallelism set to 8, the table scan query consumes 25 CPU-sec running 3.8 sec when data is in memory. At MAXDOP 20, the query consumes 37 CPU-sec running in 2.1sec. This is why I emphasized earlier that plan cost is not hugely relavent.

(In case you were curious, the 60GB, 16M row table scan consumes 23 CPU-sec at DOP 1, 24.5 CPU-sec, 12.3 sec elapsed at DOP 2, the same 24.5 CPU-sec, 6.6 sec elapsed at DOP 4, i.e., excellent scaling to DOP 8, and good continued scaling to DOP 20. This is an amazing 2.6GB/s per sec per core, and 700,000 rows per sec per core. Of course, this is a wide table with 175 columns averaging 3750 bytes per row.)

The Wild Plan

The actual query we are interested in is not the ones discussed above. Due to the nature of the application, PowerPoint documents can be indicated by the expression shown in any of three columns, one of which is part of a Full-Text catalog, as expressed by the query below.

(It actually turns out that this query is not entirely correct from the technical perspective, but it is correct by executive direction; also, part of the reason why I will never be an executive.)

Given that this is a relatively simple SQL expression, and that the two elements of this query are known to run quickly, we might intuitively expect this composite query to also run quickly. But as I said earlier, do not even bother with intuition, and always always focus on the execution plan as shown below.

Can you say: "We are sooo screwed!"

What is wrong with this plan? Let us compare this plan with the table scan plan from above. Both plans have approximately equal cost, as the 60GB table scan dominates. The extra Table Valued function contributes very little, as shown below.

The problem with this execution plan is that there is a fat arrow (indicating very many rows, 16M in fact) coming from the outer source (top) with the Full-Text search in the inner source (bottom). For each row from the outer source, the inner source is evaluated.

This is why I said to not pay much attention to the plan cost, including components with high cost relative to other components.

Instead, it is important to focus on the SQL operations, the number of rows and pages involved, along with our knowledge of how each operation behaves non-parallel and parallel execution plans and the difference between data in memory versus on hard drive, and now also SSD storage.

This execution plan will attempt to performance 16M Full-Text searches. We have already established that this particular Full-Text search takes about 2 sec. The full query might take 32M sec. There are 86,400 seconds per day. We should expect this query to complete in 370 days, assuming there is not a need to reboot the OS after a critical security patch. And oh by the way, we need to run this query next month too, every month as a matter of fact.

Note, in the first Query Optimizer Gone Wild, the topic was a loop join with a table scan on the inner source. So this is another loop join example.

The Tamed Plan

Now that we have identified a problem, and we know exactly what to look for in the execution plan, it is time to solve the problem. Because we have been working with SQL Server and other DBMS engines built around a cost base optimizer for many years, we know exactly what to do. The solution is to rewrite the SQL to get a good execution plan where the two base operations forle which we know that have run time is reasonable are only executed only once each.

The query below meets this objective.

The execution plan is below.

This query consumes 37 CPU-sec, and 6.9 sec elapsed. Given that the two component elements of this query combined for 27 CPU-sec and 6.4 sec elapsed, the hash join and 2 parallelism repartition streams component increased true cost by 10 CPU-sec, but almost a minuscue 0.5 sec elapsed time.

I suppose that I should file this on Connect, but Microsoft locked my account out, and does not want to send me the unlock code. So I am posting this here.

How to Find the Statistics Used to Compile an Execution Plan

Paul White — Tue, 20 Sep 2011 15:22:15 GMT

In this post, I show you how to determine exactly which statistics objects were used by the query optimizer to produce an execution plan.

Trace Flags

We will need three undocumented trace flags. The first one (3604) is well-known – it redirects trace output to the client so it appears in the SSMS messages tab.

The second trace flag is 9292. With this enabled, we get a report of statistics objects which are considered ‘interesting’ by the query optimizer when compiling, or recompiling the query in question. For potentially useful statistics, just the header is loaded.

The third trace flag is 9204. With this enabled, we see the ‘interesting’ statistics which end up being fully loaded and used to produce cardinality and distribution estimates for some plan alternative or other. Again, this only happens when a plan is compiled or recompiled – not when a plan is retrieved from cache.

You can enable and disable these flags with the usual DBCC TRACEON and TRACEOFF commands, but it is also possible to enable them just for a particular statement using the undocumented QUERYTRACEON query hint (demonstrated below).

Sample Query

     DBCC FREEPROCCACHE

    SELECT 

        p.Name,

        total_quantity = SUM(th.Quantity)

    FROM AdventureWorks.Production.Product AS p

    JOIN AdventureWorks.Production.TransactionHistory AS th ON

        th.ProductID = p.ProductID

    WHERE

        th.ActualCost >= $5.00

        AND p.Color = N'Red'

    GROUP BY

        p.Name

    ORDER BY

        p.Name

    OPTION

    (

        QUERYTRACEON 3604,

        QUERYTRACEON 9292,

        QUERYTRACEON 9204

    )

The DBCC FREEPROCCACHE is just there to empty the plan cache so we get a compilation. You can also evict the current plan from cache if you know its handle (SQL Server 2008) or use a RECOMPILE query hint. Using RECOMPILE is often convenient, but you may get a different plan compared to that obtained without the hint. Note that compiling the query is enough – we do not need to execute the query; simply requesting an ‘estimated plan’ will do. It doesn’t hurt to run it either though, just to be clear.

Sample Output

    Stats header loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.Product, 

        IndexId: 1, 

        ColumnName: ProductID, 

        EmptyTable: FALSE

    Stats loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.Product, 

        IndexId: 1, 

        ColumnName: ProductID, 

        EmptyTable: FALSE

    Stats header loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.Product, 

        IndexId: 3, 

        ColumnName: Name, 

        EmptyTable: FALSE

    Stats loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.Product, 

        IndexId: 3, 

        ColumnName: Name, 

        EmptyTable: FALSE

    Stats header loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.Product, 

        IndexId: 11, 

        ColumnName: Color, 

        EmptyTable: FALSE

    Stats loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.Product, 

        IndexId: 11, 

        ColumnName: Color, 

        EmptyTable: FALSE

    Stats header loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.TransactionHistory, 

        IndexId: 2, 

        ColumnName: ProductID, 

        EmptyTable: FALSE

    Stats loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.TransactionHistory, 

        IndexId: 2, 

        ColumnName: ProductID, 

        EmptyTable: FALSE

    Stats header loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.TransactionHistory, 

        IndexId: 5, 

        ColumnName: ActualCost, 

        EmptyTable: FALSE

    Stats loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.TransactionHistory, 

        IndexId: 5, 

        ColumnName: ActualCost, 

        EmptyTable: FALSE

There’s no sign of an official way to get this very useful information in Denali, despite it being requested many times over the years. Trace flag 9204 works at least as far back as SQL Server 2005. Both 92xx flags work in 2008, R2, and Denali CTP 3.

Enjoy!

SQLkiwi@gmail.com
@SQL_Kiwi

Oracle Index Skip Scan

jchang — Mon, 13 Jun 2011 03:55:00 GMT

There is a feature, called index skip scan that has been in Oracle since version 9i. When I across this, it seemed like a very clever trick, but not a critical capability. More recently, I have been advocating DW on SSD in approrpiate situations, and I am thinking this is now a valuable feature in keeping the number of nonclustered indexes to a minimum.

Briefly, suppose we have an index with key columns: Col1, Col2, in that order. Obviously, a query with a search argument (SARG) on Col1 can use this index, assuming the data distribution is favorable. However, a query with the SARG on Col2 but not Col1 cannot use the index in a seek operation.

Now suppose that the cardinality of Col1, (the number of distinct values of Col1), is relatively low. The database engine could seek each distinct first value of Col1 and the specified SARG on Col2. Microsoft SQL Server currently does not have the Oracle Index Skip-Scan feature, but the capability can be achieved with a work-around.

In this example, the LINEITEM table has a cluster key on columns L_SHIPDATE, L_ORDERKEY, but does not have an index leading with L_ORDERKEY. Our query is to find a specific Order Key in the LineItem table. If there is a table with the distinct date values, DimDate, we could force a loop join from the DimDate table to LineItem (even though only columns from LineItem are required) to get the execution plan below.

The question is now: how effective is this technique? The most efficient execution plan is of course, to have an index leading with the Order Key column. But the situation calls for keeping the number of nonclustered indexes to an absolute minimum. So how does the above execution plan compare with a table scan?

A table scan, in this type of query, such that only few rows meet an easy to evaluare SARG, might run at about 1GB/s per core. Note this is far higher than the 200MB/sec cited in the Microsoft Fast Track Data Warehouse documents. This is because the FTDW baseline is a table scan that aggregates several columns of every row. And not only that, a Hash Match is also required to group the results. Basically, a needle in haystack table scan runs much faster than the more complex aggregate and group scan. At most, a 1GB table scan might acceptable for a non-parallel execution plan and even a 50GB table scan could be tolerable on a powerful 32-core system with an unrestricted parallel execution plan.

A loop join can run somewhere in range of 100,000-200,000 seeks/sec to the inner source. Realistically, a Data Warehouse with 10 years data has distinct 3652 days (depending on the leap year situation). A loop join with 3650 rows from the outer source should run some where around 36ms. Even if the DW had 20 years data, this is still acceptable, on the assumption that the non-lead column Order Key search is in the minority, with the plus being one less index on the big table is required. If the query could be bounded to with a single year or quarter-year, then we are approaching the efficiency of having the extra nonclustered index.

New Book on the Query Optimizer

Andrew Kelly — Sat, 09 Apr 2011 22:30:04 GMT

A friend of mine Benjamin Nevarez has written a book on the inner workings of the Query Optimizer and it was just released. I read a preview and was definitely impressed so I expect the full version to be just as good. More details can be found here and it is available at Amazon here as well. I hope you enjoy it.

Andy