Size of the image in DB using varbinary (max)

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Size of the image in DB using varbinary (max)

Author

Topic

sagitariusmzi
Posting Yak Master

113 Posts

Posted - 2010-01-14 : 00:50:56

Hi,

I have an image of size 3801K, when i save the image, it gives me the size 3904 (which i get using the SP_SPACEUSED [schema.table].
that means that 3904/8 = 488 pages
but the actual size of the image is 3801 and size returned from table using SP_SPACEUSED is 3904 (which means 96 bytes overhead is also there)
Total overhead = 488 * 96 bytes = 46848 bytes / 1024 = 46KB total overhead by 488 pages
which 3904 (actual size returned using SP_SPACEUSED) – 46KB (overhead of every page) = 3858KB
less the actual size of the image was 3801 , that gives me 57KB.

Now my question is
1. Why the (size returned+overhead of the page) is not equal to size of the image
2. Where the remaining 57KB has been used
3. If overhead is already included in every page (in my case 488 * 96 bytes) then the difference is of 103KB

Regards

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-14 : 04:46:42

The image goes over (3801 * 1024) / 8192 = 475,125 pages.

The questions are:
Is 3801k the accurate size (3801 * 1024 = 3,892,224)?
What is the blocking factor on the used hard disk?

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-14 : 05:08:20

Your image size is 3801 kb. A page in SQL Server can hold 8000 bytes (not 8192 bytes or 9 kb) for a single column.
3801 kb is 3 892 224 bytes.

3892224 bytes / 8000 bytes is 486.528 pages. Since only whole pages is counted this is 487 pages.
487 pages * 8 kb (including system overhead of 132 bytes per page) is 3896 kb.

You are using 486 full pages, and 4224 bytes of the last #487 page.

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-14 : 06:05:53

Hi Peso it's absolut new to me that a page in sql server can hold 8000 bytes.
I have learned page has a size of 8192 bytes and this is 8064 bytes data and 128 bytes overhead.
I have learned this a time ago when I was going to make my mcts in sql servr 2005.

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-14 : 06:16:03

Msg 1701, Level 16, State 1, Line 2
Creating or altering table '#Sample' failed because the minimum row size would be 8063, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-14 : 06:25:06

Msg 131, Level 15, State 2, Line 1
The size (8060) given to the column 'col1' exceeds the maximum allowed for any data type (8000).

Yes, a page is 8192 bytes (8 kb) but it holds 132 bytes of system/internal data, such as page type, page linkers and so on.
Can have have as much as 8060 bytes of userdata in a single page.
For example; Col1 CHAR(8000), Col2 UNIQUEIDENTIFIER, Col2 BIGINT.
Or Col1 CHAR(8000), Col2 CHAR(53)

N 56°04'39.26"
E 12°55'05.63"

sagitariusmzi
Posting Yak Master

113 Posts

Posted - 2010-01-14 : 07:35:09

Hi, Peso

In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.

http://msdn.microsoft.com/en-us/library/ms190969.aspx
http://msdn.microsoft.com/en-us/library/aa174529(SQL.80).aspx

Please correct me , i am wrong.

i am using varbinary(max)

Regards

sagitariusmzi
Posting Yak Master

113 Posts

Posted - 2010-01-14 : 08:15:01

When i executed this query

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N'DBNAME'), OBJECT_ID(N'dbo.TABLENAME'), NULL, NULL , 'DETAILED');

i got the results, which are similar to that of Peso's results

alloc_unit_type_desc page_count
IN_ROW_DATA 1
LOB_DATA 486

Regards

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-14 : 08:30:52

quote:
Originally posted by Peso

Msg 131, Level 15, State 2, Line 1
The size (8060) given to the column 'col1' exceeds the maximum allowed for any data type (8000).

Yes, a page is 8192 bytes (8 kb) but it holds 132 bytes of system/internal data, such as page type, page linkers and so on.
Can have have as much as 8060 bytes of userdata in a single page.
For example; Col1 CHAR(8000), Col2 UNIQUEIDENTIFIER, Col2 BIGINT.
Or Col1 CHAR(8000), Col2 CHAR(53)

N 56°04'39.26"
E 12°55'05.63"

Thanks Peso.
I have learned something wrong or it is also possible that I remebered wrong because I am already a Grandpa

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources