HOLAP much faster then MOLAP !?

When I process a cube with the HOLAP (100% performance gain) storage option the querytime from Excel XP is very fast.
When I process the same cube with the MOLAP (100% performance gain) storage option the same query takes forever.
I'm puzzled. Can someone explain this?

Edwin

P.S. THe cube has a DistinctCount measure in it. Does this have anything to do with the poor MOLAP performance?You are correct - that is unusual. How big are both of the cubes ? Have you tried duplicating the same behavior with the foodmart sample ? Since you are suspicious of the distinctcount - have you eliminated that measure to see how it affects performance ?|||Have you tried it under excel 2000 ?|||Thank for the reply.

Excel 2000 gives me the same problem, as well as the cube data browser in the Analysis Manager itself.
I replaced the Distinct Count by a Sum. Performance was OK in both HOLAP and MOLAP, so the Distinct Count causes the pain (but I really need it).
I couldn't reproduce the same thing in the Foodmart cubes (size perhaps?).
Maybe I stumbled on some undocumented limit.
Another thing: In HOLAP mode with 100% performance gain it still computes aggregation data in Oracle. I know this, because when I shut Oracle I get #value errors. I thougt 100% means 'all aggregations in Analysis Services'. Also I'm often exactly 1 above the exact value with the Distinct Counts, because NULL seams to count a distinct value.
I rfead somewhere that Analysis Services transform a NULL into a 0 when one of the other measures in the source table is not NULL.
Is that correct, and if so, is there a patch for that? Otherwise I must define fact-views for each Distinct Count measure with "WHERE ... IS NOT NULL".

Development Server : PIII 500, 512 MB memory
(should eat this 7.5MB cube for breakfast)
Relational database: Oracle 8.1.7.3
Analysis Services : Service Pack 2
Fact table: : 69000 rows
Measures: : 1 Distinct Count
Dimension data : 1 3000 members, 1 level
2 69000 members, divided in 7 levels
3 Price-dimension (hierarchy 1)
4 Price-dimension (hierarchy 2)
Cube in MB 7.5 MB (in both HOLAP and MOLAP mode)

Edwin

history of Process info

i think i might a have a slight clue about this but need help...
in SQL Server Enterprise Manager -- Management -- Current Activity --
Process Info i can see the current info for SPIDs and users and all sorts of
great info.
How can i view that same info from yesterday at a specific time?
thanks a million.
You could use a log reader tool of which there are many to choose eg
Lumigent's LogExplorer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||"Paul Ibison" wrote:

> You could use a log reader tool of which there are many to choose eg
> Lumigent's LogExplorer.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
> Lumgent wants $995
|||Ok there's a new low-cost ($195) alternative from Redgate:
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm.
BTW I'm assuming that you actually have the log as a backup, or have been
using full recovery mode. if you've used simple then this strategy won't
work for the particular transaction you're looking for.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

history of Process info

i think i might a have a slight clue about this but need help...
in SQL Server Enterprise Manager -- Management -- Current Activity --
Process Info i can see the current info for SPIDs and users and all sorts of
great info.
How can i view that same info from yesterday at a specific time?
thanks a million.You could use a log reader tool of which there are many to choose eg
Lumigent's LogExplorer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||"Paul Ibison" wrote:

> You could use a log reader tool of which there are many to choose eg
> Lumigent's LogExplorer.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
> Lumgent wants $995|||Ok there's a new low-cost ($195) alternative from Redgate:
http://www.red-gate.com/products/SQ...scue/index.htm.
BTW I'm assuming that you actually have the log as a backup, or have been
using full recovery mode. if you've used simple then this strategy won't
work for the particular transaction you're looking for.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

High memory Utilization

The SQL Process was using 80% of the memory. I configured
it to use half of the memory so now its using about 55% of
it. SQL is running on a duel xeon 1200 ghz box and really
isnt getting hit that much. I dont know where to start
looking so any ideas would be helpful.If you have it configured at 50%, it makes "sense" that it is at 55% =consumption because there is some additional overhead that is not =included in the 50% cap. I know, it is a bit strange.
High memory usage does not necessarily indicate a problem or a memory =leak. SQL Serveris designed to grab memory and use it. It is only supposed to =relese
memory if other applications on the same box need it.
You may find this information helpful:
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;q321363
Does SQL Server have memory leaks? How can I tell? Why is SQL Server =using so much memory?
http://www.mssqlserver.com/faq/troubleshooting-memoryleak.asp
-- Keith
"Eddie" <anonymous@.discussions.microsoft.com> wrote in message =news:0f0d01c3aa07$bef276c0$a501280a@.phx.gbl...
> The SQL Process was using 80% of the memory. I configured > it to use half of the memory so now its using about 55% of > it. SQL is running on a duel xeon 1200 ghz box and really > isnt getting hit that much. I dont know where to start > looking so any ideas would be helpful.|||Keith
With regards to memory usage, I have 4GB of RAM and sqlservr.exe is using about 1.8GB or so Taskmgr shows it using. My server is NOT a heavy transaction SQL server, but I need the RAM for IIS
Can't I force throttle it back via Ent. Mgr to say like 1GB of RAM?
SQL is supposed to release RAM when not used, but I don't think it releases immediately which might cause the memory hits on my IIS service running? What do you think? Thanks
Steve|||You cam limit the amount of RAM that SQL Server uses. Probably the =easiest method would be to use the Enterprise Manager GUI. You could =also use sp_configure if you are comfortable with that method.
SQL Server will release memory ONLY if it needs to. It does not release =memory when SQL Server is not being used. The database server caches =data and query plans to memory and will only release it if other apps on =the box need it.
-- Keith
"steve schroeder" <anonymous@.discussions.microsoft.com> wrote in message =news:1F515B86-6AC2-49C8-B36D-FCF15CBE4FF5@.microsoft.com...
> Keith;
> > With regards to memory usage, I have 4GB of RAM and sqlservr.exe is =using about 1.8GB or so Taskmgr shows it using. My server is NOT a =heavy transaction SQL server, but I need the RAM for IIS.
> Can't I force throttle it back via Ent. Mgr to say like 1GB of RAM? > SQL is supposed to release RAM when not used, but I don't think it =releases immediately which might cause the memory hits on my IIS service =running? What do you think? Thanks.
> > Stevesql

High CPU -- identify which query ties up cpu

Hello,
I'm running SQL2K enterprise, recently faced constant high cpu usage by
sqlsrvr process.
How can I identify which query or queries tie up cpu? I mean something more
precise and comprehensible than cpu column in sp_who2.
thanks for any help,
Gary.Gary
I'd run SQL Server profiler to identify long running queries , there are
some events liek CPU,Duration and then try to speed them up
http://www.sql-server-performance.c...or_counters.asp
http://www.sql-server-performance.c...counters_io.asp
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:5079326B-B881-4728-B042-0448C1105D71@.microsoft.com...
> Hello,
> I'm running SQL2K enterprise, recently faced constant high cpu usage by
> sqlsrvr process.
> How can I identify which query or queries tie up cpu? I mean something
> more
> precise and comprehensible than cpu column in sp_who2.
> thanks for any help,
> Gary.

High CPU -- identify which query ties up cpu

Hello,
I'm running SQL2K enterprise, recently faced constant high cpu usage by
sqlsrvr process.
How can I identify which query or queries tie up cpu? I mean something more
precise and comprehensible than cpu column in sp_who2.
thanks for any help,
Gary.Gary
I'd run SQL Server profiler to identify long running queries , there are
some events liek CPU,Duration and then try to speed them up
http://www.sql-server-performance.com/performance_monitor_counters.asp
http://www.sql-server-performance.com/performance_monitor_counters_io.asp
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:5079326B-B881-4728-B042-0448C1105D71@.microsoft.com...
> Hello,
> I'm running SQL2K enterprise, recently faced constant high cpu usage by
> sqlsrvr process.
> How can I identify which query or queries tie up cpu? I mean something
> more
> precise and comprehensible than cpu column in sp_who2.
> thanks for any help,
> Gary.