[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

Row size based on datatypes and NULL fields

Hi

We have a system that sends out emails for our newsletters, the system is recording the emails that were sent into a SQL table.

This has resulted in a VERY large database being created, our database is currently sitting at 75gb, when it really should be between 2 and 5 gb's in size.

We have determined that the table that is using up this space, is the table that records what emails were sent. We are now looking to cut down on the data being stored in this table, though we need some help to determine the best way to reduce its size.

We are using nvarchar(MAX), (nullable) to store some of the information.
Question 1: If this field is set to null, how much disk space does it take up?

We are using varbinary(MAX), (nullable) to store other byte data,
Question 2: if this field is set to null, how much disk space does it take up?

Question 3: If we set the nvarchar to 500 in size, how much space does this take up if we add 100 characters to the field, and how much will it take up on the drive if it is set to null ?

Question 4:How much space does a uniqueidentifier take up on the hard drive when used, and when its null?

Question 5:
Below you will see our table structure, I need to know how we can change its structure so that the row size is less than 8kb's (i have marked the rows as REQ, where we absolutly need the rows, other rows which are not marked can be set to null, or removed from the table.)

Please advise on datatypes that should be used AND the size of the types so that a row in this table would be less than 8kb's:

[COLUMN NAME]            [DATATYPE]
ID                  uniqueidentifier (REQ)
EmailCampaignID            uniqueidentifier (REQ)
EmailSubscriberID            uniqueidentifier (REQ)
OutboundQStatusID            int (REQ)
obtainDetailsFromCampaign      bit
FromName                  nvarchar(500)
FromEmail                  nvarchar(500)
ReplyEmail                  nvarchar(500)
Subject                  nvarchar(500)
ToEmail                  nvarchar(500) (REQ)
ToName                  nvarchar(500) (REQ)
HTMLBody                  varbinary(MAX)
TXTBody                  varbinary(MAX)
RCF822                  varbinary(MAX)
PriorityLevel                  int
SMTPSendingLog            nvarchar(MAX)
SMTPReference1            nvarchar(MAX)
SMTPReference2            nvarchar(MAX)
SMTPReference3            nvarchar(MAX)
SentDT                  datetime (REQ)
FailCount                  int (REQ)
dt                  datetime (REQ)
msrepl_tran_version            uniqueidentifier
0
KamM
Asked:
KamM
3 Solutions
 
ptjcbCommented:
>>nvarchar(max) Question 1: If this field is set to null, how much disk space does it take up?
As much as it needs, up to 2GB. The storage size is two times the number of characters entered + 2 bytes. For example, if you have entered 1000 characters, that is 2002 bytes.

>>nvarbinary(max)
As much as it needs, up to 2GB. Same issue as nvarchar(max).

>> If we set the nvarchar to 500 in size, how much space does this take up if we add 100 characters to the field, and how much will it take up on the drive if it is set to null ?

202 bytes

>> How much space does a uniqueidentifier take up on the hard drive when used, and when its null?

The uniqueidentifier stores 16-byte binary values. If the column is NULL, then it should be 0.

You can check the actual size of any of these columns with

SELECT datalength(columnName)
FROM t1

There is also overhead for having a row.

To get a better estimate - use

GO
EXEC sp_spaceused N'tableName';
GO
0
 
Arthur_WoodCommented:
Any varXXXX(NNN) field, when the field is NULL, will have a real size of 0, so:

">>nvarchar(max) Question 1: If this field is set to null, how much disk space does it take up?
As much as it needs, up to 2GB. The storage size is two times the number of characters entered + 2 bytes. For example, if you have entered 1000 characters, that is 2002 bytes.
#### When this field is NULL, it will have a size of 0

>>nvarbinary(max)
As much as it needs, up to 2GB. Same issue as nvarchar(max).
#### When this field is NULL, it too will have a size of 0


AW
0
 
ptjcbCommented:
Also there is overhead for each row.

FROM http://support.microsoft.com/kb/827968

When you estimate the size of a table or of a database, consider the following information:
"      Each row in the table has an overhead of 6 bytes.
"      Each column in the table has an overhead of 1 byte, plus 1 byte for every 256 bytes of row storage.
"      Fixed-length data types have an overhead of 1 byte for each column, and the overhead is rounded to the next higher byte.
"      Zero-length string columns occupy 1 byte in the row.

***
I realize that this is for SQL Server CE, but the same values apply to any SQL Server.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Scott PletcherSenior DBACommented:
Do you *really* need unicode chars?  Just changing all the nvarchar cols to varchar will save you 1/2 the space they take up.

Also, normalize it a bit so that the email content is stored in a separate table, then assign an int value as a content code.  The way it is now, if 10 (or 100 or 1000) people are all sent the same email, the content is stored 10/100/1000 times, when it only needs to be stored once.
0
 
Scott PletcherSenior DBACommented:
If you typically send to the same people over and over -- and that is usually the case -- then you could also gain huge space back by shortening the names, such as:
ToEmail                  nvarchar(500) (REQ)
ToName                  nvarchar(500) (REQ)
to a coded int value also, with a lookup table to get the original name value back.
0
 
KamMAuthor Commented:
Thank you all for your feedback, you have all helped with questions 1 to 4, ptjcb thank you for the datalength note that has helped me see the actual usage on the drive.

I still need some help with question 5, how will i get this to be under 8kb, the reason i want it under 8kb is that i have read up on the net, that a row being less than 8kb's in size has a huge performance advantage, not to mention it will be the RIGHT size for my database file size.

Another thing im looking at on sql, i run the SP sp_spaceused , which gives me a reserved value, what is this reserved value? how can i reclaim this space, should i reclaim it? why is it reserved? If you could give me an explanation on these results returned it will help allot.

0
 
ptjcbCommented:
ScottPletcher gave you two suggestions that should make your table size smaller.

I would check the actual length of every column and, as Scott points out, change the data type from nvarchar to varchar, and consider breaking the table into smaller tables.

It is difficult to restructure your table because I do not know your requirements, your table structure, your actual data, your documentation or your application. I don't know the difference between HTMLBody or TXTBody, for example.

Sp_spacedused - the reserved value is the total amount of space allocated to the objects in your database. You cannot reclaim that space, it should be the sum of the data, index_size, and unused. It is reserved space needed for your database.

http://msdn2.microsoft.com/en-us/library/ms188776.aspx

For example, I have a small test database
Reserved
1368 KB                                    632 KB      648 KB      88 KB








0
 
ptjcbCommented:
ScottPletcher gave you two suggestions that should make your table size smaller.

I would check the actual length of every column and, as Scott points out, change the data type from nvarchar to varchar, and consider breaking the table into smaller tables.

It is difficult to restructure your table because I do not know your requirements, your table structure, your actual data, your documentation or your application. I don't know the difference between HTMLBody or TXTBody, for example.

Sp_spacedused - the reserved value is the total amount of space allocated to the objects in your database. You cannot reclaim that space, it should be the sum of the data, index_size, and unused.

http://msdn2.microsoft.com/en-us/library/ms188776.aspx

For example, I have a small test database
Reserved                             data        index    unallocated
1368 KB                                    632 KB      648 KB      88 KB

If you add the data, index, and unallocated you have the reserved size.








0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now