Database Reference
In-Depth Information
We also need to consider what size our partitions should be. In general between 5
and 60 million rows per partition, or up to around 3 GB, is a good size. If you have
a measure group with a single partition of below 5 million rows then don't worry,
it will perform very well, but it's not worth dividing it into smaller partitions. It's
equally possible to get good performance with larger partitions. It's also best to avoid
having too many partitions as well - if you have more than a thousand it may make
SQL Management Studio and SSDT slow to respond, and it may be worth creating
fewer, larger partitions assuming these partitions stay within the size limits for a
single partition we've just given.
A good reference for performance tuning your partitioning strategy is the SQL
Server 2008 R2 Analysis Services Performance Guide which is available as follows:
http://tinyurl.com/ssasR2perf .
Automatically generating large numbers of Partitions
When creating a measure group for the first time, it's likely you'll
already have a large amount of data and may need to create a
correspondingly large number of partitions for it. Clearly the last thing
you'll want to do is create tens or hundreds of partitions manually and
it's worth knowing some tricks to create these partitions automatically.
One method involves taking a single partition, scripting it out to
XMLA, and then pasting and manipulating this in Excel, as detailed
here: http://tinyurl.com/generatepartitions . The Analysis
Services Stored Procedure Project also contains a set of functions
for creating partitions automatically based on MDX set expressions:
http://tinyurl.com/autopartition . Management of partitions
once the cube has gone into production is discussed in Chapter 11 ,
Monitoring Cube Performance and Usage .
Unexpected Partition scans
Even when you have configured your partitions properly it's sometimes the case
that Analysis Services will scan partitions that you don't expect it to be scanning
for a particular query. If you see this happening (and we'll talk about how you can
monitor partition usage later in this chapter) the first thing to determine is whether
these extra scans are making a significant contribution to your query times. If they
aren't, it's probably not worth worrying about; if they are, there are some things to
try to attempt to stop it happening.
 
Search WWH ::




Custom Search