 | |
Analysis Service hangs when processing cubes requested by DSO
microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Analysis Service hangs when processing cubes requested by DSO -
07-09-2003
, 12:37 PM
#2
| |||
| |||
|
|
I have a DSO app that builds three cubes and then process them. The fact tables and dimensions are stored in SQL Server. Recently Analysis Service would hang when processing the cubes. Based on trace from SQL Profiler (because MSAS is retrieving dimension and fact data from the data source), it is trying to process one dimension that has 900,000 members. Before that dimension, it processed a similar one successfully. The other dimensions are much smaller. The Analysis Service process is taking 1.8 GB memory, but no cpu activity. I let it run for an hour and killed it. However, if I process the cubes from Analysis Manager, everything works fine within 30 minutes. MSAS SP3 is used. I'm wondering if that has something to do with it. Any comments and suggestions will be appreciated. Yubo Fan |
#3
| |||
| |||
|
|
Yubo, You are very close to the 2GB limit for Analysis Services. Have you altered the boot.ini file to allow Analysis Services to use 3 GB of memory? Aside from that, I'd recommend that in your code that you process each dimension by itself, otherwise if you just issue a process statement at the database level all the dimensions will be processed as a single transaction which means that you'll need to be able to keep a copy of all of them in memory at once. If you're processing cubes via DSO, I would do this in the following order. 1. Create the database. 2. Process each dimension by itself. 3. Process each cube by itself. 4. Process each virtual cube by itself. Sean -- Sean Boon SQL Server BI Product Unit **This posting is provided AS IS, with no warranties, and confers no rights.** "Yubo Fan" <yubo.fan (AT) sas (DOT) com> wrote in message news:behjsf$1n5$1 (AT) license1 (DOT) unx.sas.com... I have a DSO app that builds three cubes and then process them. The fact tables and dimensions are stored in SQL Server. Recently Analysis Service would hang when processing the cubes. Based on trace from SQL Profiler (because MSAS is retrieving dimension and fact data from the data source), it is trying to process one dimension that has 900,000 members. Before that dimension, it processed a similar one successfully. The other dimensions are much smaller. The Analysis Service process is taking 1.8 GB memory, but no cpu activity. I let it run for an hour and killed it. However, if I process the cubes from Analysis Manager, everything works fine within 30 minutes. MSAS SP3 is used. I'm wondering if that has something to do with it. Any comments and suggestions will be appreciated. Yubo Fan |
#4
| |||
| |||
|
|
From: "Yubo Fan" <yubo.fan (AT) sas (DOT) com Newsgroups: microsoft.public.sqlserver.olap Subject: Re: Analysis Service hangs when processing cubes requested by DSO Date: Wed, 9 Jul 2003 13:18:41 -0700 Organization: SAS Institute, Inc. Lines: 67 Message-ID: <behtb2$6fs$1 (AT) license1 (DOT) unx.sas.com References: <behjsf$1n5$1 (AT) license1 (DOT) unx.sas.com e0p$02kRDHA.2316 (AT) tk2msftngp13 (DOT) phx.gbl NNTP-Posting-Host: d7925.na.sas.com X-Trace: license1.unx.sas.com 1057781922 6652 172.25.126.77 (9 Jul 2003 20:18:42 GMT) X-Complaints-To: usenet (AT) unx (DOT) sas.com NNTP-Posting-Date: 9 Jul 2003 20:18:42 GMT X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed 00.sul.t-online.de!t-onlin |
ing!In.nntp.be!vienna7.his.com!attws1!ip.att.net!l amb.sas.com!newshost!not-f
or-mail
Quote:
|
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.olap:40537 X-Tomcat-NG: microsoft.public.sqlserver.olap How do I change boot.ini to allow MSAS to use 3GB? "Sean Boon [MS]" <seanboon (AT) online (DOT) microsoft.com> wrote in message news:e0p$02kRDHA.2316 (AT) tk2msftngp13 (DOT) phx.gbl... Yubo, You are very close to the 2GB limit for Analysis Services. Have you altered the boot.ini file to allow Analysis Services to use 3 GB of memory? Aside from that, I'd recommend that in your code that you process each dimension by itself, otherwise if you just issue a process statement at the database level all the dimensions will be processed as a single transaction which means that you'll need to be able to keep a copy of all of them in memory at once. If you're processing cubes via DSO, I would do this in the following order. 1. Create the database. 2. Process each dimension by itself. 3. Process each cube by itself. 4. Process each virtual cube by itself. Sean -- Sean Boon SQL Server BI Product Unit **This posting is provided AS IS, with no warranties, and confers no rights.** "Yubo Fan" <yubo.fan (AT) sas (DOT) com> wrote in message news:behjsf$1n5$1 (AT) license1 (DOT) unx.sas.com... I have a DSO app that builds three cubes and then process them. The fact tables and dimensions are stored in SQL Server. Recently Analysis Service would hang when processing the cubes. Based on trace from SQL Profiler (because MSAS is retrieving dimension and fact data from the data source), it is trying to process one dimension that has 900,000 members. Before that dimension, it processed a similar one successfully. The other dimensions are much smaller. The Analysis Service process is taking 1.8 GB memory, but no cpu activity. I let it run for an hour and killed it. However, if I process the cubes from Analysis Manager, everything works fine within 30 minutes. MSAS SP3 is used. I'm wondering if that has something to do with it. Any comments and suggestions will be appreciated. Yubo Fan |
#5
| |||
| |||
|
|
From: "Yubo Fan" <yubo.fan (AT) sas (DOT) com Newsgroups: microsoft.public.sqlserver.olap Subject: Re: Analysis Service hangs when processing cubes requested by DSO Date: Wed, 16 Jul 2003 14:24:30 -0700 Organization: SAS Institute, Inc. Lines: 195 Message-ID: <bf4fqe$4j0$1 (AT) license1 (DOT) unx.sas.com References: <behjsf$1n5$1 (AT) license1 (DOT) unx.sas.com e0p$02kRDHA.2316 (AT) tk2msftngp13 (DOT) phx.gbl> <behtb2$6fs$1 (AT) license1 (DOT) unx.sas.com |
Quote:
|
NNTP-Posting-Host: d7925.na.sas.com X-Trace: license1.unx.sas.com 1058390670 4704 172.25.126.77 (16 Jul 2003 21:24:30 GMT) X-Complaints-To: usenet (AT) unx (DOT) sas.com NNTP-Posting-Date: 16 Jul 2003 21:24:30 GMT X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed 00.sul.t-online.de!t-onlin |
ing!In.nntp.be!vienna7.his.com!attws1!ip.att.net!l amb.sas.com!newshost!not-f
or-mail
Quote:
|
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.olap:40808 X-Tomcat-NG: microsoft.public.sqlserver.olap This issue has not been totally resolved. More disoveries are as follows: 1. We're using an explicit transaction around all processing, so MSAS only commits once after processing all dimensions. 2. MSAS SP2 can finish the processing without problem, SP3 or SP3a would just hang while processing the 3rd large dimension 3. Commenting out the explicit transaction would enable SP3 to finish 4. Memory doesn't seem to be the problem. After cleaning up other cubes and dimensions, I was able to gain almost 500 MB. MSAS hangs while it's taking 1.5 GB 5. When MSAS SP3 hangs, nobody can connect to it. I don't like the common suggestion such as "have you tried 3GB". I hope we can really understand the problem before heading into the 3GB land. I'm only processing dimensions here. I have NOT processed any cubes yet. If VLDM techinque is used (in fact VLDM processes are launched), why would MSAS main process take so much memory? If memory is not enough, I would expect a clear message. At this point, my workaround is not to use transaction, but I hope somebody would be interested in finding out what MSAS SP3 is doing to cause such a problem. - Yubo Fan "William Wang[MSFT]" <v-rxwang (AT) online (DOT) microsoft.com> wrote in message news:WXIdVatRDHA.2724 (AT) cpmsftngxa06 (DOT) phx.gbl... Hi Yubo, Since the issue does not occur with Analysis Manager, but with DSO, it is usually complex to troubleshoot on newsgroups. I would suggest that you open an incident with Microsoft Product Support Services, so that we can have dedicated engineer to assist you. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, please go to the following address on the World Wide Web: http://support.microsoft.com/directory/overview.asp To enable Application Memory Tuning, you can modify the boot.ini file by adding /3GB parameter to the ARC path. Here's an example of a Boot.ini file with the /3GB parameter added. [boot loader] timeout=30 default=multi(0)disk(0)rdisk(0)partition(1)\WINNT [operating systems] multi(0)disk(0)rdisk(0)partition(1)\WINNT = "Windows 2000 Advanced Server" /fastdetect /3GB multi(0)disk(0)rdisk(0)partition(1)\WINNT = "NT 4.0 Server Enterprise Edition" /fastdetect /3GB After you enable Application Memory Tuning, you must instruct Analysis Services to use the additional memory, because Analysis Services does not detect that additional memory is available. You may make it by modifying the registry. Before modifying the registry, please make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. You may locate the registry key HKEY_LOCAL_MACHINE\Software\Microsoft\Olap Server\Current Version, edit the HighMemoryLimit registry value, and then set it to a decimal value that is approximately 2.7 GB in bytes. Do not set this value to the full 3 GB because this does not permit the cleaner thread enough time to react to a low memory situation. This value corresponds to Memory Conservation Threshold. I suggest that you refer the Microsoft Knowledge Base Article - 295443 INF: How To Enable Analysis Server to Use 3 GB of RAM for more information. http://support.microsoft.com/?id=295443 This posting is provided "AS IS" with no warranties, and confers no rights. Sincerely, William Wang Microsoft Partner Online Support -------------------- | From: "Yubo Fan" <yubo.fan (AT) sas (DOT) com | Newsgroups: microsoft.public.sqlserver.olap | Subject: Re: Analysis Service hangs when processing cubes requested by DSO | Date: Wed, 9 Jul 2003 13:18:41 -0700 | Organization: SAS Institute, Inc. | Lines: 67 | Message-ID: <behtb2$6fs$1 (AT) license1 (DOT) unx.sas.com | References: <behjsf$1n5$1 (AT) license1 (DOT) unx.sas.com e0p$02kRDHA.2316 (AT) tk2msftngp13 (DOT) phx.gbl | NNTP-Posting-Host: d7925.na.sas.com | X-Trace: license1.unx.sas.com 1057781922 6652 172.25.126.77 (9 Jul 2003 20:18:42 GMT) | X-Complaints-To: usenet (AT) unx (DOT) sas.com | NNTP-Posting-Date: 9 Jul 2003 20:18:42 GMT | X-Priority: 3 | X-MSMail-Priority: Normal | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106 | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed 00.sul.t-online.de!t-onlin e.de!npeer.de.kpn-eurorings.net!news-out.nuthinbutnews.com!propagator2-sterl ing!In.nntp.be!vienna7.his.com!attws1!ip.att.net!l amb.sas.com!newshost!not-f or-mail | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.olap:40537 | X-Tomcat-NG: microsoft.public.sqlserver.olap | | How do I change boot.ini to allow MSAS to use 3GB? | | "Sean Boon [MS]" <seanboon (AT) online (DOT) microsoft.com> wrote in message | news:e0p$02kRDHA.2316 (AT) tk2msftngp13 (DOT) phx.gbl... | > Yubo, | | > You are very close to the 2GB limit for Analysis Services. Have you | altered | > the boot.ini file to allow Analysis Services to use 3 GB of memory? | > Aside from that, I'd recommend that in your code that you process each | > dimension by itself, otherwise if you just issue a process statement at | the | > database level all the dimensions will be processed as a single | transaction | > which means that you'll need to be able to keep a copy of all of them in | > memory at once. | | > If you're processing cubes via DSO, I would do this in the following | order. | | > 1. Create the database. | > 2. Process each dimension by itself. | > 3. Process each cube by itself. | > 4. Process each virtual cube by itself. | | > Sean | | | > -- | > Sean Boon | > SQL Server BI Product Unit | | > **This posting is provided AS IS, with no warranties, and confers no | > rights.** | | | > "Yubo Fan" <yubo.fan (AT) sas (DOT) com> wrote in message | > news:behjsf$1n5$1 (AT) license1 (DOT) unx.sas.com... | > > I have a DSO app that builds three cubes and then process them. The fact | > > tables and dimensions are stored in SQL Server. Recently Analysis | Service | > > would hang when processing the cubes. Based on trace from SQL Profiler | > > (because MSAS is retrieving dimension and fact data from the data | source), | > > it is trying to process one dimension that has 900,000 members. Before | > that | > > dimension, it processed a similar one successfully. The other dimensions | > are | > > much smaller. The Analysis Service process is taking 1.8 GB memory, but | no | > > cpu activity. I let it run for an hour and killed it. | | > > However, if I process the cubes from Analysis Manager, everything works | > fine | > > within 30 minutes. | | > > MSAS SP3 is used. I'm wondering if that has something to do with it. | | > > Any comments and suggestions will be appreciated. | | > > Yubo Fan | | | | | | | |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.