page size in Sql Server

  • Ramukar LivingForSQLServer

    SSCommitted

    Points: 1749

    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

  • ps.

    One Orange Chip

    Points: 29252

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

    Why do you need to change it?



    Pradeep Singh

  • MichaelJasson

    SSCrazy

    Points: 2732

    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.

  • Ramukar LivingForSQLServer

    SSCommitted

    Points: 1749

    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

  • EdVassie

    SSC Guru

    Points: 60274

    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

  • forsqlserver

    SSCoach

    Points: 18900

    looping

    Thanks

  • Jayanth_Kurup

    SSC-Insane

    Points: 22967

    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]

  • Perry Whittle

    SSC Guru

    Points: 233859

    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" 😉

  • Ramukar LivingForSQLServer

    SSCommitted

    Points: 1749

    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

  • TheSQLGuru

    SSC Guru

    Points: 134017

    >> 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

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    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.

  • MysteryJimbo

    SSC-Insane

    Points: 24203

    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

  • Hardy21

    SSCrazy Eights

    Points: 9708

    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 13 (of 13 total)

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