Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Hi, as per the BOL, In the "Optimizing Data Warehouse Query Performance Through Bitmap Filtering"

the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai according to sql Server 2008.

Can you verify it please.

Hi Akash,

What statements in the topic led you to that conclusion? I wrote the topic, so I want to make sure I correct any such sentences that can be perceived incorrectly.

Thanks,

Gail

|||

Hi Akash,

technically, there is no reason for higher cost of scans. Could you please point Gail to the BOL topic so that we can make sure that it is accurate?

Thanks,

Torsten Grabs [MSFT]

Program Manager

SQL Server Query Processor

|||

Hi Gail,

As i've mentioned that in the BOL when you type "Bitmap Index", you let the user understand how optimized bitmap filtering is implemented. You also demonstrate an example for it comparing both the SQL Server 2005 along with Katmai and in that very example you did mention that the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai when compared to sql Server 2005. Just have a look at the figures.

Clustered index scan costs 3% for DimProduct & 12% for DimCustomer in case of SQL Server 2008 where as the same clustering index scan costs 2% and 11% respectively in SQL Server 2005.

Similarly Table Scan costs 14% in SQL Server 2008 and 13% in case of SQL Server 2005.

Should not there be a decrese in scanning cost in Katmai when compared to SQL Server 2005 ?

|||

Hi Akash,

Thanks very much for the pointer to the BOL entry you were referring to. There are two considerations two keep in mind when comparing the table scan cost information as you do:

- Costing functions may change between releases. Hence, when you compare query plan costs and operator costs across releases, you are on unstable ground as you make the assumption that the costing function has not been changed.

- The percentages are relative costs. The less expensive the remaining query plan, i.e., the plan on top of the table scan, gets the higher this percentage value will be - assuming that the cost of the scan is the same. If you look into the subtree cost information of the scan, you will see that the cost is the same for both plans. But, the remaining operators, in particular the hash joins on top, have become cheaper since there are fewer rows to process - which explain why you see the percentages increase.

A further note on why the absolute costs between the scan operators remain the same: even with bitmap filters, the table scan operation has to read all the rows from the disk or buffer pool initially. However, as soon as the row is read into the query processor, we can apply the bitmap filter. The main cost, however, is spent in disk or buffer pool reads - the bitmap filter processing itself is very cheap. Therefore, we do not model the bitmap filtering cost in plan generation. However, optimized bitmap filters do model the cardinality changes of the bitmap filter on top of the scan operation.

Hope this explains the showplan information in the BOL topic.

Best regards,

Torsten Grabs [MSFT]

Program Manager

Microsoft SQL Server Query Processor

|||

Thanks Torsten. It was really a great help,appreciate your effort in letting me understand this