Exploring the SQL Server Query Optimizer

detective-1424831_640

The SQL Server Query Optimizer is always of great interest to students taking Learning Tree’s Developing SQL Queries for SQL Server and Developing High-Performance SQL Server Databases courses. Today we’ll look beyond the operators in the query plan and focus our attention on some aspects of the optimization process itself.

Optimization is always a balancing act; if you take too long to find the best plan, it defeats the purpose of optimization. The optimizer must therefore decide how much optimization is appropriate for a query. For example, consider the query:

SELECT * FROM DimGeography g.

Not much to be done here and the optimizer knows it. The optimizer throws back what it calls the “trivial” plan. You can see this in the query plan XML or in the properties of the SELECT icon in the graphical query plan. You can see the XML by right-click on the graphical plan and clicking “Show Execution Plan XML…”

"Trivial" in XML PLan

This information is also available in the Properties window for the Select icon in the graphical plan. We can hit F4 and then click the Select icon, or we can right-click on the Select icon and choose properties. Either way we can view the “Optimization Level” property in the list.

PropertieWindow Trivial

Now let’s make the query a little less trivial.

SELECT s.Storename

, g.RegionCountryName

FROM DimGeography g

JOIN DimStore s

ON s.GeographyKey=g.GeographyKey

When we run this query, we see that the optimization level is now Full, meaning that the optimizer realized there was some advantage to taking a closer look at the query.

Non-trivial XML Plan

We also notice that the “StatementOptmEarlyAbortReason” attribute in the XML plan has been set to “GoodEnoughPlanFound”. This is exactly what it sounds like, and the same information can be read from the properties window.

Query1 SELECT Properties

Microsoft is not about to reveal any of the trade secrets about the inner workings of the optimizer, but we can get a better feel for what is happening by using an undocumented query trace, 8675. To see information in the SSMS messages tab from this trace we must first turn on trace 3604 using DBCC.

When we run this first query we basically get a bunch of nothing, as might be expected for a “trivial” query.

DBCC TRACEON(3604)

— Test Query 0

SELECT * FROM DimGeography g

OPTION (RECOMPILE, QUERYTRACEON 8675);

End of simplification, time: 0 net: 0 total: 0 net: 0

End of post optimization rewrite, time: 0 net: 0 total: 0 net: 0

End of query plan compilation, time: 0 net: 0 total: 0 net: 0

(The RECOMPILE option is there simply because the optimizer won’t do anything at all if the plan is already cached in RAM.)

Let’s try the same experiment with the next query.

— Test Query 1

SELECT s.Storename

, g.RegionCountryName

FROM DimGeography g

JOIN DimStore s

ON s.GeographyKey=g.GeographyKey

OPTION (RECOMPILE, QUERYTRACEON 8675);

Now we see a more interesting report.

End of simplification, time: 0.001 net: 0.001 total: 0.001 net: 0.001

end exploration, tasks: 55 no total cost time: 0 net: 0 total: 0.001 net: 0.001

end exploration, tasks: 56 no total cost time: 0 net: 0 total: 0.001 net: 0.001

end exploration, tasks: 128 no total cost time: 0 net: 0 total: 0.002 net: 0.002

end exploration, tasks: 129 no total cost time: 0 net: 0 total: 0.002 net: 0.002

end search(1), cost: 0.0854515 tasks: 129 time: 0.015 net: 0 total: 0.018 net: 0.003

End of post optimization rewrite, time: 0.001 net: 0 total: 0.019 net: 0.003

End of query plan compilation, time: 0.001 net: 0 total: 0.021 net: 0.003

The optimizer did some “exploration” after the query simplification and decided that the cose of 0.0854515 (as seen in the line beginning “end search(1)”) was good enough . This is the same cost that is shown in the graphical query plan for this query. Now let’s increase the complexity of the query a bit more.

— Test Query 2

SELECT COUNT(*)

, p.ProductName

, g.RegionCountryName

FROM DimGeography g

JOIN DimStore s

ON s.GeographyKey=g.GeographyKey

JOIN FactSales fs

ON fs.StoreKey=s.StoreKey

JOIN DimProduct p

ON p.ProductKey= fs.ProductKey

GROUP BY p.ProductName, g.RegionCountryName

OPTION (RECOMPILE, QUERYTRACEON 8675);

The 8675 trace report now shows another pass.

End of simplification, time: 0.001 net: 0.001 total: 0.001 net: 0.001

end exploration, tasks: 173 no total cost time: 0.002 net: 0.002 total: 0.003 net: 0.003

end exploration, tasks: 174 no total cost time: 0.001 net: 0.001 total: 0.004 net: 0.004

end exploration, tasks: 405 no total cost time: 0.001 net: 0.001 total: 0.006 net: 0.006

end exploration, tasks: 406 no total cost time: 0 net: 0 total: 0.006 net: 0.006

end search(0), cost: 109.785 tasks: 406 time: 0 net: 0 total: 0.006 net: 0.006

end exploration, tasks: 1064 Cost = 109.785 time: 0.004 net: 0.004 total: 0.01 net: 0.01

end exploration, tasks: 1065 Cost = 109.785 time: 0 net: 0 total: 0.01 net: 0.01

end exploration, tasks: 1794 Cost = 109.785 time: 0.003 net: 0.003 total: 0.014 net: 0.014

end exploration, tasks: 1795 Cost = 109.785 time: 0 net: 0 total: 0.014 net: 0.014

end search(1), cost: 72.3044 tasks: 1795 time: 0 net: 0 total: 0.014 net: 0.014

end exploration, tasks: 1796 Cost = 72.3044 time: 0 net: 0 total: 0.014 net: 0.014

end exploration, tasks: 1797 Cost = 72.3044 time: 0 net: 0 total: 0.014 net: 0.014

end exploration, tasks: 3316 Cost = 72.3044 time: 0.011 net: 0.011 total: 0.025 net: 0.025

end exploration, tasks: 3317 Cost = 72.3044 time: 0 net: 0 total: 0.025 net: 0.025

end search(1), cost: 69.1059 tasks: 3317 time: 0 net: 0 total: 0.025 net: 0.025

End of post optimization rewrite, time: 0 net: 0 total: 0.025 net: 0.025

End of query plan compilation, time: 0.001 net: 0.001 total: 0.027 net: 0.027

At the end of search(0) the calculated cost of 109.785 was deemed worthy of optimization, and the cost at the end of the next pass, search(1) shows a cost of 72.3044, still not quite good enough so the optimizer crunched a bit more until the cost was brought down to 69.1059. As before, this is the cost that would be observed in the graphical query plan.

Query Optimizer Time Out

For some queries, the “ReasonForEarlyTerminationOfStatementOptimization” may read “Time Out”. Continuing to search for a better plan is viewed by the optimizer as costing too much time. Such queries may be amenable to hand-crafted optimization. Perhaps the query can be written more simply or written to use a different technique. For example, some legacy aggregate queries may benefit from being re-crafted to make use of the windowing functions. There is also a good chance that such queries may benefit from a variety of possible query hints. A query hint reduces the freedom of the optimizer in choosing plans, so not only does the process take less time, but the optimizer is looking at the possibilities not already flagged by you as appropriate, thereby increasing the likelihood of coming up with a superior plan.

Query Optimizer Memory Limit Exceeded

A last possibility, one that should be rare in a healthy system, is the optimizer cannot obtain the necessary memory for its work. In this case, the query itself must be dissected to find any options for a cleaner query expression. Large cumbersome queries should be refactored anyway just as a part of good practice. Helping the optimizer in such cases is just an added plus.

 Conclusion

It’s interesting to take a closer look at the steps being taken by the query optimizer. When the query plan reveals termination due to a timeout, a opportunity is revealed to refactor the query and achieve better performance.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.