Reason for multiple filegroups/ files?

SQL2K
SP4
If my data is spread accross a Raid array, is there any real reason to use
multiple filegroups/ files? Will I see any performance benefit? Will I only
see it on VLDB's. If I do use them, is there a good way to group them
logically?
TIA, ChrisRChrisR
>Will I see any performance benefit?
Probably, if you put the filegroup on another PHYSICAL disk
>Will I only
> see it on VLDB's. If I do use them, is there a good way to group them
> logically?
Yes , it makes sence only for VLDB
Look, you will have to investigate it carefuly, I mean how you are going
to restore in case of failure or somethinmg like that , it is not so easy
to maintain and I have my doubt that nowadays we can really gain
perfiomance benefit from creating a new file group . Perhaps you might
think about adding a new datafile .NDF and seeing how it works
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:%23hGlHLPKHHA.1912@.TK2MSFTNGP03.phx.gbl...
> SQL2K
> SP4
> If my data is spread accross a Raid array, is there any real reason to use
> multiple filegroups/ files? Will I see any performance benefit? Will I
> only
> see it on VLDB's. If I do use them, is there a good way to group them
> logically?
> TIA, ChrisR
>|||Add on ; please take a look into below URL's. But to make use of the full
advantage you may need multiple disk controller as Uri suggested.
http://www.databasejournal.com/features/mssql/article.php/1439801
http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=42&rl=1
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u4aAfSPKHHA.3936@.TK2MSFTNGP02.phx.gbl...
> ChrisR
>>Will I see any performance benefit?
> Probably, if you put the filegroup on another PHYSICAL disk
>>Will I only
>> see it on VLDB's. If I do use them, is there a good way to group them
>> logically?
> Yes , it makes sence only for VLDB
>
> Look, you will have to investigate it carefuly, I mean how you are going
> to restore in case of failure or somethinmg like that , it is not so easy
> to maintain and I have my doubt that nowadays we can really gain
> perfiomance benefit from creating a new file group . Perhaps you might
> think about adding a new datafile .NDF and seeing how it works
>
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:%23hGlHLPKHHA.1912@.TK2MSFTNGP03.phx.gbl...
>> SQL2K
>> SP4
>> If my data is spread accross a Raid array, is there any real reason to
>> use
>> multiple filegroups/ files? Will I see any performance benefit? Will I
>> only
>> see it on VLDB's. If I do use them, is there a good way to group them
>> logically?
>> TIA, ChrisR
>>
>|||At the very least I always recommend creating one secondary filegroup to
place all the user objects and leave just the system objects in the primary
filegroup. This makes it cleaner and easier to do piecemeal restores if
needed. From there it depends on how you use your data and what your
hardware config is like. If you want to separate different groups of objects
such as Non-clustered indexes from the Clustered / tables onto different
drive arrays you need at least two filegroups. If you know this is something
you may do in the future but aren't now it still may make sense to separate
them into multiple FG's to make the move easier down the road. If the db is
relatively small, your hardware is decent you may never see any advantages
of multiple files or filegroups.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:%23hGlHLPKHHA.1912@.TK2MSFTNGP03.phx.gbl...
> SQL2K
> SP4
> If my data is spread accross a Raid array, is there any real reason to use
> multiple filegroups/ files? Will I see any performance benefit? Will I
> only
> see it on VLDB's. If I do use them, is there a good way to group them
> logically?
> TIA, ChrisR
>|||Thanks everyone, this is all as I suspected.
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:%23hGlHLPKHHA.1912@.TK2MSFTNGP03.phx.gbl...
> SQL2K
> SP4
> If my data is spread accross a Raid array, is there any real reason to use
> multiple filegroups/ files? Will I see any performance benefit? Will I
only
> see it on VLDB's. If I do use them, is there a good way to group them
> logically?
> TIA, ChrisR
>

Real Time ROLAP different then ROLAP

What is the benefit of setting the proactive cache to Real Time ROLAP. Wouldn't this be the same as ROLAP. Confused on why there is such an option.

Books online doesn't seem to answer it clearly.

Proactive caching adds the ability to automatically detect changes in source data and update Analysis Services objects appropriately. In the case of Real-Time ROLAP (which was also an option in AS 2000), the AS server can detect data changes (via events, polling or client-initiated notification) and invalidate its in-memory cache - without proactive caching, explicit processing would be needed to drop the cache.

http://msdn2.microsoft.com/en-us/library/ms175646.aspx

>>

Real Time ROLAP

OLAP is in real time. Detail data and aggregations are stored in relational format. The server listens for notifications when data changes and all queries reflect the current state of the data (zero latency). This setting would typically be used for a data source with very frequent and continuous updates when the very latest data is always required by users.

>>

|||

Thanks. I'm getting closer to understanding.

How can the latency be zero? If in the Real Time ROLAP case, reprocessing of the cube is required to modify aggregations (assuming non 0% aggregation). This would imply not zero latency, but the latency it takes to reprocess the cube. To me it seems like Real Time MOLAP is more likely to have the latest data over Real Time ROLAP. What am I missing?

|||

I don't believe that there is a standard setting such as Real Time MOLAP - with pure MOLAP, there will be some latency while the cache is rebuilt. However, proactive caching policy can cause queries to be directed to the fact table (ROLAP mode) while the MOLAP cache is being rebuilt - but this would no longer be pure MOLAP.

According to BOL below, no aggregations are stored for Real Time OLAP, so there is probably no significant reprocessing time involved when data is updated - [MS] folks could provide more detailed info:

http://msdn2.microsoft.com/en-us/library/ms174915(SQL.90).aspx

>>

Partition Storage (SSAS)

...

Real-time ROLAP is typically used when clients want to see changes immediately. No aggregations are stored with real-time ROLAP.

>>

|||

Hi Deepak

Could you please send me some code sample and "how to " for send client notification to Analysis Server?

Thanks alot.
Regards;
Rakesh

Real Time ROLAP different then ROLAP

What is the benefit of setting the proactive cache to Real Time ROLAP. Wouldn't this be the same as ROLAP. Confused on why there is such an option.

Books online doesn't seem to answer it clearly.

Proactive caching adds the ability to automatically detect changes in source data and update Analysis Services objects appropriately. In the case of Real-Time ROLAP (which was also an option in AS 2000), the AS server can detect data changes (via events, polling or client-initiated notification) and invalidate its in-memory cache - without proactive caching, explicit processing would be needed to drop the cache.

http://msdn2.microsoft.com/en-us/library/ms175646.aspx

>>

Real Time ROLAP

OLAP is in real time. Detail data and aggregations are stored in relational format. The server listens for notifications when data changes and all queries reflect the current state of the data (zero latency). This setting would typically be used for a data source with very frequent and continuous updates when the very latest data is always required by users.

>>

|||

Thanks. I'm getting closer to understanding.

How can the latency be zero? If in the Real Time ROLAP case, reprocessing of the cube is required to modify aggregations (assuming non 0% aggregation). This would imply not zero latency, but the latency it takes to reprocess the cube. To me it seems like Real Time MOLAP is more likely to have the latest data over Real Time ROLAP. What am I missing?

|||

I don't believe that there is a standard setting such as Real Time MOLAP - with pure MOLAP, there will be some latency while the cache is rebuilt. However, proactive caching policy can cause queries to be directed to the fact table (ROLAP mode) while the MOLAP cache is being rebuilt - but this would no longer be pure MOLAP.

According to BOL below, no aggregations are stored for Real Time OLAP, so there is probably no significant reprocessing time involved when data is updated - [MS] folks could provide more detailed info:

http://msdn2.microsoft.com/en-us/library/ms174915(SQL.90).aspx

>>

Partition Storage (SSAS)

...

Real-time ROLAP is typically used when clients want to see changes immediately. No aggregations are stored with real-time ROLAP.

>>

|||

Hi Deepak

Could you please send me some code sample and "how to " for send client notification to Analysis Server?

Thanks alot.
Regards;
Rakesh