Re: MSAS 2000 Read Time (64 bit) ; DB: DB2
- From: "Dave Wickert [MSFT]" <dwickert@xxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 19 Sep 2005 18:03:43 -0700
Now that is a *nice* machine.
Since this is 64-bit, it means that you are using the native DB2 OLEDB
provider. I would check with them to ensure that you have the latest version
of the software.
To debug this, I think the first thing that I would do is to get the SQL
statements that we are issuing (from the system-wide processing log file,
which if you haven't already done, stop reading this email and do
immediately, then read the rest . . .
(stopping while you turn on the system-wide processing log file)
OK. Now go to that file and look at the SQL statement. Pull it out and
execute it by-hand using the normal DB2 utilities. Sorry, I'm not up on the
latest and greatest from IBM, but I am sure you know it. Execute several
statement in parallel yourself interactively. What throughput are you
getting?
A known problem is if you are table partitioning on the IBM DB2-side and
partitioning on the AS-side. The way that AS passes the data slice to DB2 is
to use ODBC cannonical format for parameters. The SQL looks something like:
SELECT <col list> FROM <table> WHERE <slice field> = ?
And then the ? is replaced with the data slice for the partition. The WHERE
clause is automatically added by AS when you have multi-partitioning. The
issue on the DB2-side is that the DB2 optimizer cannot use table
partitioning across a parameter passed like that. It must be passed as a
constant. Thus on the AS-side, you set an advanced filter for the partition
to be <slice-field> = <value> and then set a registry setting to disable AS
from adding the default WHERE clause for partitioning. The registry setting
is documented here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql2k_anservregsettings.asp
See MAP_NOT_USE_SLICE_FOR_QUERY
Hope this helps.
--
Dave Wickert [MSFT]
dwickert@xxxxxxxxxxxxxxxxxxxx
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prasad" <Prasad@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:25DD0DB2-3DCA-426A-BF0C-954CFF4A8D31@xxxxxxxxxxxxxxxx
> Hi Dave,
>
> I am running 3 cube partitions in parallel using Parallel Partition
> Utility.
> The Database is DB2 UDB Version 8.2. The MSAS Read time is a concern at
> this
> point. I am not doing any aggregation on the cubes. When I see the
> generated
> logs, MSAS is reading 10,000 rows per second which is 60K per minute.
> Taking
> all 3 partitions into consideration it is reading about 180K rows per
> minute.
>
> When we execute the same 3 queries in parallel on the DB2 database and
> spit
> out the results to a text file, every thing is done in a matter of 5 to 6
> minutes. I am trying to find out the bottle neck and speed up the Read
> time.
> I mentioned our Hardware and Software settings along with the Analysis
> Services settings. Please let me know.
>
> Software:
> . 64 bit Microsoft Analysis Services 2000 Enterprise Edition
> . 64 bit Microsoft Windows Server 2003 Enterprise Edition
>
> Hardware:
> . Intel Itanium processor Family
> . 1.60 GHz, 8 Way
> . 60 GB RAM
> . Gigabit Ethernet
> . SCSI Disk Device
>
> MSAS Settings:
>
>
> Performance settings:
> . Maximum number of threads: 16
> . Large Level defined as: 10,000
>
> Memory settings:
> . Minimum allocated memory: 16384 MB
> . Memory conservation threshold: 32768 MB
>
> Processing settings:
> . Read-ahead buffer size: 32 MB
> . Process buffer size: 256 MB
>
> Thanks,
>
> Prasad.
>
>
.
- Follow-Ups:
- Re: MSAS 2000 Read Time (64 bit) ; DB: DB2
- From: Prasad
- Re: MSAS 2000 Read Time (64 bit) ; DB: DB2
- Prev by Date: Re: MSAS 2gb/3gb limit...
- Next by Date: Re: Client tools for SQL Server 2005 Analysis Services - Where are they?
- Previous by thread: Re: MSAS 2000 Read Time (64 bit) ; DB: DB2
- Next by thread: Re: MSAS 2000 Read Time (64 bit) ; DB: DB2
- Index(es):
Relevant Pages
|
Loading
