Size of database - SQL Server Forums

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 database

Author

Topic

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-03-09 : 03:12:55

I have created a sample database,created a table with single column of type varbinary(max), so i can save snaps.

On start with no data at all...sp_spaceused showing following data
data: 752 KB
index size: 576 KB

Now i insert 100 records with a same snap having size 17 KB each...now sp_spaceused information was
data: 2744 KB
index size: 592 KB

it means per row size is 19.92 KB
Why i am getting 2.92KB extra size ?????
Is there anyother way to get row size ?????

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 03:45:48

Hi,

You got to understand there is overhead for storing information in SQL Server. For example a SQL Server page is 8K (8192) from which the first 96 bytes of the page are header. There is also additional overhead for varbinary fields, that is it writes in the system where to look for in the page. ..

You can read more on storing variable length columns here, http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-27-How-does-the-storage-engine-find-variable-length-columns.aspx.

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-03-09 : 05:44:57

On more thing tht really effective in my case is LOW AVERAGE PAGE DENSITY i.e. 35 %, as data is binnary so there is a possiblity of page splitting. How to improve average page density ????

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 14:15:40

since you are using a varbinary(max) and inserting a 17kb for each row.. which is bigger then 8K page size. The storing is a bit more tricky to understand and explain (I don't know all internals yet!). Try creating an identity column on the field and clustering it with fill factor of lets say 95%? But I would think you will still not get full pages. When you are looking at the Page Density what stats are you looking at? DMV? dm_db_physical_index_stats?

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-03-11 : 07:32:58

Yes moving fill factor to 95% and applying defregmentation script really worked...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources