 | |
Re: URGENT : CRASH on Analysis Services SQL Server 2000 on PRODUCT
microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: URGENT : CRASH on Analysis Services SQL Server 2000 on PRODUCT -
05-03-2005
, 01:01 PM
|
Hi. I agree with Howard. 9M rows is not that much. We successfully implemented a cube with 250M rows, with 6 years of data. But I had to create monthly partitions and play with the memory buffer size. Initially I tried to create quartely partitions, but it was using to much the paging files. Partitioning the cube monthly decreased the build time in 3 hours. I created custom aggregations instead of using the standard ones (the ones msas recommends). This improved the queries performance from minutes to seconds. I'm also using the parallelprocess utility to run 2 partitions at the time. With the combinations of parallelprocess + monthly partitions + custom aggregations I was able to drop the build time from 11 hrs to 6 hrs. Hope this info is helpful for you. Ercilia Hirata Coca-Cola Company "Howard Wild via SQLMonster.com" wrote: For this scenario I would suggest developing a partition strategy. Analysis services can easily cope with 9m records, however, your server may not be able to cope with all 9m in one chunk. Partitioning not only helps users queries return faster; it also breaks down the workload when building the cube, meaning it won?t use up so much resources (like memory) in one go. -- Message posted via http://www.sqlmonster.com |
#2
| |||
| |||
|
|
Hi. You talk about a ParallelProcesse Utility, where can i find this? Is it part of the AS installetion? Thanks. "Sampa2870" wrote: Hi. I agree with Howard. 9M rows is not that much. We successfully implemented a cube with 250M rows, with 6 years of data. But I had to create monthly partitions and play with the memory buffer size. Initially I tried to create quartely partitions, but it was using to much the paging files. Partitioning the cube monthly decreased the build time in 3 hours. I created custom aggregations instead of using the standard ones (the ones msas recommends). This improved the queries performance from minutes to seconds. I'm also using the parallelprocess utility to run 2 partitions at the time. With the combinations of parallelprocess + monthly partitions + custom aggregations I was able to drop the build time from 11 hrs to 6 hrs. Hope this info is helpful for you. Ercilia Hirata Coca-Cola Company "Howard Wild via SQLMonster.com" wrote: For this scenario I would suggest developing a partition strategy. Analysis services can easily cope with 9m records, however, your server may not be able to cope with all 9m in one chunk. Partitioning not only helps users queries return faster; it also breaks down the workload when building the cube, meaning it won?t use up so much resources (like memory) in one go. -- Message posted via http://www.sqlmonster.com |
#3
| |||
| |||
|
|
Hi, You can find it on the Microsoft Web page. I believe this is the latest version. http://www.microsoft.com/downloads/d...displaylang=en "Japm" <Japm (AT) discussions (DOT) microsoft.com> wrote in message news:EE5C6FBA-EF7D-4E45-B986-A270B1537C9B (AT) microsoft (DOT) com... Hi. You talk about a ParallelProcesse Utility, where can i find this? Is it part of the AS installetion? Thanks. "Sampa2870" wrote: Hi. I agree with Howard. 9M rows is not that much. We successfully implemented a cube with 250M rows, with 6 years of data. But I had to create monthly partitions and play with the memory buffer size. Initially I tried to create quartely partitions, but it was using to much the paging files. Partitioning the cube monthly decreased the build time in 3 hours. I created custom aggregations instead of using the standard ones (the ones msas recommends). This improved the queries performance from minutes to seconds. I'm also using the parallelprocess utility to run 2 partitions at the time. With the combinations of parallelprocess + monthly partitions + custom aggregations I was able to drop the build time from 11 hrs to 6 hrs. Hope this info is helpful for you. Ercilia Hirata Coca-Cola Company "Howard Wild via SQLMonster.com" wrote: For this scenario I would suggest developing a partition strategy. Analysis services can easily cope with 9m records, however, your server may not be able to cope with all 9m in one chunk. Partitioning not only helps users queries return faster; it also breaks down the workload when building the cube, meaning it won?t use up so much resources (like memory) in one go. -- Message posted via http://www.sqlmonster.com |
#4
| |||
| |||
|
|
Thanks a lot. Do you know where i can find more information about partiotiong and aggregations? I'm interested spaceilly in experieces about this topics. Thanks again. "David Botzenhart" wrote: Hi, You can find it on the Microsoft Web page. I believe this is the latest version. http://www.microsoft.com/downloads/d...displaylang=en "Japm" <Japm (AT) discussions (DOT) microsoft.com> wrote in message news:EE5C6FBA-EF7D-4E45-B986-A270B1537C9B (AT) microsoft (DOT) com... Hi. You talk about a ParallelProcesse Utility, where can i find this? Is it part of the AS installetion? Thanks. "Sampa2870" wrote: Hi. I agree with Howard. 9M rows is not that much. We successfully implemented a cube with 250M rows, with 6 years of data. But I had to create monthly partitions and play with the memory buffer size. Initially I tried to create quartely partitions, but it was using to much the paging files. Partitioning the cube monthly decreased the build time in 3 hours. I created custom aggregations instead of using the standard ones (the ones msas recommends). This improved the queries performance from minutes to seconds. I'm also using the parallelprocess utility to run 2 partitions at the time. With the combinations of parallelprocess + monthly partitions + custom aggregations I was able to drop the build time from 11 hrs to 6 hrs. Hope this info is helpful for you. Ercilia Hirata Coca-Cola Company "Howard Wild via SQLMonster.com" wrote: For this scenario I would suggest developing a partition strategy. Analysis services can easily cope with 9m records, however, your server may not be able to cope with all 9m in one chunk. Partitioning not only helps users queries return faster; it also breaks down the workload when building the cube, meaning it won?t use up so much resources (like memory) in one go. -- Message posted via http://www.sqlmonster.com |
#5
| |||
| |||
|
|
In the same page there is a performance tuning white paper that is really good. David "Japm" <Japm (AT) discussions (DOT) microsoft.com> wrote in message news:4117FD29-470C-42DF-AF7C-8C3F28C5D7FD (AT) microsoft (DOT) com... Thanks a lot. Do you know where i can find more information about partiotiong and aggregations? I'm interested spaceilly in experieces about this topics. Thanks again. "David Botzenhart" wrote: Hi, You can find it on the Microsoft Web page. I believe this is the latest version. http://www.microsoft.com/downloads/d...displaylang=en "Japm" <Japm (AT) discussions (DOT) microsoft.com> wrote in message news:EE5C6FBA-EF7D-4E45-B986-A270B1537C9B (AT) microsoft (DOT) com... Hi. You talk about a ParallelProcesse Utility, where can i find this? Is it part of the AS installetion? Thanks. "Sampa2870" wrote: Hi. I agree with Howard. 9M rows is not that much. We successfully implemented a cube with 250M rows, with 6 years of data. But I had to create monthly partitions and play with the memory buffer size. Initially I tried to create quartely partitions, but it was using to much the paging files. Partitioning the cube monthly decreased the build time in 3 hours. I created custom aggregations instead of using the standard ones (the ones msas recommends). This improved the queries performance from minutes to seconds. I'm also using the parallelprocess utility to run 2 partitions at the time. With the combinations of parallelprocess + monthly partitions + custom aggregations I was able to drop the build time from 11 hrs to 6 hrs. Hope this info is helpful for you. Ercilia Hirata Coca-Cola Company "Howard Wild via SQLMonster.com" wrote: For this scenario I would suggest developing a partition strategy. Analysis services can easily cope with 9m records, however, your server may not be able to cope with all 9m in one chunk. Partitioning not only helps users queries return faster; it also breaks down the workload when building the cube, meaning it won?t use up so much resources (like memory) in one go. -- Message posted via http://www.sqlmonster.com |
|
« 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.