How to avoid sql server page fragmentation in this scenario?

Question

I want to order SQL Inserts into a table to optimize page use by avoiding fragmentation as much as possible. I will be running a .net Windows Service, which every 2 hours will take some data from a database and optimize it for future queries. A varchar(6000) column is involved, though I estimate it will rarely go beyond 4000 bytes. In fact, this column can vary normally between 600 and 2400. It's 6000 to help avoiding truncating errors. Still I can control that column size through .net. There won't ever be updates nor delete. Just selects (and inserts every 2 hours). There will be around 1000 inserts every 2 hours.

I'm using Sql Server 2005. Page size are said to be 8096 bytes. I need to insert rows in a table. Given the size of rows, between 4 and 12 rows could fit in a page.

So from .net I will read data from database, store it in memory, (use some clustering algorithm maybe?), and the insert around 1000 rows.

I was wondering if there is a way to avoid or minimize page fragmentation in this scenario.

Answer 1

Is the table a btree or a heap? Do you have a clustered index on it? If yes, then what column is the clustered index on, and how is the column value computed at insert?

Why do you care about fragmentation to start with? Space consideration or read ahead performance? For space, you should skip SQL 2005 and go to SQL 2008 for Page compression. For read ahead, it would be worth investigating why you need large read aheads to start with.

Overall, index fragmentation is more of an overhiped bru-ha-ha everyone talks about but very few really understand. There are many many more aveanues to pursue before fragmentation becomes the real bottleneck.