Visualizing Data File Layout IIIhttp://sqlblog.com/blogs/merrill_aldrich/archive/2013/01/28/visualizing-data-file-layout-iii.aspxThis is part three of a blog series illustrating a method to render the file structure of a SQL Server database into a graphic visualization. Previous Installments: Part 1 Part 2 Those that have been reading this series might be be thinking, “Is he goingenCommunityServer 2.1 SP2 (Build: 61129.1)re: Visualizing Data File Layout IIIhttp://sqlblog.com/blogs/merrill_aldrich/archive/2013/01/28/visualizing-data-file-layout-iii.aspx#47378Tue, 29 Jan 2013 13:07:47 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47378Neeraj Mittal<p>Nice explanation of complex topic</p>
re: Visualizing Data File Layout IIIhttp://sqlblog.com/blogs/merrill_aldrich/archive/2013/01/28/visualizing-data-file-layout-iii.aspx#47388Tue, 29 Jan 2013 17:27:42 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47388csm<p>Great tool for academic purposes!! Nothing better than a graphic to explain complex topics.</p>
<p>Hope that you can share this tool with us. </p>
re: Visualizing Data File Layout IIIhttp://sqlblog.com/blogs/merrill_aldrich/archive/2013/01/28/visualizing-data-file-layout-iii.aspx#47392Wed, 30 Jan 2013 03:28:57 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47392merrillaldrich<p>Thanks guys. The public version is in the works.</p>
re: Visualizing Data File Layout IIIhttp://sqlblog.com/blogs/merrill_aldrich/archive/2013/01/28/visualizing-data-file-layout-iii.aspx#47454Sun, 03 Feb 2013 06:17:03 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47454rivantsov<p>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 &quot;CreateDateTime&quot;. So the immediate response to your concerns is &quot;Guids as PK are OK, just make sure you add sane clustered index&quot;. This seems like a complete solution, but... I myself have some concerns - it does not seem to always work well. </p>
<p>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?</p>
<p>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.</p>
<p>Another interesting question - what's the impact of Guid randomness on 'index' performance? </p>
re: Visualizing Data File Layout IIIhttp://sqlblog.com/blogs/merrill_aldrich/archive/2013/01/28/visualizing-data-file-layout-iii.aspx#47455Sun, 03 Feb 2013 06:44:22 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47455rivantsov<p>oh, sorry, I misunderstood. you're demoing the tool... it's not about guids/pk per se</p>