Re: Fragmentation opinion wanted.

> Well I guess I should have been more verbose as well. Loading was just > an example of data access where fragmentation elimination could hurt > performance. But consider a data warehouse, reading a large percentage > of a large table. You want to hit as many spindles as possible in order > to maximise performance. Not necessarily. You want to hit as many spindles of RELEVANT data as possible to maximize performance. > It depends on your queries. Assuming that fragment elimination will help > performance is a generalisation that has caught many people out. Pushing > a large number of threads onto a single disk, rather than spreading them > out over many can hurt performance, depending on the type of operation. > There, is that verbose enough? :-) Yeah, but it doesn't really speak to what I'm saying. The goal would ideally be to get a combination of parallelism AND fragment elimination. Not easy to do with 7.x or 9.x engines, but the hash scheme in XPS is perfect for this. > And I guess you could generalise now that fragment elimination is good > for OLTP and bad for DSS. ;-) Well, I suppose that's closer to it for > most sites, but not all. I say it's good for both, depending on the data itself, of course. Here's a situation where it is good for DSS, and I'm going to drastically generalize here. Let's say the disks we're using here can handle no more than one I/O thread each. I run monthly reports summarizing by region. In this particular company, say we have five regions. We have five physical disks to work with. Each region has 100 million rows in 1 GB of data. A query on round robin fragmentation to five dbspaces, one per disk means: * Five parallel threads each reading in 100 million rows. 5 GB total. But all you need is 100 million rows in 1 GB. So round-robin wins on load time, but basically sucks for DSS queries. Sure you get some parallelism, but no elimination Okay, so let's try expression-based fragmentation (on region number). One region per disk/dbspace. My region-based query gives: * One thread reading in 100 million rows. 1 GB total. So we get elimination, but no parallelism. Easier on the system, but probably not a significant time saver over round-robin on an idle system. So finally, we get hash fragmentation. We'll divvy up 5 dbspaces per disk for a total of 25 dbspaces of 200MB each: * Fragment expression will use region number for expression and then round robin each region across 5 dbspaces (one on each disk). So I run my report and here's what happens: Starting with 25 dbspaces, I eliminate 20 of them--benefit of expression-based fragmentation. I'm left with 5 dbspaces, each of those on a separate disk--benefit of round-robin fragmentation. So instead of the first two options, each of which involves a thread scanning 100 million rows / 1 GB of data, I have: Five parallel threads, each reading from a separate disk and each one only scanning 20 million rows / 2 MB each. All things being equal, you just cut your query time to a FIFTH of round robin fragmentation. YMMV, but any load time you lose with hash/expression fragmentation is easily made up in query savings. Now there are obviously a boatload of factors that can influence this (adminstrative overhead, system load, number of querys, total refresh vs. CDC, etc)...that's why it gets dangerous to generalize, especially from a third-party vendor who obviously doesn't understand the database architecture. --Chuck