This is my second post discussing some query optimizer undocumented trace flags (you can read the first one, “Inside the Query Optimizer Memo Structure”, here). Although is not the purpose of this post to discuss the entire optimization process or go into detail on any of the optimization phases, you can read more about it in my book Inside the SQL Server Query Optimizer, which you can download for free from the simple-talk website. And same as before, please bear in mind that all these trace flags are undocumented and unsupported, and should not be used on a production environment. You can use them as a way to explore and understand how the query optimizer works.

As mentioned in my previous post, you will first have to enable the trace flag 3604 to redirect the trace output to the client executing the command, in this case to the Messages tab in SQL Server Management Studio.

DBCC TRACEON(3604)

I’ll start with three trace flags which display logical and physical trees used during the optimization process. First, trace flag 8605 will display the query initial tree representation created by SQL Server. Test if by running

SELECT e.EmployeeID FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS s ON e.EmployeeID = s.SalesPersonID
OPTION (RECOMPILE, QUERYTRACEON 8605)

It will show the following output

*** Converted Tree: ***
    LogOp_Project QCOL: [e].EmployeeID
        LogOp_Join
            LogOp_Get TBL: HumanResources.Employee(alias TBL: e) HumanResources.Employee TableID=901578250 TableReferenceID=0 IsRow: COL: IsBaseRow1001
            LogOp_Get TBL: Sales.SalesPerson(alias TBL: s) Sales.SalesPerson TableID=1042102753 TableReferenceID=0 IsRow: COL: IsBaseRow1003
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier QCOL: [e].EmployeeID
                ScaOp_Identifier QCOL: [s].SalesPersonID
        AncOp_PrjList 

Trace flag 8606 will display additional logical trees used during the optimization process. Run

SELECT e.EmployeeID FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS s ON e.EmployeeID = s.SalesPersonID
OPTION (RECOMPILE, QUERYTRACEON 8606)

The output shows several different logical trees: input tree, simplified tree, join-collapsed tree, tree before project normalization, and tree after project normalization. These trees will include logical operators only. Part of the output is shown next.

*** Input Tree: ***
        LogOp_Project QCOL: [e].EmployeeID
            LogOp_Select
                LogOp_Join
                    LogOp_Get TBL: HumanResources.Employee(alias TBL: e) HumanResources.Employee TableID=901578250 TableReferenceID=0 IsRow: COL: IsBaseRow1001
                    LogOp_Get TBL: Sales.SalesPerson(alias TBL: s) Sales.SalesPerson TableID=1042102753 TableReferenceID=0 IsRow: COL: IsBaseRow1003
                    ScaOp_Const TI(bit,ML=1) XVAR(bit,Not Owned,Value=1)
                ScaOp_Comp x_cmpEq
                    ScaOp_Identifier QCOL: [e].EmployeeID
                    ScaOp_Identifier QCOL: [s].SalesPersonID
            AncOp_PrjList
*******************

*** Simplified Tree: ***
        LogOp_Join
            LogOp_Get TBL: HumanResources.Employee(alias TBL: e) HumanResources.Employee TableID=901578250 TableReferenceID=0 IsRow: COL: IsBaseRow1001
            LogOp_Get TBL: Sales.SalesPerson(alias TBL: s) Sales.SalesPerson TableID=1042102753 TableReferenceID=0 IsRow: COL: IsBaseRow1003
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier QCOL: [s].SalesPersonID
                ScaOp_Identifier QCOL: [e].EmployeeID
*******************
*** Join-collapsed Tree: ***
        LogOp_Join
            LogOp_Get TBL: HumanResources.Employee(alias TBL: e) HumanResources.Employee TableID=901578250 TableReferenceID=0 IsRow: COL: IsBaseRow1001
            LogOp_Get TBL: Sales.SalesPerson(alias TBL: s) Sales.SalesPerson TableID=1042102753 TableReferenceID=0 IsRow: COL: IsBaseRow1003
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier QCOL: [s].SalesPersonID
                ScaOp_Identifier QCOL: [e].EmployeeID
*******************
*** Tree Before Project Normalization ***
        LogOp_Join
            LogOp_Get TBL: HumanResources.Employee(alias TBL: e) HumanResources.Employee TableID=901578250 TableReferenceID=0 IsRow: COL: IsBaseRow1001
            LogOp_Get TBL: Sales.SalesPerson(alias TBL: s) Sales.SalesPerson TableID=1042102753 TableReferenceID=0 IsRow: COL: IsBaseRow1003
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier QCOL: [s].SalesPersonID
                ScaOp_Identifier QCOL: [e].EmployeeID
*****************************************
*** Tree After Project Normalization ***
        LogOp_Join
            LogOp_Get TBL: HumanResources.Employee(alias TBL: e) HumanResources.Employee TableID=901578250 TableReferenceID=0 IsRow: COL: IsBaseRow1001
            LogOp_Get TBL: Sales.SalesPerson(alias TBL: s) Sales.SalesPerson TableID=1042102753 TableReferenceID=0 IsRow: COL: IsBaseRow1003
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier QCOL: [s].SalesPersonID
                ScaOp_Identifier QCOL: [e].EmployeeID
****************************************
*** Stop search, level 1 ***

One interesting example is seeing how a tree is simplified when the query optimizer can detect a contradiction during the simplification phase. The purpose of the simplification stage is to reduce the query tree into a simpler form in order to make the optimization process easier. Contradiction detection is one of several possible simplifications. Following on an example on my book and this blog post, run the following query

SELECT * FROM HumanResources.Employee
WHERE VacationHours > 300
OPTION (RECOMPILE, QUERYTRACEON 8606)

Part of the output is next

*** Input Tree: ***
        LogOp_Project QCOL: [AdventureWorks].[HumanResources].[Employee].EmployeeID QCOL: [AdventureWorks].[HumanResources].[Employee].NationalIDNumber QCOL: [AdventureWorks].[HumanResources].[Employee].ContactID QCOL: [AdventureWorks].[HumanResources].[Employee].LoginID QCOL: [AdventureWorks].[HumanResources].[Employee].ManagerID QCOL: [AdventureWorks].[HumanResources].[Employee].Title QCOL: [AdventureWorks].[HumanResources].[Employee].BirthDate QCOL: [AdventureWorks].[HumanResources].[Employee].MaritalStatus QCOL: [AdventureWorks].[HumanResources].[Employee].Gender QCOL: [AdventureWorks].[HumanResources].[Employee].HireDate QCOL: [AdventureWorks].[HumanResources].[Employee].SalariedFlag QCOL: [AdventureWorks].[HumanResources].[Employee].VacationHours QCOL: [AdventureWorks].[HumanResources].[Employee].SickLeaveHours QCOL: [AdventureWorks].[HumanResources].[Employee].CurrentFlag QCOL: [AdventureWorks].[HumanResources].[Employee].rowguid QCOL: [AdventureWorks].[HumanResources].[Employee].ModifiedDate
            LogOp_Select
                LogOp_Get TBL: HumanResources.Employee HumanResources.Employee TableID=901578250 TableReferenceID=0 IsRow: COL: IsBaseRow1001
                ScaOp_Comp x_cmpGt
                    ScaOp_Identifier QCOL: [AdventureWorks].[HumanResources].[Employee].VacationHours
                    ScaOp_Const TI(smallint,ML=2) XVAR(smallint,Not Owned,Value=300)
            AncOp_PrjList
*******************
*** Simplified Tree: ***
        LogOp_ConstTableGet (0) COL: Chk1000  COL: IsBaseRow1001  QCOL: [AdventureWorks].[HumanResources].[Employee].EmployeeID QCOL: [AdventureWorks].[HumanResources].[Employee].NationalIDNumber QCOL: [AdventureWorks].[HumanResources].[Employee].ContactID QCOL: [AdventureWorks].[HumanResources].[Employee].LoginID QCOL: [AdventureWorks].[HumanResources].[Employee].ManagerID QCOL: [AdventureWorks].[HumanResources].[Employee].Title QCOL: [AdventureWorks].[HumanResources].[Employee].BirthDate QCOL: [AdventureWorks].[HumanResources].[Employee].MaritalStatus QCOL: [AdventureWorks].[HumanResources].[Employee].Gender QCOL: [AdventureWorks].[HumanResources].[Employee].HireDate QCOL: [AdventureWorks].[HumanResources].[Employee].SalariedFlag QCOL: [AdventureWorks].[HumanResources].[Employee].VacationHours QCOL: [AdventureWorks].[HumanResources].[Employee].SickLeaveHours QCOL: [AdventureWorks].[HumanResources].[Employee].CurrentFlag QCOL: [AdventureWorks].[HumanResources].[Employee].rowguid QCOL: [AdventureWorks].[HumanResources].[Employee].ModifiedDate
*******************
*** Join-collapsed Tree: ***
        LogOp_ConstTableGet (0) COL: Chk1000  COL: IsBaseRow1001  QCOL: [AdventureWorks].[HumanResources].[Employee].EmployeeID QCOL: [AdventureWorks].[HumanResources].[Employee].NationalIDNumber QCOL: [AdventureWorks].[HumanResources].[Employee].ContactID QCOL: [AdventureWorks].[HumanResources].[Employee].LoginID QCOL: [AdventureWorks].[HumanResources].[Employee].ManagerID QCOL: [AdventureWorks].[HumanResources].[Employee].Title QCOL: [AdventureWorks].[HumanResources].[Employee].BirthDate QCOL: [AdventureWorks].[HumanResources].[Employee].MaritalStatus QCOL: [AdventureWorks].[HumanResources].[Employee].Gender QCOL: [AdventureWorks].[HumanResources].[Employee].HireDate QCOL: [AdventureWorks].[HumanResources].[Employee].SalariedFlag QCOL: [AdventureWorks].[HumanResources].[Employee].VacationHours QCOL: [AdventureWorks].[HumanResources].[Employee].SickLeaveHours QCOL: [AdventureWorks].[HumanResources].[Employee].CurrentFlag QCOL: [AdventureWorks].[HumanResources].[Employee].rowguid QCOL: [AdventureWorks].[HumanResources].[Employee].ModifiedDate
*******************
*** Tree Before Project Normalization ***
        LogOp_ConstTableGet (0) COL: Chk1000  COL: IsBaseRow1001  QCOL: [AdventureWorks].[HumanResources].[Employee].EmployeeID QCOL: [AdventureWorks].[HumanResources].[Employee].NationalIDNumber QCOL: [AdventureWorks].[HumanResources].[Employee].ContactID QCOL: [AdventureWorks].[HumanResources].[Employee].LoginID QCOL: [AdventureWorks].[HumanResources].[Employee].ManagerID QCOL: [AdventureWorks].[HumanResources].[Employee].Title QCOL: [AdventureWorks].[HumanResources].[Employee].BirthDate QCOL: [AdventureWorks].[HumanResources].[Employee].MaritalStatus QCOL: [AdventureWorks].[HumanResources].[Employee].Gender QCOL: [AdventureWorks].[HumanResources].[Employee].HireDate QCOL: [AdventureWorks].[HumanResources].[Employee].SalariedFlag QCOL: [AdventureWorks].[HumanResources].[Employee].VacationHours QCOL: [AdventureWorks].[HumanResources].[Employee].SickLeaveHours QCOL: [AdventureWorks].[HumanResources].[Employee].CurrentFlag QCOL: [AdventureWorks].[HumanResources].[Employee].rowguid QCOL: [AdventureWorks].[HumanResources].[Employee].ModifiedDate
*****************************************
*** Tree After Project Normalization ***
        LogOp_ConstTableGet (0) COL: Chk1000  COL: IsBaseRow1001  QCOL: [AdventureWorks].[HumanResources].[Employee].EmployeeID QCOL: [AdventureWorks].[HumanResources].[Employee].NationalIDNumber QCOL: [AdventureWorks].[HumanResources].[Employee].ContactID QCOL: [AdventureWorks].[HumanResources].[Employee].LoginID QCOL: [AdventureWorks].[HumanResources].[Employee].ManagerID QCOL: [AdventureWorks].[HumanResources].[Employee].Title QCOL: [AdventureWorks].[HumanResources].[Employee].BirthDate QCOL: [AdventureWorks].[HumanResources].[Employee].MaritalStatus QCOL: [AdventureWorks].[HumanResources].[Employee].Gender QCOL: [AdventureWorks].[HumanResources].[Employee].HireDate QCOL: [AdventureWorks].[HumanResources].[Employee].SalariedFlag QCOL: [AdventureWorks].[HumanResources].[Employee].VacationHours QCOL: [AdventureWorks].[HumanResources].[Employee].SickLeaveHours QCOL: [AdventureWorks].[HumanResources].[Employee].CurrentFlag QCOL: [AdventureWorks].[HumanResources].[Employee].rowguid QCOL: [AdventureWorks].[HumanResources].[Employee].ModifiedDate

In this case the query optimizer makes use of an existing check constraint to conclude that no records qualify for the predicate VacationHours > 300, replacing the entire tree with a LogOp_ConstTableGet logical operator. If you try displaying an output tree, as discussed next, you will get a PhyOp_ConstTableScan physical operator, and you will get a constant scan operator on the final execution plan. You can see a different behavior if you try the same query with a predicate like VacationHours > 20.

Trace flag 8607 shows the optimization output tree. Try the following sentence

SELECT e.EmployeeID FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS s ON e.EmployeeID = s.SalesPersonID
OPTION (RECOMPILE, QUERYTRACEON 8607)

Notice that this time we have physical operators and the output tree is closer to the final execution plan.

****************************************
*** Output Tree: ***
        PhyOp_Apply lookup TBL: HumanResources.Employee (0) (x_jtInner)
            PhyOp_Range TBL: Sales.SalesPerson(alias TBL: s)(2) ASC  Bmk ( QCOL: [s].SalesPersonID) IsRow: COL: IsBaseRow1003
            PhyOp_Range TBL: HumanResources.Employee(alias TBL: e)(1) ASC  Bmk ( QCOL: [e].EmployeeID) IsRow: COL: IsBaseRow1001
                ScaOp_Comp x_cmpEq
                    ScaOp_Identifier QCOL: [s].SalesPersonID
                    ScaOp_Identifier QCOL: [e].EmployeeID
********************
** Query marked as Cachable
********************

Trace flag 8675 shows the query optimization phases for a specific optimization along with some other information like cost estimation, tasks, etc. You may want to test it with complex queries to see different optimization phases like in the following example

SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City,
SP.Name AS State, CR.Name AS CountryRegion
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
JOIN Person.StateProvince SP ON
SP.StateProvinceID = A.StateProvinceID
JOIN Person.CountryRegion CR ON
CR.CountryRegionCode = SP.CountryRegionCode
ORDER BY I.CustomerID
OPTION (RECOMPILE, QUERYTRACEON 8675)

It shows the following output

End of simplification, time: 0.003 net: 0.003 total: 0.003 net: 0.003
end exploration, tasks: 275 no total cost time: 0.005 net: 0.005 total: 0.009 net: 0.009
end exploration, tasks: 642 no total cost time: 0.003 net: 0.003 total: 0.012 net: 0.012
end search(0),  cost: 9.04 tasks: 681 time: 0 net: 0 total: 0.013 net: 0.013
end exploration, tasks: 1465 Cost = 9.04 time: 0.004 net: 0.004 total: 0.017 net: 0.017
end exploration, tasks: 2518 Cost = 9.04 time: 0.007 net: 0.007 total: 0.024 net: 0.024
end search(1),  cost: 5.86879 tasks: 2609 time: 0 net: 0 total: 0.025 net: 0.025
end exploration, tasks: 2610 Cost = 5.86879 time: 0 net: 0 total: 0.025 net: 0.025
end exploration, tasks: 5170 Cost = 5.86879 time: 0.021 net: 0.021 total: 0.047 net: 0.047
end search(1),  cost: 5.86248 tasks: 5469 time: 0.001 net: 0.001 total: 0.048 net: 0.048
end exploration, tasks: 5756 Cost = 5.86248 time: 0.001 net: 0.001 total: 0.05 net: 0.05
end exploration, tasks: 6434 Cost = 5.86248 time: 0.005 net: 0.005 total: 0.055 net: 0.055
end search(2),  cost: 5.84575 tasks: 7092 time: 0.004 net: 0.004 total: 0.059 net: 0.059
End of post optimization rewrite, time: 0 net: 0 total: 0.059 net: 0.059
End of query plan compilation, time: 0 net: 0 total: 0.06 net: 0.06

Note the optimization phases search(0), search(1) and search(2). Also, as mentioned in my book, you need at least three tables to qualify for search 0, so the following query will go directly to search 1 (you can also test this with trace flag 2372 as described later):

SELECT * FROM HumanResources.Employee
WHERE ManagerID = 12
OPTION (RECOMPILE, QUERYTRACEON 8675)

Trace flags 2372 and 2373 show memory utilization during the optimization process but we can also use them to gain information about the activities the query optimizer is performing for a particular query. Trace flag 2372 shows memory utilization during the different optimization stages. Same as before, more complicated queries will show more optimization stages.

SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City,
SP.Name AS State, CR.Name AS CountryRegion
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
JOIN Person.StateProvince SP ON
SP.StateProvinceID = A.StateProvinceID
JOIN Person.CountryRegion CR ON
CR.CountryRegionCode = SP.CountryRegionCode
ORDER BY I.CustomerID
OPTION (RECOMPILE, QUERYTRACEON 2372)

The output is next

Memory before NNFConvert: 13
Memory after NNFConvert: 14
Memory before project removal: 15
Memory after project removal: 15
Memory before simplification: 15
Memory after simplification: 35
Memory before heuristic join reordering: 35
Memory after heuristic join reordering: 46
Memory before project normalization: 46
Memory after project normalization: 46
Memory before stage TP: 46
Memory after stage TP: 81
Memory before stage QuickPlan: 81
Memory after stage QuickPlan: 144
Memory before stage Full: 144
Memory after stage Full: 156
Memory before copy out: 156
Memory after copy out: 157

Notice that “stage TP” or transaction processing phase is the same as search(0) shown before with trace flag 8675. In the same way, “stage QuickPlan” is the search(1) and “stage Full” is search(2).

Trace flag 2373 shows memory utilization while applying optimization rules and deriving properties.

SELECT e.EmployeeID FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS s ON e.EmployeeID = s.SalesPersonID
OPTION (RECOMPILE, QUERYTRACEON 2373)

Part of the output is next

Memory before rule IJtoIJSEL: 14
Memory after rule IJtoIJSEL: 14
Memory before rule MatchGet: 14
Memory after rule MatchGet: 14
Memory before rule MatchGet: 14
Memory after rule MatchGet: 14
Memory before rule JoinToIndexOnTheFly: 14
Memory after rule JoinToIndexOnTheFly: 14
Memory before rule JoinCommute: 14
Memory after rule JoinCommute: 14
Memory before rule JoinToIndexOnTheFly: 14
Memory after rule JoinToIndexOnTheFly: 14
Memory before rule JNtoIdxLookup: 14

Finally, trace 8757 can be used to skip the trivial plan optimization and basically force a full optimization. As a reminder, the trivial plan optimization is used for very simple queries that don’t require any cost estimation decision. For example, the following query will produce a trivial plan, which you can verify by looking at the optimization level or StatementOptmLevel property in your execution plan

SELECT * FROM dbo.DatabaseLog

By applying trace flag 8757, the trivial plan optimization will be skipped as you can verify by running the following query and once again verifying the optimization level property.

SELECT * FROM dbo.DatabaseLog
OPTION (QUERYTRACEON 8757)

But first, a quick reminder of what the memo structure is. The memo is a search data structure that is used to store the alternatives generated and analyzed by the SQL Server query optimizer. These alternatives can be logical or physical operators and are organized into groups such that each alternative in the same group produces the same results. The query optimizer first copies the original query tree’s logical expressions into the memo structure, placing each operator from the query tree in its own group, and then triggers the entire optimization process. During this process, transformation rules are applied to generate all the alternatives, starting with these initial logical expressions. As the transformation rules produce new alternatives, these are added to their equivalent groups. Transformation rules may also produce a new expression which is not equivalent to any existing group, and which causes a new group to be created. A new memo structure is created for each optimization.

But instead of trying to explain the basics of how the memo structure works you can refer to my book, Inside the SQL Server Query Optimizer, which you can download for free from the simple-talk website. In this post I will show you how to see the contents of the memo structure. First enable the trace flag 3604 to redirect the trace output to the client executing the command, in this case SQL Server Management Studio.

DBCC TRACEON(3604)

Next I will be using the undocumented trace flags 8608 and 8615 but, although I can still use DBCC TRACEON, this time I will use the also undocumented QUERYTRACEON query hint. Please bear in mind that all these are undocumented SQL Server statements and should not be used on a production environment.

The first trace flag, 8608, will show the initial memo structure, which you can see in the Messages tab of the Query window in Management Studio.

SELECT e.EmployeeID FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS s ON e.EmployeeID = s.SalesPersonID
OPTION (QUERYTRACEON 8608)

Running the previous query will show the following output

--- Initial Memo Structure ---
Root Group 5: Card=8.33333 (Max=10000, Min=0)
   0 LogOp_Join 0 1 4
Group 4:
   0 ScaOp_Comp  2 3
Group 3:
   0 ScaOp_Identifier
Group 2:
   0 ScaOp_Identifier
Group 1: Card=17 (Max=10000, Min=0)
   0 LogOp_Get
Group 0: Card=290 (Max=10000, Min=0)
   0 LogOp_Get

The next query uses trace flag 8615 to display the final memo structure

SELECT e.EmployeeID FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS s ON e.EmployeeID = s.SalesPersonID
OPTION (QUERYTRACEON 8615)

which shows the following output

--- Final Memo Structure ---
Group 9: Card=1 (Max=1, Min=0)
   0 LogOp_SelectIdx 8 4
Group 8: Card=17 (Max=10000, Min=0)
   0 LogOp_GetIdx
Group 7: Card=1 (Max=1, Min=0)
   1 PhyOp_Range 1 ASC 4.0 Cost(RowGoal 0,ReW 0,ReB 16,Dist 17,Total 17)= 0.0058127
   0 LogOp_SelectIdx 6 4
Group 6: Card=290 (Max=10000, Min=0)
   0 LogOp_GetIdx
Root Group 5: Card=8.33333 (Max=10000, Min=0)
   2 PhyOp_Applyx_jtInner 1.2 7.1 Cost(RowGoal 0,ReW 0,ReB 0,Dist 0, …)= 0.00918446
   1 LogOp_Join 1 0 4
   0 LogOp_Join 0 1 4
Group 4:
   0 ScaOp_Comp  2.0 3.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 3
Group 3:
   0 ScaOp_Identifier   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 1
Group 2:
   0 ScaOp_Identifier   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 1
Group 1: Card=17 (Max=10000, Min=0)
   3 PhyOp_Range 1 ASC   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0033007
   2 PhyOp_Range 2 ASC   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0033007
   0 LogOp_Get
Group 0: Card=290 (Max=10000, Min=0)
   0 LogOp_Get

You can notice that among other things the output shows information about operators, groups, cardinality information (Card) and cost estimation. I will be discussing a few more undocumented trace flags on a second post very soon.

I can’t believe I haven’t posted anything in my blog in months. Just after getting back from the PASS Summit 2011 I got involved in several SQL Server projects and I’ve been really busy since then. By the way, I just recently saw my first article published in SQL Server Pro (formerly SQL Server Magazine). The article, “Improve the Performance of Data Warehouse Queries with Columnstore Indexes”, can also be seen online here at the SQL Server Pro website. Also, just a few days ago I was selected as a 2012 Idera ACE so I am really excited to be part of this team. In addition, I will be speaking at some SQL Server events this month.

First, I will be presenting a session at 24 Hours of PASS, which is a free event with 24 consecutive SQL Server webcasts and it is scheduled for March 21st. My session, Improving the Performance of your Data Warehouse Queries with Columnstore Indexes will be the last session of the event and it is scheduled for 4:00pm Pacific Time. This edition of 24 Hours of PASS will feature close captioning in 15 different languages and you can still register here.

Three days after 24 Hours of PASS I will be presenting four sessions at our local SQLSaturday #120 in Orange County, CA. SQLSaturday #120 will be held at the Goldenwest College in Huntington Beach on March 24th and will include many great speakers like Grant Fritchey, Kendra Little, Jeremiah Peschka, Denny Cherry, Lynn Langit, Audrey Hammonds, and Carlos Bossy, just to name a few.

Just after SQLSaturday #120 I will be headed to Las Vegas, NV to participate on my first SQL Server Connections conference. The Spring 2012 edition of SQL Server Connections will run from March 26th to 29th and will host one of the SQL Server 2012 launch events. I will be presenting my session Top 10 Query Optimizer Topics for Better Performance on Thursday afternoon.

As always, I look forward to meeting lots of SQL Server professionals on these events.

This is my late review of the PASS Summit 2011, which was hosted once again at the Washington State Convention & Trade Center in Seattle a couple of weeks ago. The PASS Summit is the largest SQL Server event in the world and this year was scheduled to run 189 sessions with 204 speakers from all over the world. In addition to being my ninth year attending this conference I was also excited that this was my fourth year speaking there as well.

So the week started for me on Monday morning flying from Los Angeles to Seattle. Later in the afternoon I went to the registration area where I started meeting a lot of people of the SQL Server community just to end the day with some SQL Karaoke at the Bush Garden. I was originally scheduled to attend Dr. David DeWitt’s pre-con “A Peek Inside an RDBMS” on Tuesday but unfortunately it was cancelled and I didn’t attend any other pre-con that day. The Welcome Reception was held on Tuesday night and included the traditional Quiz Bowl, which I show in the next picture. From the Welcome Reception some of us went to the Speakers and Volunteers Party at hosted The Garage.

On Wednesday, the first keynote of the conference started with Rushabh Mehta, PASS President, and was followed by Ted Kummert, Microsoft Senior Vice President, Business Platform Division. Not a big surprise but Ted made it official that SQL Server code-named Denali will now be named SQL Server 2012 and will be released on the first half of the next calendar year. Among other things, Ted talked about Big Data and announced that Microsoft will be supporting Hadoop and it is planning to deliver Apache Hadoop-based distributions for both Windows Server and Windows Azure. He also mentioned that SQL Server and SQL Server Parallel Data Warehouse connectors for Apache Hadoop had being released just the previous week. By the way, all three keynotes of the conference were broadcasted live and you can still watch them on demand at the PASS website.

I had to leave the keynote early to find the room and prepare everything for my first session at the conference. My session, “Inside the SQL Server Query Optimizer”, was scheduled for a room with capacity for 520 people and next is a picture taken just a few minutes before I started presenting. I was even asked to sign a copy of my book just before my session

The rest of Wednesday after lunch I went to a couple of sessions. With 15 sessions running concurrently choosing which ones to attend was not an easy task. I attended Bob Ward’s half-day session “Inside Tempdb” and later went to Aaron Bertrand’s “What’s new in SQL Server code-named Denali – Engine and Tools”. Half-day sessions were new at the PASS Summit this year. On Wednesday night I went to the SolidQ Party, which I’ve also attended in previous years.

I was not presenting any session on Thursday so I just went to the keynote and spent the rest of the day attending sessions. The keynote included Bill Graziano, PASS Executive Vice President, Finance, and Quentin Clark, Microsoft Corporate Vice President. Quentin started talking about SQL Server 2012 and warned that would be impossible to talk about all the hundreds of new features and improvements of this new version so instead he went into explaining his 12 favorite areas of value, which he called the “Fantastic 12 of SQL Server 2012”. He spent the rest of the keynote talking about the most important new SQL Server 2012 features grouped into these 12 areas of value. After the keynote, my choice for session in the morning was Itzik Ben-Gan “Bug or Feature?”.

During lunch I was on the same table as Eric Hanson, Principal Program Manager Lead, Query Processing and Storage at Microsoft, so I used the opportunity to talk to him about the new columnstore indexes feature. After lunch I went to the session “Physical Join Operators” by Ami Levin, which was very entertaining. I continued with Rob Farley “Joins, Sargability and the Evils of Residualiciousness” to finally close the day with “SQLCAT: SQL Server HA and DR Design Architectures and Best Practices” with Sanjay Mishra, Justin Erickson and Mike Weiner.

I spent Thursday night mostly at the Community Appreciation Party, hosted again this year at GameWorks. I met many people there and ended in the same table with Lubor Kollar who mentioned that he is back to work with the SQL Server Core Engine development team, responsible for query optimization, query execution, and data warehousing.

The keynote on Friday started with Rick Heighes, PASS Vice President, Marketing, followed by David DeWitt, Microsoft Technical Fellow, Data and Storage Platform Division. Dr. DeWitt’s keynote, entitled "Big Data: What’s the Big Deal?", was one of the most anticipated sessions of the conference and focused on Hadoop and its ecosystem of software tools. He concluded his keynote saying that relational databases and Hadoop are designed to meet different needs and they can complement each other so database professionals need to make sure that both technologies work together the best they can. After the keynote I attended Adam Machanic “Query Tuning Mastery: Zend and the Art of Workspace Memory”.

Something amazing for me during the conference was seeing my book Inside the SQL Server Query Optimizer available at the PASS Bookstore. Next I am including a picture taken on Friday when there were only a few copies left -It is the one with the beacon in the cover. I also saw my book at the Red Gate booth and, of course, I gave away a couple of copies at each of my sessions.

Just after lunch on Friday I went to see Susan Price and Murshed Zaman presenting “Project Apollo: How to use Columnstore Indexes to Revolutionize Query Performance on your Data Warehouse”. Susan presented a similar session last year but I was not able to attend it as I was also presenting a session at the same time. I stayed in the same room to learn more about the columnstore indexes with Wayne Snyder’s session “Using Columnstore/Vertipaq indexes in SQL Server code-named Denali” but again I had to leave early as I was speaking next.

Then it was time for presenting my last session, “Parameter Sniffing: the Query Optimizer vs. the Plan Cache”. Since this was the last round of sessions at the conference I was wondering, same as other speakers I met at the Speaker Ready room, if nobody or very few people would show up. Fortunately the attendance for my session was good enough and the presentation went really well. Finally I spend Friday night with dinner at the Tap House with several people of the SQL Server community.

Something interesting I saw on Saturday afternoon while going for lunch to the Hard Rock Cafe was the people of the Occupy Seattle movement on the streets of Seattle. I took several pictures there, one is shown next.

I flew back to Los Angeles on Saturday night and interestingly enough I was scheduled to speak again at the SoCal Code Camp at the University of Southern California the next day, where I presented the same two sessions I did at the PASS Summit.

In summary, this was another excellent PASS Summit and I can’t wait for the next one, which is already scheduled for Seattle in November 6-9, 2012. See you then.

But first, a little bit of background. Remember that in general query optimization is an expensive operation and, in order to avoid this optimization cost, the plan cache will try to keep the generated execution plans in memory so they can be reused. So, if the stored procedure is executed thousands of times, only one optimization is needed. However, if a new connection running the same stored procedure has different SET options it may generate a new plan instead of reusing the one already on the plan cache. This new plan can be reused by later executions of the same stored procedure with the same connection settings. A new plan is needed as these SET options can impact the choice of an execution plan because they affect the results of evaluating constant expressions during the optimization process (a process known as constant folding and explained here). Another connection setting, FORCEPLAN, acts in a similar way to a hint, requesting the Query Optimizer both to preserve the join order as specified on the query syntax and to use nested loop joins only. As indicated in the Microsoft white paper Plan Caching in SQL Server 2008, the following SET options will affect the reuse of execution plans.

ANSI_NULL_DFLT_OFF
ANSI_NULL_DFLT_ON
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
DATEFIRST
DATEFORMAT
FORCEPLAN
LANGUAGE
NO_BROWSETABLE
NUMERIC_ROUNDABORT
QUOTED_IDENTIFIER

Unfortunately different management or development tools, like Management Studio, ADO.NET, or even sqlcmd, may have different SET options in their default configuration. You will find that mostly the problem is that one of the options, ARITHABORT, is OFF in ADO.NET and ON in Management Studio. So it may be possible that, in our example, Management Studio and the web application are using distinct cached plans and that the web application initially got a good plan for the parameters used during the optimization, but this plan is not good for some other executions of the same stored procedure with different parameters.

But now let us see how to prove that parameter sniffing is in fact the problem for your specific instance of the issue, and how to extract the plans to inspect both the parameters and the SET options used during optimization. Since AdventureWorks does not have the default SET options of a new database, let us create our own and copy some data from AdventureWorks

CREATE DATABASE Test
GO

Create a new table and a stored procedure to test

USE Test
GO
SELECT * INTO dbo.SalesOrderDetail
FROM AdventureWorks.Sales.SalesOrderDetail
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderDetail_ProductID
ON dbo.SalesOrderDetail(ProductID)
GO
CREATE PROCEDURE test (@pid int)
AS
SELECT * FROM dbo.SalesOrderDetail
WHERE ProductID = @pid

Let us test two different applications, executing the stored procedure from a .NET application (C# code included at the end) and from Management Studio. For the purpose of this test we want to assume that a plan with a table scan is a bad plan and a plan using an index seek/RID lookup is the optimal one.

Start with a clean plan cache by running

DBCC FREEPROCCACHE

Run the .NET application from a command prompt window and provide the value 870 as a parameter (note that this application is only running the test stored procedure)

C:\TestApp\test
Enter ProductID: 870

At this moment we can start inspecting the plan cache to see the plans available in memory. Run the following script from the Test database (we will be running this script again later during this exercise)

SELECT plan_handle, usecounts, pvt.set_options
FROM (
    SELECT plan_handle, usecounts, epa.attribute, epa.value
    FROM sys.dm_exec_cached_plans
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
    WHERE cacheobjtype = 'Compiled Plan') AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "objectid")) AS pvt
where pvt.objectid = object_id('dbo.test')

You should get an output similar to this

plan_handle                                           usecounts    set_options
0x05000700210F0207B8C09007000000000000000000000000    1            251

The output shows that we have one execution plan in the plan cache, it has been used once (as indicated by the usecounts value), and the set_options value, taken from the sys.dm_exec_plan_attributes DMF, is 251. Since this was the first execution of the stored procedure, it was optimized using the parameter 870 which in this case created a plan using a table scan (consider here a “bad” plan). Now run the application again using a parameter that returns only a few records and will benefit from an index seek/RID lookup plan:

C:\TestApp\test
Enter ProductID: 898

If you inspect the plan cache again you will notice that the plan has been used twice and unfortunately this time it was not good for the second parameter used

plan_handle                                           usecounts    set_options
0x05000700210F0207B8C09007000000000000000000000000    2            251

At this moment the developer may try to troubleshoot this problem by running the stored procedure in Management Studio using something like this

EXEC test @pid = 898

Now the developer is surprised to find that SQL Server is returning a good execution plan and the query is returning immediately. Inspecting the plan cache again will show something similar to this

plan_handle                                           usecounts    set_options
0x05000700210F0207B8C09007000000000000000000000000    2            251
0x05000700210F0207B860650B000000000000000000000000    1            4347

You can see that a new plan was added for the Management Studio execution, with a different value for set_options.

What to do next? It is time to inspect the plans and look at the SET options and parameters used during the optimization. Select the plan_handle of the first plan created (the one with set_options 251 in your own example) and use it to run the following query

select * from sys.dm_exec_query_plan
(0x05000700210F0207B8C09007000000000000000000000000)

You can find the SET options at the beginning of the plan

<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false"
CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true"
ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

And the used parameters at the end

<ParameterList>
    <ColumnReference Column="@pid" ParameterCompiledValue="(870)" />
</ParameterList>

Do the same for the second plan and you will get the following information for the SET options

<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true"
ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

and the following parameter information

<ParameterList>
    <ColumnReference Column="@pid" ParameterCompiledValue="(898)" />
</ParameterList>

This information shows that the ARITHABORT SET option has different value on these plans and that the parameter used to optimize the query on the web application was 870. (The same information is available from the Properties window of a graphical plan). You can also verify the operators used in the plan, the first one using a table scan and the second one an index seek/RID lookup combination.

Now that you have captured the plans you can force a new optimization so the application can use a better plan immediately (keeping in mind that this is not a permanent solution). Try this

sp_recompile test

So now you know that you have a problem related to parameter sniffing. What to do next? I have a few recommendations on previous posts here and here. I have another one here but usually you should not be doing this.

Finally, you can use the following script to display SET options for a specific set_options value

declare @set_options int = 251
if ((1 & @set_options) = 1) print 'ANSI_PADDING'
if ((4 & @set_options) = 4) print 'FORCEPLAN'
if ((8 & @set_options) = 8) print 'CONCAT_NULL_YIELDS_NULL'
if ((16 & @set_options) = 16) print 'ANSI_WARNINGS'
if ((32 & @set_options) = 32) print 'ANSI_NULLS'
if ((64 & @set_options) = 64) print 'QUOTED_IDENTIFIER'
if ((128 & @set_options) = 128) print 'ANSI_NULL_DFLT_ON'
if ((256 & @set_options) = 256) print 'ANSI_NULL_DFLT_OFF'
if ((512 & @set_options) = 512) print 'NoBrowseTable'
if ((4096 & @set_options) = 4096) print 'ARITH_ABORT'
if ((8192 & @set_options) = 8192) print 'NUMERIC_ROUNDABORT'
if ((16384 & @set_options) = 16384) print 'DATEFIRST'
if ((32768 & @set_options) = 32768) print 'DATEFORMAT'
if ((65536 & @set_options) = 65536) print 'LanguageID'

C# Code

using System;
using System.Data;
using System.Data.SqlClient;

class Test
{
    static void Main()
    {
        SqlConnection cnn = null;
        SqlDataReader reader = null;

        try
        {
            Console.Write("Enter ProductID: ");
            string pid = Console.ReadLine();

            cnn = new SqlConnection("Data Source=(local);Initial Catalog=Test;
                Integrated Security=SSPI");
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cnn;
            cmd.CommandText = "dbo.test";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@pid", SqlDbType.Int).Value = pid;
            cnn.Open();
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader[0]);
            }
            return;
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            if (cnn != null)
            {
                if (cnn.State != ConnectionState.Closed)
                    cnn.Close();
            }
        }
    }
}

I am currently working on the two sessions that I will be presenting at the PASS Summit: Inside the SQL Server Query Optimizer and Parameter Sniffing: the Query Optimizer vs. the Plan Cache. In addition, I will be presenting these two new sessions in other SQL Server events in Southern California including SQLSatuday #95.

First, I will be speaking at the Los Angeles SQL Server Professionals Group on Thursday September 15th. The meeting will be hosted at the UCLA Anderson School of Management and will start at 6:30 PM. I will present only one session, Inside the SQL Server Query Optimizer, in this meeting. You can find additional information about the meeting and directions on their website.

Two days later, on September 17th, I will be speaking at SQLSaturday #95 in San Diego, CA. Of course, this SQLSaturday will also have many other great speakers and the final schedule is already posted here. In addition to presenting both of my sessions described before I will be participating in the Ask the Experts – SQL Server Q&A session coordinated by Thomas Mueller. For more details and directions for SQLSaturday #95 please go to their website here.

On October 7th I will be presenting my Query Optimizer session at the Orange County SQL Server Professionals User Group in Mission Viejo, CA. Details and directions will be posted soon on their website here.

Then it is time for the PASS Summit, the largest SQL Server and BI conference in the world. The PASS Summit is hosted again this year in Seattle, WA and it is scheduled for October 11-14. The schedule for my two sessions is not final at the moment of writing this but so far it looks like I will be speaking on Wednesday and Friday.

I am flying back from the PASS Summit on Saturday and planning to present my two sessions at the SoCal Code Camp the following day, Sunday October 16th. The SoCal Code Camp is a community driven event for developers to come and learn from their peers. At this moment they are still accepting sessions so no schedule has been created yet. You can register, find additional information and directions on their website here.

Finally, although I am not going to be speaking, I will be attending SQL in the City in Los Angeles, CA on October 28th. SQL in the City is a one day SQL Server training event which will include several SQL Server MVPs and you can look at their site here for more details and information.

I look forward to meeting lots of SQL Server professionals on these events.

Same as with previous versions of SQL Server, in Denali the query optimizer can choose between the available access methods, which now also include columnstore indexes, and as always, this will be a cost-based decision. A new choice the query optimizer will have to make is the selection of an execution mode. The new query processing algorithms mentioned in my previous post will run in what is called a batch execution mode, which is different from the traditional processing mode, now called row mode.

In the row execution mode operators process data one row at a time. The new batch execution mode process data in batches which is more efficient for large amounts of data, like the workloads present on data warehouse queries. Each operator in an execution plan can use the row execution mode and, when columnstore indexes are available, some operators can also use the batch mode. There is both an estimated and an actual execution mode and this information is displayed on the query execution plan as I will show later. It is also worth mentioning that, although columnstore indexes can speed up the performance of data warehouse queries, they are not a good choice for very selective queries returning only a few records. In this case the query optimizer may have to rely on row stores, like clustered or regular nonclustered indexes, to find those records quickly. There are no seeks on columnstore indexes.

Same as with previous versions of SQL Server, you still have the choice to use a hint to force any index in the cases where the query optimizer is not giving you a good execution plan. This can happen for example when the query optimizer is choosing a columnstore index when it shouldn’t or when you want to force a columnstore index when it is not being selected. You can also use the new IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX hint to ask the query optimizer to avoid using any columnstore index.

Let me show you an example which you can test on SQL Server Denali CTP3, currently available for download here. To follow this example you will also need the AdventureWorksDWDenali database, available at CodePlex and I will use the same example on BOL to skip the basics and go directly to analyze the batch processing mode (By the way the BOL example didn’t work directly with the AdventureWorksDWDenali database so I had to add a few more columns at the end of the CREATE TABLE statement.)

First, use the following BOL code to create a partition function, a partition scheme and a new partitioned table with a columnstore index

USE AdventureWorksDWDenali;
GO

CREATE PARTITION FUNCTION [ByOrderDateMonthPF](int) AS RANGE RIGHT
FOR VALUES (
    20050701, 20050801, 20050901, 20051001, 20051101, 20051201,
    20060101, 20060201, 20060301, 20060401, 20060501, 20060601,
    20060701, 20060801, 20060901, 20061001, 20061101, 20061201,
    20070101, 20070201, 20070301, 20070401, 20070501, 20070601,
    20070701, 20070801, 20070901, 20071001, 20071101, 20071201,
    20080101, 20080201, 20080301, 20080401, 20080501, 20080601,
    20080701, 20080801, 20080901, 20081001, 20081101, 20081201
)
GO

CREATE PARTITION SCHEME [ByOrderDateMonthRange]
AS PARTITION [ByOrderDateMonthPF]
ALL TO ([PRIMARY])
GO

-- Create a partitioned version of the FactResellerSales table
CREATE TABLE [dbo].[FactResellerSalesPtnd](
    [ProductKey] [int] NOT NULL,
    [OrderDateKey] [int] NOT NULL,
    [DueDateKey] [int] NOT NULL,
    [ShipDateKey] [int] NOT NULL,
    [ResellerKey] [int] NOT NULL,
    [EmployeeKey] [int] NOT NULL,
    [PromotionKey] [int] NOT NULL,
    [CurrencyKey] [int] NOT NULL,
    [SalesTerritoryKey] [int] NOT NULL,
    [SalesOrderNumber] [nvarchar](20) NOT NULL,
    [SalesOrderLineNumber] [tinyint] NOT NULL,
    [RevisionNumber] [tinyint] NULL,
    [OrderQuantity] [smallint] NULL,
    [UnitPrice] [money] NULL,
    [ExtendedAmount] [money] NULL,
    [UnitPriceDiscountPct] [float] NULL,
    [DiscountAmount] [float] NULL,
    [ProductStandardCost] [money] NULL,
    [TotalProductCost] [money] NULL,
    [SalesAmount] [money] NULL,
    [TaxAmt] [money] NULL,
    [Freight] [money] NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [CustomerPONumber] [nvarchar](25) NULL,
    [OrderDate] datetime NULL,
    [DueDate] datetime NULL,
    [ShipDate] datetime NULL
) ON ByOrderDateMonthRange(OrderDateKey);
GO

-- Copy the data from the FactResellerSales into the new table
INSERT INTO dbo.FactResellerSalesPtnd WITH(TABLOCK)
SELECT * FROM dbo.FactResellerSales;
GO

-- Create the columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX [csindx_FactResellerSalesPtnd]
ON [FactResellerSalesPtnd]
(
    [ProductKey],
    [OrderDateKey],
    [DueDateKey],
    [ShipDateKey],
    [ResellerKey],
    [EmployeeKey],
    [PromotionKey],
    [CurrencyKey],
    [SalesTerritoryKey],
    [SalesOrderNumber],
    [SalesOrderLineNumber],
    [RevisionNumber],
    [OrderQuantity],
    [UnitPrice],
    [ExtendedAmount],
    [UnitPriceDiscountPct],
    [DiscountAmount],
    [ProductStandardCost],
    [TotalProductCost],
    [SalesAmount],
    [TaxAmt],
    [Freight],
    [CarrierTrackingNumber],
    [CustomerPONumber]
);

Now run the following query

SELECT SalesTerritoryKey, SUM(ExtendedAmount) AS SalesByTerritory
FROM FactResellerSalesPtnd
GROUP BY SalesTerritoryKey;

This will create the following plan where you can see the new Columnstore Index Scan operator

The properties of the Columnstore Index Scan operator are shown next

You may notice that the actual and estimated execution mode is Row (lines 3 and 4 on the list of properties). Row execution mode was selected because the table is not large enough to require the batch execution mode. We can use the undocumented ROWCOUNT and PAGECOUNT options of the UPDATE STATISTICS statement to simulate a larger table as shown next (for more information about how this works see my post about the DTA here)

UPDATE STATISTICS FactResellerSalesPtnd WITH ROWCOUNT = 10000000, PAGECOUNT = 1000000

Removing the existing plan (using for example DBCC FREEPROCCACHE) and running the same query again will now show the following plan (only part is shown), which this time is using parallelism.

In addition, by looking at the properties of the Columnstore Index Scan you can notice that this time it is using the batch execution mode

You can also use the new IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX hint to disallow the use of a columnstore index. Run the following code

SELECT SalesTerritoryKey, SUM(ExtendedAmount) AS SalesByTerritory
FROM FactResellerSalesPtnd
GROUP BY SalesTerritoryKey
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);

This will show you the following plan which as you can see it is now directly using the FactResellerSalesPtnd table, without using the columnstore index.

Finally, since the number of records and pages of the FactResellerSalesPtnd table was altered for this test, perhaps you want drop it and create a new copy if you need to do some additional testing

DROP TABLE FactResellerSalesPtnd

I am honored to be speaking at the PASS Summit again this year. I’ve been attending this SQL Server conference every year since 2003 and this will be my fourth year speaking. Same as last year I will again be presenting two sessions.

On my first session, Inside the SQL Server Query Optimizer, I will go into the internals of the Query Optimizer and will show you the steps that it performs in the background covering everything from the time a query is submitted to SQL Server until an execution plan is generated. On my second session, Parameter Sniffing: the Query Optimizer vs. the Plan Cache, I will show you how the Query Optimizer uses parameter sniffing to produce a plan tailored to the current parameters of a query and why in some cases it could be a performance problem, including troubleshooting and solutions to these cases.

The PASS Summit is less than four months away and you can register here. I look forward to meeting lots of SQL Server professionals, including those whom I only know via twitter. See you in Seattle in October.

A problem faced by some queries using scalar expressions is that they usually cannot benefit from statistics and, without them, the Query Optimizer will use the 30% selectivity guess on inequality comparisons. A solution to this problem can be the use of computed columns, as SQL Server can automatically create and update statistics on these columns which can help the Query Optimizer to create better execution plans. An additional benefit of this solution is that you don’t need to specify the name of the computed column in your queries for SQL Server to use its statistics. The Query Optimizer automatically matches the computed column definition to an existing scalar expression in a query, so your applications do not need to be changed. Although computed columns have been available in previous versions of SQL Server, the automatic matching feature was only introduced with SQL Server 2005.

To see an example, run this query, which creates the plan shown next:

SELECT * FROM Sales.SalesOrderDetail
WHERE OrderQty * UnitPrice > 25000

The estimated number of rows is 36,395.1, which is 30% of the total number of rows, 121,317, although the query returns only 5 records. SQL Server is obviously using a selectivity guess, as it cannot estimate the selectivity of the expression OrderQty * UnitPrice > 25000.

Now create a computed column:

ALTER TABLE Sales.SalesOrderDetail
ADD cc AS OrderQty * UnitPrice

Run the previous SELECT statement again and note that, this time, the estimated number of rows has changed to 84.3101 which is very close to the actual number of rows returned by the query, as shown in the following plan:

You can optionally test replacing the 25,000 in the query with some other values, like 1,000, 10,000, or 20,000 and verify that the estimated again will be close to the actual number of rows returned.

Note that creating the computed column does not create statistics; these statistics are created the first time that the query is optimized, and you can run the next query to display the information about the statistics objects for the Sales.SalesOrderDetail table:

SELECT * FROM sys.stats
WHERE object_id = object_id('Sales.SalesOrderDetail')

The newly created statistics object will most likely be at the end of the list. Copy its name and use the following command to display the details about the statistics object (I’ve used the name of my local object, but you should replace that as appropriate). You can also use "cc" as the name of the object to get the same results. In both cases, the "cc" column should be shown on the Columns field in the density section.

DBCC SHOW_STATISTICS ('Sales.SalesOrderDetail', _WA_Sys_0000000C_2645B050)

Unfortunately, for the automatic matching feature to work, the expression must be exactly the same as the computed column definition. So, if I change the query to UnitPrice * OrderQty, instead of OrderQty * UnitPrice, the execution plan will show an estimated number of rows of 30% again, as this query will demonstrate:

SELECT * FROM Sales.SalesOrderDetail
WHERE UnitPrice * OrderQty > 25000

As mentioned, the computed column provides statistics so the Query Optimizer can try to get you a better execution plan. In addition, you can create an index on the existing computed column to provide a better navigational alternative. Create the following index

CREATE INDEX IX_cc on Sales.SalesOrderDetail(cc)

By running the original SELECT statement again the Query Optimizer will now choose the newly created index and will produce a more efficient plan using an Index Seek/Key Lookup instead of a Clustered Index Scan, as shown next.

Finally, drop the index and computed column you’ve just created:

DROP INDEX Sales.SalesOrderDetail.IX_cc
GO
ALTER TABLE Sales.SalesOrderDetail
DROP COLUMN cc