June 6, 2011 at 4:17 am
Hi,i am really confused.
i have one created table
Create table ntinyint
(Id tinyint Not Null
)
GO
insert into ntinyint (Id)values(1)
Go 51
--This will insert 51 row in table ntinyint
-Now we know Datatype is tinyint so it is going to occupy 1 byte.
after inserting 51 row in table when i use SP_spaceused to know hoy many size is occupied by Table Data ?
Result is 16KB i am amazed.so i use DBCC IND and then DBCC Page to know how first Data page is used.
I am attaching output of first Data page in attachment.
In attachment you can see .
m_slotCnt = 47 m_freeCnt = 7579
m_freeData = 519
47 records are in this page,
7579 bytes are free
529 byte is offset from the start of the page to the first byte after the end of the last record on the page.
you also can see in output
Slot 1 Offset 0x69 Length 9
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 9
Memory Dump @0x2776C069
00000000: 10000500 01010000 00†††††††††††††††††.........
Slot 1 Column 1 Offset 0x4 Length 1 Length (physical) 1
so Question is how 9 byte is occupied by offset?
and why SQl server did not use remaining free bytes(m_freeCnt = 7579) to insert data?
June 6, 2011 at 4:31 am
What is it that you're concerned about?
Remember there's page header, row header and slot array, so even with a single tinyint, the row will be larger than 1 byte. There's still a substantial amount of free space, so any new rows will go onto the same page until it's full. Then SQL will add a second page to the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 6, 2011 at 4:41 am
yes,that is true ,Page header size is 96 byte.when i insert 51 Row ,47 rows goes in first Data page and then Sql server insert remaining 4 row in second data page.
in attached page.txt file you can see out put of DBCC Page Detailed output ,in out put m_freeCnt is 7579,then why sql sever don't use remaining space?
June 6, 2011 at 4:53 am
Post the output of DBCC Ind.
When I tried that I got exactly what I expected, two pages allocated to the table (16kB total), one of them being a data page.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 6, 2011 at 5:00 am
I have attached output of
Exec sp_spaceused'ntinyint'
DBCC IND ('SANDBOX','ntinyint',-1)
in excel file.
June 6, 2011 at 5:25 am
I think you have run that test on 32 bit system.i am working on 64 bit.
I am getting same result you get on 32 bit system.i am able to insert 700 row in 32 bit system.
June 6, 2011 at 5:30 am
Gaurang-Patel (6/6/2011)
I think you have run that test on 32 bit system.i am working on 64 bit.I am getting same result you get on 32 bit system.i am able to insert 700 row in 32 bit system.
No. I ran it on a 64-bit server. Besides, the processor architecture has absolutely no effect on the data file structure.
What's the server's default fill factor?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 6, 2011 at 5:42 am
My Default fill factor is 0,
i am sorry that output from 32 bit sql server running on Window server 2008 32 bit edition in VMware.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply