Bit-Map Indexes and Query Governors 
Author Message
 Bit-Map Indexes and Query Governors

I'm using SQL Server 7 for Data Warehousing.

There are 2 features in Oracle that look particularly useful:

1) Bit-Map Indexes. In SQL it is very hard to Index up a large table
for Adhoc queries, because it's impossible to guess which columns are
required. The Bit-Map index seems to be ideal for deciding which rows
of a table to scan when complex criteria are applied in an Adhoc
select query.

2) Query Governor to assign proportions of Hardware resource to
specific groups of people. Which would stop 1 user gaining nearly 100%
of the server resource when they run a large query during a quiet
point and yet then a load of other User's come along and want to use
the Server, but have to wait say 15 minutes for the original user's
query to finish.

Are there any similar features in SQL Server?

David



Sat, 28 Feb 2004 01:00:04 GMT
 Bit-Map Indexes and Query Governors

David,

SQL Server 7 ships with OLAP Services. OLAP Services supports bit map
indexes, but you don't specify the types of indexes in OLAP Services.

The SQL Server database engine is really heavily oriented to OLTP
applications. OLAP / DSS applications are best done with OLAP Services.
While it might take a bit to ramp up to speed with OLAP Services, I think
you will be very pleasantly surprised by the flexibility, speed and results
of the product.

BTW, SQL Server 2000 ships with Analysis Services which is OLAP Services
renamed. Analysis Services is an even better product than OLAP Services, and
if possible I would strongly recommend upgrading to SQL Server 2000 in order
to take advantage of Analysis Services.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> I'm using SQL Server 7 for Data Warehousing.

> There are 2 features in Oracle that look particularly useful:

> 1) Bit-Map Indexes. In SQL it is very hard to Index up a large table
> for Adhoc queries, because it's impossible to guess which columns are
> required. The Bit-Map index seems to be ideal for deciding which rows
> of a table to scan when complex criteria are applied in an Adhoc
> select query.

> 2) Query Governor to assign proportions of Hardware resource to
> specific groups of people. Which would stop 1 user gaining nearly 100%
> of the server resource when they run a large query during a quiet
> point and yet then a load of other User's come along and want to use
> the Server, but have to wait say 15 minutes for the original user's
> query to finish.

> Are there any similar features in SQL Server?

> David



Sat, 28 Feb 2004 03:16:06 GMT
 Bit-Map Indexes and Query Governors
I am actually using OLAP Services. However the way I'm using it is....
- In our SQL Server Data warehouse, we have 60 monthend snapshots of
our 13 million customers, which are held in 60 large (5.5GB)
de-normalised tables.
- Each month we then aggregate this information into 1 table (around
20 million rows, 10GB) which contains summary information for all
monthends in a star/snowflake schema.
- We use the summary table to build a MOLAP cube each monthend (using
Office 2000 Web components/Excel 2000 as the frontend), whose
sub-second response times we have been impressed with.

However, the users often need a lower level of detail than we have
included in the cubes and so they write SQL queries against the
underlying 60 customer tables. I think realistically these tables are
too large to feed into OLAP and also the Users quite like the
flexibility of writing complex SQL queries. The trouble is we're
finding it very difficult to index up these large tables for Adhoc
querying.

Unfortunately SQL 2000 is not approved software in our organisation,
so I'm unable to upgrade to Analysis services.

Any ideas on how to index a large table for Adhoc queries?

David



Sat, 28 Feb 2004 16:20:35 GMT
 Bit-Map Indexes and Query Governors
David,
I think AS2000 could handle the level of detail you are discussing so it
might be worthwhile trying to have it added to the approved list.

Why do you say that SQL Server does not have the capabilities for these
sorts of queries? Are you getting poor perf/bad plans? SQL Server uses a
technique called index intersections to provide most of the perf of bitmap
indexes without many of the overheads, if you index for this scenario then
you should get good performance for heavily attributed dimensional queries,
which I am guessing may be your issue?

Below are a couple of useful links.

-Euan

Info on very high scale Analysis Services Cubes:
http://www.microsoft.com/sql/techinfo/BI/terabytecube.asp

Info on tuning the SQL Server relational database for DW scenarios:
http://www.microsoft.com/sql/techinfo/administration/2000/RDBMSperftu...
p

Quote:
> I am actually using OLAP Services. However the way I'm using it is....
> - In our SQL Server Data warehouse, we have 60 monthend snapshots of
> our 13 million customers, which are held in 60 large (5.5GB)
> de-normalised tables.
> - Each month we then aggregate this information into 1 table (around
> 20 million rows, 10GB) which contains summary information for all
> monthends in a star/snowflake schema.
> - We use the summary table to build a MOLAP cube each monthend (using
> Office 2000 Web components/Excel 2000 as the frontend), whose
> sub-second response times we have been impressed with.

> However, the users often need a lower level of detail than we have
> included in the cubes and so they write SQL queries against the
> underlying 60 customer tables. I think realistically these tables are
> too large to feed into OLAP and also the Users quite like the
> flexibility of writing complex SQL queries. The trouble is we're
> finding it very difficult to index up these large tables for Adhoc
> querying.

> Unfortunately SQL 2000 is not approved software in our organisation,
> so I'm unable to upgrade to Analysis services.

> Any ideas on how to index a large table for Adhoc queries?

> David



Fri, 05 Mar 2004 14:50:42 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Bit Mapped Index and Star Index information request

2. Are bit map indexes supported in SQL2000

3. using bit-mapped indexes

4. Bit-Mapped Indexing.....(Beta Testers Wanted)

5. Bit-Mapped Indexing Routines - Beta Testers Wanted

6. Bit-Mapped Indexing Routines----Beta Testers Wanted

7. Bit-Mapped Indexing.....(Beta Testers Wanted)

8. Bit-Mapped Indexing Routines----Beta Testers Wanted

9. Bit-Mapped Indexing Routines - Beta Testers Wanted

10. Bit-Mapped Indexing Routines - Beta Testers Wanted

11. Oracle 7.3 bit map index problem

12. Bit-mapped index documentation/support?


 
Powered by phpBB® Forum Software