7

So, every time I read that a data file shrink will cause fragmentation or a developer rave on that the 95% frag he sees is the cause of all life's misery, I wonder what the problem really is. We no longer dedicate a single disk for anything and instead carve LUNs out of auto-tiered pools of EFD, 15k, 10k spindles; however, I have never seen any test considering whether logical fragmentation means anything performance wise once we ignore the one disk test model.

Perhaps the bpool performance will suck more, but if I take as an axiom that my IO subsystem is still returning what I need in less than 20ms and further assume that every index I have is fragmented to hell, does it really matter? That is, in the world of terabyte indexes, is it cheaper to keep the fragmentation than to remedy it or is it even cheaper just to use a heap and build non-clustered indexes on it? I guess more study is needed; but I just wanted to throw the idea out here for discussion.

| improve this question | |
  • Best question I've seen on the site recently. Welcome, and +1. Looking forward to some quality answers. – Philᵀᴹ May 19 '13 at 18:27
  • 1
    I'll add that this is something that frustrates me these days - 10 years ago I'd dictate tablespace mappings (I'm an Oracle DBA) to clients and tell them what storage to buy for a hybrid OLTP/reporting system. I knew what disks they had and what configurations (RAID 5 for boring stuff, 0+1 for perf etc). Now, you're lucky if the "storage admin" even knows what has been presented to your DB host. Don't even get me started on thin provisioning! Anyway, SQL Server Question, so I'll leave it to the SQL Server people :) – Philᵀᴹ May 19 '13 at 18:31
  • I agree for Oracle or SQL Server. Mostly, we get a single storage array, all the IO is random whether we like it or not. So, if I can keep to a 20ms limit by throwing more spindles, bigger ports, or magic at it, then why the hell should I care about frag if my data is getting to me as fast as I need it. B-tree frag is what it is, but who cares assuming my IO is happening at the speed I need. I would even argue that the btree itself is going out of business, but I don;t want to start a war =-) – ooutwire May 19 '13 at 18:34
  • 1
    @JonSeigel However, where is Brent's evidence? This is the problem we face. All we have is some 4 year old blog post from Paul Randal poo-pooing us about shrinkfile; but his example is trite and totally meaningless in 2013. – ooutwire May 19 '13 at 19:48