Jon Gurgul's Blog

Tag: Page

  • SQL Index dm_db_index_xxx_stats

    —

    by

    Jon Gurgul
    in

    There are 3 dm_db_index_xxx_stats objects that can be used to check and investigate index information. sys.dm_db_index_usage_stats can be used to see how beneficial the index is, and its related maintenance cost. Note that this information is reset upon instance restart. The final two index stats objects provide information on the physical and operational statistics of…

  • SQL Server Page Types

    —

    by

    Jon Gurgul
    in

    There are lots of different page types and Paul Randall list them in his article. However as I tend to have a rather poor memory for remembering them first off here are the ones that can be anywhere within the database. m_type 1  DATA_PAGE 2  INDEX_PAGE 3 TEXT_MIX_PAGE 4 TEXT_TREE_PAGE 7 SORT_PAGE 10 IAM_PAGE Now…

  • SQL Server Row Internals

    —

    by

    Jon Gurgul
    in

    Row Storage A Row is stored on a Page which is 8192 Bytes, and this is broken down in two key areas when considering physical storage. Firstly on all pages there is a 96 Byte Header. In addition to this we also need to store the Row structure as well as its location in the…

  • TORN_PAGE_DETECTION Internals (SQL 2005 onwards)

    —

    by

    Jon Gurgul
    in

    CHECKSUM is in the majority of cases the more appropriate PAGE_VERIFY option as it provides a much more complete check (slightly higher overhead) when compared to TORN_PAGE_DETECTION. I thought today however I would write a simple post to show how the internals of TORN_PAGE_DETECTION work even tho it is less used. I am guessing that you have…

  • SQL Server Max Row Size 8060 Part 2

    —

    by

    Jon Gurgul
    in

    In my previous post on row maximum length I showed that the largest row size that could be stored on page was 8053 byte. So to recap a table with 2 fixed length columns CHAR(8000) & CHAR(53) the internal storage would look as below, with an unused space of 34 bytes. Page Header 96 bytes…

  • SQL Server Max Row Size 8060

    —

    by

    Jon Gurgul
    in

    When asked the size of the largest row that could be placed on to a single SQL Server data page the answer that most give is 8060 bytes. If you try to create a fixed length table with columns that total this length however it throws an error. Please create a database to run this test and…