Post reply

page size in Sql Server

  • July 6, 2009 at 8:59 am

    #135982

    Guys,

    why Page size in Sql server is 8K? (why not 4K or 16K)

    and can we change default page size?

    Can anyone please clarify my doubt.

    Thanks in advance

    ram

  • July 6, 2009 at 9:05 am

    #1020438

    This is by design of Sql Server. We cannot change and it's always 8K.

    Why do you need to change it?


    Pradeep Singh

  • July 6, 2009 at 9:06 am

    #1020439

    This is granular arrangement from sql server architects. We can't change page size.

    Why This is 8k?

    1 MB = 128 pages.

    Now 1 MB = 1024

    so we needed something that should divide this completely and that should now be cumbersome as well. You know that one row, if this doesn't fit into one page, is written on another page. One row can't be written on two pages. Keeping this in mind 8K was choosen as 4K would be too short and every row would have to be < 4K.

    -MJ
    Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.

  • July 7, 2009 at 12:07 am

    #1020891

    thanks MichaelJasson and Ps for ur reply...

    I am doing query tuning as part of my work... for that im learning sql architecture and query processor behaviour..

    I know page size is 8K. wanted to know the reason for 8K size.

    (like any io related performance benifit)

    thanks again for ur help and time

    Ram

  • July 8, 2009 at 5:09 am

    #1021561

    The page size in SQL Server is 8K because the SQL Server authors made it that way.

    Some other database systems allow the DBA to specify the page size. There can be some situations where a 4K or even a 32K page size can be more efficient than an 8K page size, but we do not have that choice in SQL Server.

    Just about all applications will perform very well on SQL Server. If you have a very large database (> 10 TB) or a very large number of concurrent users (> 1000) you should research which database system is best for your application - it may be SQL Server, but sometimes DB2, Oracle or Teradata may be the right choice.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • July 20, 2011 at 1:53 am

    #1355403

    looping

    Thanks

  • July 20, 2011 at 2:04 am

    #1355409

    I think the source of this could be in the underlying File system. NTFS seems to have better IO performance when using 8 KB cluster sizes , but I am not suggesting this is the rational behind having 8KB data pages.

    Jayanth Kurup[/url]

  • July 20, 2011 at 3:01 am

    #1355431

    Ramkumar (LivingForSQLServer) (7/6/2009)

    Guys,

    why Page size in Sql server is 8K? (why not 4K or 16K)

    and can we change default page size?

    Can anyone please clarify my doubt.

    Thanks in advance

    ram

    i'm stabbing in the dark and guessing here, but you're an Oracle DBA right? 😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • July 20, 2011 at 11:31 am

    #1355900

    Perry Whittle,

    My blog name is LivingForSQLServer. Its not just a name sake. I mean it. 🙂

    and I had this doubt while writing articles on Storage internals concept.I believe (as one friend shared) there could be some file system/IO level advantage behind this 8 K size.

    may be there could be some relation between Extent size (64K) and IO. experts can clarify.

    and If other RDBMS support different page sizes, then its our right to understand

    1. advantage behind that

    2. why SQL Server can't do that

  • July 21, 2011 at 9:10 am

    #1356493

    >> One row can't be written on two pages. Keeping this in mind 8K was choosen as 4K would be too short and every row would have to be < 4K.

    That is not true. Longer rows can go to an overflow page arrangement that has been available in SQL server for several versions now.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • July 21, 2011 at 9:26 am

    #1356519

    forsqlserver (7/20/2011)

    looping

    No need to post to get notifications.

    Go to the top right of the thread and see the topic options menu.

  • July 22, 2011 at 5:10 am

    #1356941

    I would have guessed the easiest answer is the page size would be a legacy from the engine's Sybase days.

    Why Microsoft havent changed this or implemented adaptive pages sizes I couldnt say. The answers given so far all seem plausible

  • July 21, 2014 at 12:19 am

    #1730891

    MysteryJimbo (7/22/2011)

    I would have guessed the easiest answer is the page size would be a legacy from the engine's Sybase days.

    Why Microsoft havent changed this or implemented adaptive pages sizes I couldnt say. The answers given so far all seem plausible

    +1

    Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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