Visualizing Data File Layout II

re: Visualizing Data File Layout II

Wolfgang — Mon, 28 Jan 2013 09:28:12 GMT

Hello there,

this is a very great article. Short, easy to understand and really interesting.

The only thing I cannot agree with is that you call the rebuild-overhead a not "wasted" space. As so often it depends:

In our production system some of our tables need some hundreds GB(!) space on a high availability storage. As this is really expensive it IS "wasted" space for us. That's why we do a reorg instead of a rebuild.

We already thought rebuilding works like you showed here but it's great to get the acknowledgment.

Keep on writing :-)

re: Visualizing Data File Layout II

merrillaldrich — Mon, 28 Jan 2013 13:37:22 GMT

Thanks! True, a database with one or a few very large tables can pose a challenge because so much more overhead space is needed if you reindex, compared to a database with many smaller tables. The two ways I know to mitigate are partitioning (individual partitions can be rebuilt) or, as you say, reorg. If partitioning isn't an option, I still prefer to have the overhead available - imagine, for example, if you needed to make a change to the CI.

Visualizing Data File Layout III

Merrill Aldrich — Tue, 29 Jan 2013 05:45:52 GMT

This is part three of a blog series illustrating a method to render the file structure of a SQL Server