Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
frogrammer82
Starting Member
7 Posts |
 Posted - 2002-06-15 : 19:42:22
|
| Hello,I know that SQL Server's page size is 8K. And i read an article today and my mind is bit confused!!? I want to ask about if i make kilobyte sizes of all the fields in a row to ~2K, is SQL Server read them; 'four row' at a time?.. (i dont know what was referred in the article while saying 'time')For example, i have a table with the rows called (id int PRIMARY, charfield1 char(975) NOT NULL, charfield2 char(975) NOT NULL).. I am not sure but i am thinking the size of this row is might be ~2000byte which is the one of the four of the sql server 7.0's page size?.. and as to the article i read, sql server will read four rows at a time, is this true? if it's not why it's not? if it's why it is?I think my question is little-bit confusing, sorry about it:(Please fill my brain with the correct information:) !ThanxEdited by - frogrammer82 on 06/15/2002 22:56:57Edited by - frogrammer82 on 06/16/2002 02:51:29 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-06-16 : 09:19:32
|
| Pages are 8KbMax user data is 8096 bytes (there is a 96 byte page header)Thus if your row size is ~2000 bytes you will be able to fit 4 rows on a page. Rows cannot be split across different pages.Therefore for one logical I/O operation 4 rows will be returned.This is just in terms of accessing pages in the buffer cache.There are other mechanisms that can be involved whn fetching pages from physical disk into buffer cache such as read aheads that can use larger fetches to precache pages before they are actually read.Basically the narrower your table , the more rows can fit on the page and the more efficent each logical I/O will be. If your row size was 200 bytes you could fit 40 rows on each page and each logical I/O would fetch these 40 rows.HTHJasper Smith |
 |
|
|
frogrammer82
Starting Member
7 Posts |
Posted - 2002-06-16 : 09:40:36
|
| Thanx for the answer jasper_smith!So, how much performance can i gain for example with holding the size of my rows about ~2000 bytes? I know maybe you cannot answer this type of question because of several aspects can impulse the performance. |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-06-16 : 12:16:07
|
| Why are you fixating on 2000 bytes ?For you example table, unless your charfield1 and charfield2 columns are >95% going to be 975 chars then I would use varchar,otherwise you will be wasting tremendous amounts of space and make each logical I/O highly inefficent. Whilst varchar does have some minor additional overhead it is unlikely to be outweighed by the efficency savings in this case.I'm not sure you fully understood my point from the previous post, that the smaller your row size the more rows can be fetched for each logical I/O. Since I/O cost (be it logical or physical) makes up most of the execution cost of any operation then the aim is to minimise it as much as possible. Hence you should be aiming for as narrow a table as possible whilst not limiting the data you can store. More often than not the use of smallint or tinyint rather than int is a false economy as the extra 2 or 3 bytes are "free" in that they do not affect how many rows can be stored on a page( plus Intel processors like 4 bytes.) and you don't impose an artifically low limit on the data you can store.HTHJasper SmithEdited by - jasper_smith on 06/16/2002 12:17:19 |
|
|
|
|
|
|
|
|
About SQL Server 7.0 Page Size