Databases Reference
In-Depth Information
Next, let's see an example of optimized bitmap filtering. Run the query in Listing 6-10.
USE
AdventureWorksDW
GO
SELECT
*
FROM
dbo
.
FactInternetSales
AS
f
JOIN
dbo
.
DimProduct
AS
p
ON
f
.
ProductKey
=
p
.
ProductKey
JOIN
dbo
.
DimCustomer
AS
c
ON
f
.
CustomerKey
=
c
.
CustomerKey
WHERE
p
.
ListPrice
>
50
AND
c
.
Gender
=
'M'
Listing 6-10.
Figure 6-9:
A bitmap filtering example.
Note
You may not get the plan shown earlier on a test system with a limited number of logical processors, but
you can simulate that your SQL Server instance has (for example) 8 processors by using the -P startup
parameter. In order to do that, open
Configuration Manager
, right-click on your SQL Server service,
select
Properties
, select the
Advanced
tab, and edit the
Startup Parameters
entry by adding "
;-P8
" at
the end of the line. Click
OK
and restart the instance, remembering to remove this entry when you finish
your testing.
Since this plan is too big to show here, only a section is included in Figure 6-9 (this
plan was created with SQL Server 2008 R2, so the one for SQL Server 2008 may vary a
little). This part of the plan shows one of the two available Bitmap operators, in this case
processing the rows from the
DimCustomer
table, which is the build input of the Hash