re: Visualizing Data File Layout III

Neeraj Mittal — Tue, 29 Jan 2013 13:07:47 GMT

Nice explanation of complex topic

re: Visualizing Data File Layout III

csm — Tue, 29 Jan 2013 17:27:42 GMT

Great tool for academic purposes!! Nothing better than a graphic to explain complex topics.

Hope that you can share this tool with us.

re: Visualizing Data File Layout III

merrillaldrich — Wed, 30 Jan 2013 03:28:57 GMT

Thanks guys. The public version is in the works.

re: Visualizing Data File Layout III

rivantsov — Sun, 03 Feb 2013 06:17:03 GMT

well, nice post but... sort of accepted truth for an experienced data guy. We use Guids for PK exclusively, but knowing the effect of random clustering, we explicitly specify clustered index and it starts with some natural sequential field like "CreateDateTime". So the immediate response to your concerns is "Guids as PK are OK, just make sure you add sane clustered index". This seems like a complete solution, but... I myself have some concerns - it does not seem to always work well.

Example: two tables: Order, OrderLine; parent/child, both use Guids for PK, and OrderLine references Order thru OrderId (Guid). Good practice is to base clustered index of OrderLine on OrderId - so that all lines for an order will be together, and will improve perf when we query for order with lines. But now we have this Guid as clustered index again - it means that chunks of lines will be allocated on disk with fragmentation as you describe. It will be interesting to find a solution... I'm thinking of even maybe adding artificial 'clustering' auto-inc int column that is base of clustered index and is propagated from parent to child - so child clusters as well. any ideas?

And abandoning Guids is not an option - our app does a lot of sync-ing of databases, and autoinc/identify for PK is not an option.

Another interesting question - what's the impact of Guid randomness on 'index' performance?

re: Visualizing Data File Layout III

rivantsov — Sun, 03 Feb 2013 06:44:22 GMT

oh, sorry, I misunderstood. you're demoing the tool... it's not about guids/pk per se