Post reply

Best practice on table design

  • June 24, 2003 at 2:24 am

    #81853

    Hi all,

    another best practice question.

    we know, SQL Server stores data in pages of 8KB. A single table row cannot exceed the size of a single page. So, is having a table design greater than 4 KB considered inefficient per se as for space utilization and overall performance?

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 24, 2003 at 4:58 am

    #462776

    Yes - if your row size means that a lot of space will be left on table pages (e.g. fixed row size of 3KB, * 2 = 6, leaves 2K unusable) then you will swell the database size and slow down table scans to some extent.

    But in practice, using variable length columns means that row size will typically be much smaller than the maximum. You need to consider average size rather than maximum size when deciding the issue.

    SQL server will use the actual size of the data and not the defined maximum when allocating page space to records.

    Also, since table sacns are so inefficient in most cases, your indexing strategy will probably mean that they hardly ever occur on large tables.

    In the end analysis, there is usually little you can do if the requirements of the data model dicatate a large record size. I suppose a vertical partition for frequently- vs. rarely accessed data is a possibility.

    Also, space calculations don't include text and image data, of course, which are stored (I think?) as a pointer.

    Tim

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • June 24, 2003 at 5:10 am

    #462778

    Hi Tim,

    quote:

    But in practice, using variable length columns means that row size will typically be much smaller than the maximum. You need to consider average size rather than maximum size when deciding the issue.

    good hint, but then you have to make assumption on what there might be in those columns. It can be a good approximation or a less good one.

    quote:

    Also, space calculations don't include text and image data, of course, which are stored (I think?) as a pointer.

    Yes, stored as pointer. I must admit I'm not firm in calculating table space.

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 25, 2003 at 9:07 am

    #463042

    The other performance consideration, in addition to the efficiency of table scans as mentioned, is your cache hit ratio and I/0. More rows to a page, more rows potentially in the cache, obviously a good thing. And even when you do have to read from disk, if the more rows to a page, the more efficient the I/0. If your rows truly need to be that big (>4KB) the vertical partition suggestion might be a good way to go.

    Another thing to keep in mind is that if there are no clustered indexes (it's a heap) and there are some variable length columns, SQL Server may be able to use up the "wasted" space -i.e. if row 1 is 5KB and row 1000 is 3KB they might both end up on the same page, since on a heap SQL Server is going to simply look for free space irregardless of any row ordering when row 1000 is inserted. Naturally this is a trade off since you may need the efficiency of a clustered index.

    a tip for info on calculating row size (and other low-level storage details):

    grab a copy of Inside SQL Server by Kalen Delaney. (The 7.0 or 2K editions are very similar - I couldn't find any differences in the internal storage section) Excellent discussion of this very thing, with all the detail info you'll ever need. (Including why your maximum single row size is actually 8096 bytes, and how much of that actually goes to row overhead and how much is your data.). Text/Blob columns use a 16-byte pointer in the data row.

    HTH,

    Jason


    JasonL

  • June 26, 2003 at 12:20 am

    #463176

    Hi Jason,

    quote:

    The other performance consideration, in addition to the efficiency of table scans as mentioned, is your cache hit ratio and I/0. More rows to a page, more rows potentially in the cache, obviously a good thing. And even when you do have to read from disk, if the more rows to a page, the more efficient the I/0. If your rows truly need to be that big (>4KB) the vertical partition suggestion might be a good way to go.

    good hint!

    quote:

    grab a copy of Inside SQL Server by Kalen Delaney. (The 7.0 or 2K editions are very similar - I couldn't find any differences in the internal storage section)

    choosing the right book(s) is one thing I'm struggling on. For I don't want to buy books just to look good on the shelf.

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply