{"id":1435,"date":"2012-11-01T00:00:00","date_gmt":"2012-11-01T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/fixing-gatekeeper-row-cardinality-estimate-issues\/"},"modified":"2021-08-24T13:40:04","modified_gmt":"2021-08-24T13:40:04","slug":"fixing-gatekeeper-row-cardinality-estimate-issues","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/fixing-gatekeeper-row-cardinality-estimate-issues\/","title":{"rendered":"Fixing Gatekeeper Row Cardinality Estimate Issues"},"content":{"rendered":"
\n

The SQL Server Query Optimizer needs to estimate the number of rows that are processed for each physical operator in a query plan. These row estimates are referred to as cardinality estimates<\/em> and are required in order to then calculate the cost models of the query plan operators. If these cardinality estimates are inaccurate, the Query Optimizer is less likely to choose the most appropriate execution plan for the query, and so the performance of the query’s execution is likely to be slower.<\/p>\n

There are a variety of reasons for a poor cardinality estimate, and this article will focus on just one of the more complex reasons, affecting the “gatekeeper row” cardinality estimate.<\/p>\n

Gatekeeper Row Scenario<\/h2>\n

We will illustrate this by using a query executed against the AdventureWorksDW2012 database:<\/p>\n

SELECT\u00a0\u00a0[od].[CalendarYear] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[p].[ProductLine] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SUM([fis].[SalesAmount]) AS [SalesAMT]\r\nFROM\u00a0\u00a0\u00a0\u00a0[dbo].[FactInternetSales] AS [fis]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER JOIN [dbo].[DimProduct] AS [p]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON [fis].[ProductKey] = [p].[ProductKey]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER JOIN [dbo].[DimCurrency] AS [c]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON [fis].[CurrencyKey] = [c].[currencykey]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER JOIN [dbo].[DimDate] AS [od]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON [fis].[OrderDateKey] = [od].[DateKey]\r\nWHERE\u00a0\u00a0 [c].[CurrencyName] = N'United Kingdom Pound'\r\nGROUP BY [od].[CalendarYear] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[p].[ProductLine]\r\nORDER BY [od].[CalendarYear] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[p].[ProductLine];\r\nGO <\/pre>\n
The query references one fact table and three separate dimension tables. The abridged relationship diagram is below (click for full size):<\/p>\n
\"1592-1-d345c092-8d98-40d4-8030-e720c9276\"<\/a><\/p>\n
What kind of cardinality estimates do we see and how do they compare to the actual rows? For this article, I was using SQL Server 2012 version 11.0.2316 and I saw the following estimated and actual row counts:<\/p>\n
\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
Rows<\/b><\/p>\n<\/td>\n
\n
Estimated Rows<\/b><\/p>\n<\/td>\n
\n
StmtText<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
12<\/p>\n<\/td>\n
\n
22.17362<\/p>\n<\/td>\n
\n
\u00a0 |–Sort(ORDER BY:([od].[CalendarYear] ASC, [p].[ProductLine] ASC))<\/p>\n<\/td>\n<\/tr>\n
\n
12<\/p>\n<\/td>\n
\n
22.17362<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Hash Match(Aggregate, HASH:([od].[CalendarYear],
\n[p].[ProductLine]), 
\nRESIDUAL:([AdventureWorksDW2012].[dbo].[DimDate].[CalendarYear] as 
\n[od].[CalendarYear] = 
\n[AdventureWorksDW2012].[dbo].[DimDate].[CalendarYear] as 
\n[od].[CalendarYear] AND 
\n[AdventureWorksDW2012].[dbo].[DimProduct].[ProductLine] as 
\n[p].[ProductLine] = 
\n[AdventureWorksDW2012].[dbo].[DimProduct].[ProductLine] as 
\n[p].[ProductLine]) 
\nDEFINE:([Expr1008]=SUM([AdventureWorksDW2012].[dbo].[FactInternetSales].[SalesAmount] 
\nas [fis].[SalesAmount])))<\/p>\n<\/td>\n<\/tr>\n
\n
6740<\/p>\n<\/td>\n
\n
7936.583<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Hash Match(Inner Join, 
\nHASH:([p].[ProductKey])=([fis].[ProductKey]))<\/p>\n<\/td>\n<\/tr>\n
\n
606<\/p>\n<\/td>\n
\n
606<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Clustered Index 
\nScan(OBJECT:([AdventureWorksDW2012].[dbo].[DimProduct].[PK_DimProduct_ProductKey] 
\nAS [p]))<\/p>\n<\/td>\n<\/tr>\n
\n
6740<\/p>\n<\/td>\n
\n
8466.041<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Hash Match(Inner Join, 
\nHASH:([od].[DateKey])=([fis].[OrderDateKey]))<\/p>\n<\/td>\n<\/tr>\n
\n
2191<\/p>\n<\/td>\n
\n
2191<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Clustered Index 
\nScan(OBJECT:([AdventureWorksDW2012].[dbo].[DimDate].[PK_DimDate_DateKey] 
\nAS [od]))<\/p>\n<\/td>\n<\/tr>\n
\n
6740<\/p>\n<\/td>\n
\n
10066.33<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Hash Match(Inner Join, 
\nHASH:([c].[CurrencyKey])=([fis].[CurrencyKey]))<\/p>\n<\/td>\n<\/tr>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Clustered Index 
\nScan(OBJECT:([AdventureWorksDW2012].[dbo].[DimCurrency].[PK_DimCurrency_CurrencyKey] 
\nAS [c]), 
\nWHERE:([AdventureWorksDW2012].[dbo].[DimCurrency].[CurrencyName] as 
\n[c].[CurrencyName]=N’United Kingdom Pound’))<\/p>\n<\/td>\n<\/tr>\n
\n
60398<\/p>\n<\/td>\n
\n
60398<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Clustered Index 
\nScan(OBJECT:([AdventureWorksDW2012].[dbo].[FactInternetSales].
\n[PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber] 
\nAS [fis]))<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
The clustered index scan of FactInternetSales<\/strong> shows that the estimated rows are equal to actual rows. This represents the total row count from that table, which was 60,398 rows. The estimated and actual row counts are also identical for the clustered index scan of the DimCurrency<\/strong> table, which makes sense given the “United Kingdom Pound” search condition for the CurrencyName<\/strong> column.<\/p>\n
The DimCurrency<\/strong> has 105 rows in it. But that doesn’t mean that FactInternetSales<\/strong> has rows for every possible CurrencyKey<\/strong>. In reality, the FactInternetSales<\/strong> has the following distribution of rows by currency, as we can determine by using this query:<\/p>\n
SELECT\u00a0\u00a0[c].[CurrencyName],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(*) AS [RowCount]\r\nFROM\u00a0\u00a0\u00a0\u00a0[dbo].[FactInternetSales] AS [fis]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER JOIN [dbo].[DimCurrency] AS [c]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON [fis].[CurrencyKey] = [c].[currencykey]\r\nGROUP BY [c].[CurrencyName]\r\nORDER BY [c].[CurrencyName];\r\nGO <\/pre>\n
\"1592-1-b2c97887-3a62-4f28-808e-ef10fad41\"<\/p>\n
As you can see, most currencies are not<\/em> represented in FactInternetSales<\/strong>.<\/p>\n
Given this, what happens if we change the previous query to reference a currency that has no associated rows? For example, switching to the Pakistan Rupee currency?<\/p>\n
SELECT\u00a0\u00a0[od].[CalendarYear] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[p].[ProductLine] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SUM([fis].[SalesAmount]) AS [SalesAMT]\r\nFROM\u00a0\u00a0\u00a0\u00a0[dbo].[FactInternetSales] AS [fis]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER JOIN [dbo].[DimProduct] AS [p]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON [fis].[ProductKey] = [p].[ProductKey]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER JOIN [dbo].[DimCurrency] AS [c]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON [fis].[CurrencyKey] = [c].[currencykey]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER JOIN [dbo].[DimDate] AS [od]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON [fis].[OrderDateKey] = [od].[DateKey]\r\nWHERE\u00a0\u00a0 [c].[CurrencyName] = N'Pakistan Rupee'\r\nGROUP BY [od].[CalendarYear] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[p].[ProductLine]\r\nORDER BY [od].[CalendarYear] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[p].[ProductLine];\r\nGO <\/pre>\n
The cardinality estimates versus actual now appear as follows:<\/p>\n
\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
Rows<\/b><\/p>\n<\/td>\n
\n
Estimated<\/b><\/p>\n
Rows<\/b><\/p>\n<\/td>\n
\n
StmtText<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
0<\/p>\n<\/td>\n
\n
22.17362<\/p>\n<\/td>\n
\n
\u00a0 |–Sort(ORDER BY:([od].[CalendarYear] ASC, [p].[ProductLine] ASC))<\/p>\n<\/td>\n<\/tr>\n
\n
0<\/p>\n<\/td>\n
\n
22.17362<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Hash Match(Aggregate, HASH:([od].[CalendarYear], 
\n[p].[ProductLine]), 
\nRESIDUAL:([AdventureWorksDW2012].[dbo].[DimDate].[CalendarYear] as 
\n[od].[CalendarYear] = 
\n[AdventureWorksDW2012].[dbo].[DimDate].[CalendarYear] as 
\n[od].[CalendarYear] AND 
\n[AdventureWorksDW2012].[dbo].[DimProduct].[ProductLine] as 
\n[p].[ProductLine] = 
\n[AdventureWorksDW2012].[dbo].[DimProduct].[ProductLine] as 
\n[p].[ProductLine]) 
\nDEFINE:([Expr1008]=SUM([AdventureWorksDW2012].[dbo].[FactInternetSales].[SalesAmount] 
\nas [fis].[SalesAmount])))<\/p>\n<\/td>\n<\/tr>\n
\n
0<\/p>\n<\/td>\n
\n
7936.583<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Hash Match(Inner Join, 
\nHASH:([p].[ProductKey])=([fis].[ProductKey]))<\/p>\n<\/td>\n<\/tr>\n
\n
606<\/p>\n<\/td>\n
\n
606<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Clustered Index 
\nScan(OBJECT:([AdventureWorksDW2012].[dbo].[DimProduct].[PK_DimProduct_ProductKey] 
\nAS [p]))<\/p>\n<\/td>\n<\/tr>\n
\n
0<\/p>\n<\/td>\n
\n
8466.041<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Hash Match(Inner Join, 
\nHASH:([od].[DateKey])=([fis].[OrderDateKey]))<\/p>\n<\/td>\n<\/tr>\n
\n
2191<\/p>\n<\/td>\n
\n
2191<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Clustered Index 
\nScan(OBJECT:([AdventureWorksDW2012].[dbo].[DimDate].[PK_DimDate_DateKey] 
\nAS [od]))<\/p>\n<\/td>\n<\/tr>\n
\n
0<\/p>\n<\/td>\n
\n
10066.33<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Hash Match(Inner Join, 
\nHASH:([c].[CurrencyKey])=([fis].[CurrencyKey]))<\/p>\n<\/td>\n<\/tr>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Clustered Index 
\nScan(OBJECT:([AdventureWorksDW2012].[dbo].[DimCurrency].[PK_DimCurrency_CurrencyKey] 
\nAS [c]), 
\nWHERE:([AdventureWorksDW2012].[dbo].[DimCurrency].[CurrencyName] as 
\n[c].[CurrencyName]=N’Pakistan Rupee’))<\/p>\n<\/td>\n<\/tr>\n
\n
60398<\/p>\n<\/td>\n
\n
60398<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Clustered Index 
\nScan(OBJECT:([AdventureWorksDW2012].[dbo].[FactInternetSales].
\n[PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber] 
\nAS [fis]))<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
As with the original query, the clustered index scan of FactInternetSales<\/strong> shows that the estimated rows are equal to the actual rows. This represents the total row count from that table, which was 60,398 rows. The estimated and actual row counts are also identical for the clustered index scan of the DimCurrency table<\/strong>, showing a value of “1” representing the search on Pakistan Rupee in the CurrencyName <\/strong>column.<\/p>\n
What about the Hash Match operation between the two tables? Our previous example had 10,066 (estimated) versus 6,740 (actual). But with our new query using a currency that does not <\/em>exist in the fact table, our estimate is still 10,066 and actual number of rows this time is 0.<\/p>\n
So a gatekeeper row, in this context, represents a dimension row that will let several fact table rows through to the parent operators when selected via the presence or absence of a search condition. When the gatekeeper dimension row is not<\/em> present, the fact table rows are ultimately not passed through to the final result set, but yet the cardinality estimate issue remains.<\/p>\n
I can exacerbate the problem with this cardinality estimate by modifying all the rows in the fact table to the same currency, the Yen in our case, with this simple statement (and if you’re trying this out yourself, be sure to back up your AdventureWorksDW2012 database beforehand so you can revert back afterwards):<\/p>\n
-- Setting all Fact table rows to the Yen currency\r\nUPDATE [dbo].[FactInternetSales]\r\nSET [CurrencyKey] = 102;\r\nGO<\/pre>\n
Once I have done this and we now have only Yen, I see the following estimated rows vs. actual rows if I execute the previous query that references the Pakistan Rupee.<\/p>\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
Rows<\/b><\/p>\n<\/td>\n
\n
Estimated<\/b><\/p>\n
Rows<\/b><\/p>\n<\/td>\n
\n
StmtText<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
0<\/p>\n<\/td>\n
\n
22.04758<\/p>\n<\/td>\n
\n
\u00a0 |–Stream Aggregate(GROUP BY:([od].[CalendarYear], 
\n[p].[ProductLine]) DEFINE:([Expr1008]=SUM([partialagg1015])))<\/p>\n<\/td>\n<\/tr>\n
\n
0<\/p>\n<\/td>\n
\n
23.5184<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Sort(ORDER BY:([od].[CalendarYear] ASC, [p].[ProductLine] 
\nASC))<\/p>\n<\/td>\n<\/tr>\n
\n
0<\/p>\n<\/td>\n
\n
23.5184<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Nested Loops(Inner Join, OUTER 
\nREFERENCES:([fis].[CurrencyKey]))<\/p>\n<\/td>\n<\/tr>\n
\n
12<\/p>\n<\/td>\n
\n
88.19033<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Hash Match(Aggregate, HASH:([fis].[CurrencyKey], 
\n[p].[ProductLine], [od].[CalendarYear]), 
\nRESIDUAL:([AdventureWorksDW2012].[dbo].[FactInternetSales].[CurrencyKey] 
\nas [fis].[CurrencyKey] = 
\n[AdventureWorksDW2012].[dbo].[FactInternetSales].[CurrencyKey] as 
\n[fis].[CurrencyKey] AND 
\n[AdventureWorksDW2012].[dbo].[DimProduct].[ProductLine] as 
\n[p].[ProductLine] = 
\n[AdventureWorksDW2012].[dbo].[DimProduct].[ProductLine] as 
\n[p].[ProductLine] AND 
\n[AdventureWorksDW2012].[dbo].[DimDate].[CalendarYear] as 
\n[od].[CalendarYear] = 
\n[AdventureWorksDW2012].[dbo].[DimDate].[CalendarYear] as 
\n[od].[CalendarYear]) 
\nDEFINE:([partialagg1015]=SUM([AdventureWorksDW2012].[dbo].[FactInternetSales].[SalesAmount] 
\nas [fis].[SalesAmount])))<\/p>\n<\/td>\n<\/tr>\n
\n
60398<\/p>\n<\/td>\n
\n
47348.81<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | \u00a0\u00a0\u00a0|–Hash 
\nMatch(Inner Join, HASH:([p].[ProductKey])=([fis].[ProductKey]))<\/p>\n<\/td>\n<\/tr>\n
\n
606<\/p>\n<\/td>\n
\n
606<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Clustered Index 
\nScan(OBJECT:([AdventureWorksDW2012].[dbo].[DimProduct].[PK_DimProduct_ProductKey] 
\nAS [p]))<\/p>\n<\/td>\n<\/tr>\n
\n
60398<\/p>\n<\/td>\n
\n
50507.5<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Hash Match(Inner Join, 
\nHASH:([od].[DateKey])=([fis].[OrderDateKey]))<\/p>\n<\/td>\n<\/tr>\n
\n
2191<\/p>\n<\/td>\n
\n
2191<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Clustered Index 
\nScan(OBJECT:([AdventureWorksDW2012].[dbo].[DimDate].[PK_DimDate_DateKey] 
\nAS [od]))<\/p>\n<\/td>\n<\/tr>\n
\n
60398<\/p>\n<\/td>\n
\n
60398<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Clustered Index 
\nScan(OBJECT:([AdventureWorksDW2012].[dbo].[FactInternetSales].
\n[PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber] 
\nAS [fis]))<\/p>\n<\/td>\n<\/tr>\n
\n
0<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Clustered Index 
\nSeek(OBJECT:([AdventureWorksDW2012].[dbo].[DimCurrency].[PK_DimCurrency_CurrencyKey] 
\nAS [c]), 
\nSEEK:([c].[CurrencyKey]=[AdventureWorksDW2012].[dbo].[FactInternetSales].[CurrencyKey] 
\nas [fis].[CurrencyKey]),\u00a0 
\nWHERE:([AdventureWorksDW2012].[dbo].[DimCurrency].[CurrencyName] as 
\n[c].[CurrencyName]=N’Pakistan Rupee’) ORDERED FORWARD)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
This time the Query Optimizer re-arranged the order in which the tables were joined. This new plan started with a hash join between DimDate<\/strong> and FactInternetSales<\/strong>, followed by a hash join to DimProduct<\/strong>, and then finally a nested loop join to DimCurrency<\/strong>. The estimated row count of the nested loop operation is 24 vs. the actual 0 row count. So we see that we are not<\/em> filtering out the FactInternetSales<\/strong> rows early in the plan, even though no rows are ultimately returned.<\/p>\n
Increasing the Scale of the Problem<\/h2>\n
Why is all of this important? It is because, with a real relational data warehouse database of any scale, the performance will be noticeable impacted. The problem is that the Query Optimizer isn’t aware that there are zero qualifying rows in the Fact table. The query plan generates unnecessary reads in the leaf level of the execution plan and then passes the pre-filtered rows to the intermediate levels of the plan tree and is not filtered down until later steps. Our current database is small, but the consequences of these cardinality estimate issues become more pronounced with larger tables.<\/p>\n
To demonstrate the impact at a larger scale, I’m going to increase the size of the FactInternetSales table to 3,865,472 rows:<\/p>\n
-- Increase size of table to 3,865,472 rows\r\nINSERT\u00a0\u00a0[dbo].[FactInternetSales]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0( [ProductKey] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[OrderDateKey] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[DueDateKey] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[ShipDateKey] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[CustomerKey] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[PromotionKey] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[CurrencyKey] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[SalesTerritoryKey] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[SalesOrderNumber] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[SalesOrderLineNumber] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[RevisionNumber] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[OrderQuantity] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[UnitPrice] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[ExtendedAmount] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[UnitPriceDiscountPct] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[DiscountAmount] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[ProductStandardCost] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[TotalProductCost] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[SalesAmount] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[TaxAmt] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[Freight] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[CarrierTrackingNumber] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[CustomerPONumber] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[OrderDate] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[DueDate] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[ShipDate]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT\u00a0\u00a0[FactInternetSales].[ProductKey] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[OrderDateKey] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[DueDateKey] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[ShipDateKey] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[CustomerKey] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[PromotionKey] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[CurrencyKey] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[SalesTerritoryKey] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0LEFT(CAST(NEWID() AS NVARCHAR(36)), 20) ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[SalesOrderLineNumber] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[RevisionNumber] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[OrderQuantity] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[UnitPrice] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[ExtendedAmount] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[UnitPriceDiscountPct] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[DiscountAmount] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[ProductStandardCost] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[TotalProductCost] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[SalesAmount] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[TaxAmt] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[Freight] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[CarrierTrackingNumber] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[CustomerPONumber] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[OrderDate] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[DueDate] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[FactInternetSales].[ShipDate]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0\u00a0\u00a0\u00a0[dbo].[FactInternetSales];\r\nGO 6 -- Executes this INSERT 6 separate times<\/pre>\n
I’ve inflated the size based on the modified data which had all rows associated with just one currency key. My gatekeeper dimension row is CurrencyKey = 102. If I reference this specific key, then millions of fact table rows will need to be accessed, whereas any other value of CurrencyKey, assuming equality search conditions, will cause no rows to be returned from the Fact table.<\/p>\n
Re-executing the query that referenced the Pakistan Rupee, the following plan is generated (show via SQL Server Plan Explorer – click for full size):<\/p>\n
\"1592-1-860f3fba-ac26-4198-9447-517a66a99\"<\/a><\/p>\n
The first observation is that the plan is now executed with parallel operations. This is an artifact of the increase in the number of fact table rows and associated cost.<\/p>\n
The second observation is that millions of rows are flowing through the plan tree and only narrowing down until it is roughly 2\/3rds through the plan. The associated estimated vs. actual row counts continue to be skewed and the Query Optimizer doesn’t know that all rows in FactInternet<\/strong>Sales<\/strong> only have one CurrencyKey<\/strong> value. The DimCurrency<\/strong> table is not evaluated until very late in the plan:<\/p>\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
Rows<\/b><\/p>\n<\/td>\n
\n
Estimate<\/b><\/p>\n
Rows<\/b><\/p>\n<\/td>\n
\n
StmtText<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
0<\/p>\n<\/td>\n
\n
21.11082<\/p>\n<\/td>\n
\n
\u00a0 |–Stream Aggregate(GROUP BY:([od].[CalendarYear], 
\n[p].[ProductLine]) DEFINE:([Expr1008]=SUM([partialagg1015])))<\/p>\n<\/td>\n<\/tr>\n
\n
0<\/p>\n<\/td>\n
\n
22.51457<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Parallelism(Gather Streams, ORDER BY:([od].[CalendarYear] 
\nASC, [p].[ProductLine] ASC))<\/p>\n<\/td>\n<\/tr>\n
\n
0<\/p>\n<\/td>\n
\n
22.51457<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Nested Loops(Inner Join, OUTER 
\nREFERENCES:([fis].[CurrencyKey]))<\/p>\n<\/td>\n<\/tr>\n
\n
12<\/p>\n<\/td>\n
\n
84.44327<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Sort(ORDER BY:([od].[CalendarYear] ASC, [p].[ProductLine] 
\nASC))<\/p>\n<\/td>\n<\/tr>\n
\n
12<\/p>\n<\/td>\n
\n
84.44327<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0 |–Hash Match(Aggregate, HASH:([fis].[CurrencyKey], 
\n[p].[ProductLine], [od].[CalendarYear]), 
\nRESIDUAL:([AdventureWorksDW2012].[dbo].[FactInternetSales].[CurrencyKey] 
\nas [fis].[CurrencyKey] = 
\n[AdventureWorksDW2012].[dbo].[FactInternetSales].[CurrencyKey] as 
\n[fis].[CurrencyKey] AND 
\n[AdventureWorksDW2012].[dbo].[DimProduct].[ProductLine] as 
\n[p].[ProductLine] = 
\n[AdventureWorksDW2012].[dbo].[DimProduct].[ProductLine] as 
\n[p].[ProductLine] AND 
\n[AdventureWorksDW2012].[dbo].[DimDate].[CalendarYear] as 
\n[od].[CalendarYear] = 
\n[AdventureWorksDW2012].[dbo].[DimDate].[CalendarYear] as 
\n[od].[CalendarYear]) 
\nDEFINE:([partialagg1015]=SUM([AdventureWorksDW2012].
\n[dbo].[FactInternetSales].[SalesAmount] 
\nas [fis].[SalesAmount])))<\/p>\n<\/td>\n<\/tr>\n
\n
3865472<\/p>\n<\/td>\n
\n
2900982<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Parallelism(Repartition Streams, Hash Partitioning, 
\nPARTITION COLUMNS:([fis].[CurrencyKey], [p].[ProductLine], 
\n[od].[CalendarYear]))<\/p>\n<\/td>\n<\/tr>\n
\n
3865472<\/p>\n<\/td>\n
\n
2900982<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Hash Match(Inner Join, 
\nHASH:([p].[ProductKey])=([fis].[ProductKey]))<\/p>\n<\/td>\n<\/tr>\n
\n
606<\/p>\n<\/td>\n
\n
606<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Bitmap(HASH:([p].[ProductKey]),
\nDEFINE:([Opt_Bitmap1010]))<\/p>\n<\/td>\n<\/tr>\n
\n
606<\/p>\n<\/td>\n
\n
606<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0|\u00a0\u00a0\u00a0 |–Parallelism(Repartition Streams,
\nHash Partitioning, PARTITION 
\nCOLUMNS:([p].[ProductKey]))<\/p>\n<\/td>\n<\/tr>\n
\n
606<\/p>\n<\/td>\n
\n
606<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Clustered Index 
\nScan(OBJECT:([AdventureWorksDW2012].[dbo].
\n[DimProduct].[PK_DimProduct_ProductKey] 
\nAS [p]))<\/p>\n<\/td>\n<\/tr>\n
\n
3865472<\/p>\n<\/td>\n
\n
2900982<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Parallelism(Repartition Streams, Hash Partitioning, 
\nPARTITION COLUMNS:([fis].[ProductKey]))<\/p>\n<\/td>\n<\/tr>\n
\n
3865472<\/p>\n<\/td>\n
\n
2900982<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Hash Match(Inner Join, 
\nHASH:([od].[DateKey])=([fis].[OrderDateKey]))<\/p>\n<\/td>\n<\/tr>\n
\n
2191<\/p>\n<\/td>\n
\n
2191<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Bitmap(HASH:([od].[DateKey]),
\nDEFINE:([Opt_Bitmap1009]))<\/p>\n<\/td>\n<\/tr>\n
\n
2191<\/p>\n<\/td>\n
\n
2191<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0 |–Parallelism(Repartition Streams, Hash Partitioning,
\nPARTITION 
\nCOLUMNS:([od].[DateKey]))<\/p>\n<\/td>\n<\/tr>\n
\n
2191<\/p>\n<\/td>\n
\n
2191<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Clustered Index 
\nScan(OBJECT:([AdventureWorksDW2012].[dbo].[DimDate].[PK_DimDate_DateKey] 
\nAS [od]))<\/p>\n<\/td>\n<\/tr>\n
\n
3865472<\/p>\n<\/td>\n
\n
2900982<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Parallelism(Repartition Streams, Hash Partitioning, 
\nPARTITION COLUMNS:([fis].[OrderDateKey]))<\/p>\n<\/td>\n<\/tr>\n
\n
3865472<\/p>\n<\/td>\n
\n
3865472<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Clustered Index 
\nScan(OBJECT:([AdventureWorksDW2012].[dbo].[FactInternetSales].
\n[PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber] 
\nAS [fis]), 
\nWHERE:(PROBE([Opt_Bitmap1009],[AdventureWorksDW2012].
\n[dbo].[FactInternetSales].[OrderDateKey] 
\nas [fis].[OrderDateKey],N'[IN ROW]’) AND 
\nPROBE([Opt_Bitmap1010],[AdventureWorksDW2012].
\n[dbo].[FactInternetSales].[ProductKey] 
\nas [fis].[ProductKey],N'[IN ROW]’)))<\/p>\n<\/td>\n<\/tr>\n
\n
0<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |–Clustered Index 
\nSeek(OBJECT:([AdventureWorksDW2012].
\n[dbo].[DimCurrency].[PK_DimCurrency_CurrencyKey] 
\nAS [c]), 
\nSEEK:([c].[CurrencyKey]=[AdventureWorksDW2012].
\n[dbo].[FactInternetSales].[CurrencyKey] 
\nas [fis].[CurrencyKey]),\u00a0 
\nWHERE:([AdventureWorksDW2012].[dbo].[DimCurrency].[CurrencyName] as 
\n[c].[CurrencyName]=N’Pakistan Rupee’) ORDERED FORWARD)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
Why Care?<\/h2>\n
So what are the consequences of this? There are a few reasons that stand out for the problem that I’ve illustrated:<\/p>\n