-2

I’m using SQL server 2016 and I have table in my database and table size is 120 GB. It has 300 columns and all columns are NVARCHAR(MAX) and it has 12,00,000 records in it. Mostly 100 columns are NULL all the time or it will have a short value. Here my doubt is why 12,00,000 records taken 120 GB, is it because of datatype?

This a Audit table. This will have CDC historical information.On average this table will get inserted 10,000 records per day. Because on this, my database size is increasing and SQL queries are slow. This is an Audit table and not used for any queries.

Please let me know the reason why my table is very big.

pavan kumar reddy Bandi
  • 1
  • 1
  • 1

2 Answers2

2

Of course, it depends on how you are measuring the size of the table and what other operations occur.

You are observing about 10,000 bytes per record. That does seem large, but there are things you need to consider.

NVARCHAR(MAX) has a minimum size:

nvarchar [ ( n | max ) ]

Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

Even the empty fields occupy 2 bytes plus the nullable flag. With 300 fields, that is 600-plus bytes right there (600 + 600 / 8).

You may also have issues with pages that are only partially filled. This depends on how you insert data, the primary key, and system parameters.

And there are other considerations, depending on how you are measuring the size:

  • How large are the largest fields?
  • How often are rows occupying multiple pages (each additional page has additional overhead)?
  • You are using wide characters, so they may seem larger than they seem.
  • Is your estimate including indexes?
  • If you are measuring database size, you may be including log tables.

I would suggest that you have your DBA investigate the table to see if there are any obvious problems, such as many pages that are only partially filled.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Also, besides fixing the structure, OP could look at SPARSE columns, it might benefit his use case. – Evaldas Buinauskas Sep 23 '17 at 14:13
  • Hi Gordon Linoff, Thanks for your quick reply. is there any query to identify the partially filled pages count or related information on that table. Observed that in last 4 days 3,00,000 records got inserted into the table and table is grown 45 GB extra. Now my table size is 165 GB. I still in confession how it grown and I need to clear picture to understand why it grown. Please let me know what are the important things I need to verify to get the clear picture on this table growth. Your quick response will be appreciated highly. – pavan kumar reddy Bandi Sep 24 '17 at 06:51
0

Edit: updated answer upon clarification on the number of rows that the table really have.

Taking into account that 120GB are 120,000MB you are getting 100KB per row, that is about 330 bytes for each column on average, which its usually quite higher but not for a table with 300 nvarchar(max) columns (note that the nchar and nvarchar types take 2 bytes per char, not 1).

Also you commented that one of that columns have a size of 2,000-90,000 characters (!), supposing that column has on average 46k characters we get a size of:

1,200,000 rows x 46k chars x 2 byte/char = 105GB only for the data of that column.

That leaves 15GB for the rest of columns, or about 13KB per row, which is 44 bytes per column, quite low taking into account that almost all are nvarchar(max).

But those are only estimations, for getting the real size of any column use:

select sum(datalength(ColumnName))/1024.00 as SizeKB from TableName

And all of this is only taking into account data, which is not accurate because the database structures needs its size. For example, indexes sum to the total size of a table, roughly they take the sum of the size of the columns included in the index (for example, if you would define and index on the Big Column it would take another 100GB).

You can obtain how many space the whole table uses, using the following script from another question (it will show the size for each table of the DB):

Get size of all tables in database

Check the column UsedSpaceMB, that is the size needed for the data and the indexes, if for some reason the table is using more space (usually because you deleted data) you get that size in UnusedSpaceMB (a bit of unused space is normal).

Alberto Martinez
  • 2,620
  • 4
  • 25
  • 28
  • Hi Gordon Linoff, – pavan kumar reddy Bandi Sep 24 '17 at 06:29
  • Hi Alberto Martinez, Thanks for your quick reply. Bit more information on the table growth. Table has 304 columns,in it 290 columns with NVARCHAR(MAX),10 columns with INT and DATETIME datatype. Each columns contains 20 to 30 blank fields and 100 columns contain NULL and 10 columns with 2000 character width. I last 4 days 3,00,000 records got inserted to this table and 45 GB table size is increased. Is this correct to your calculations? Please let me know your detailed ideas on this. Thanks in advance!! – pavan kumar reddy Bandi Sep 24 '17 at 08:32
  • Before continuing, when you put 12,00,000 a assumed you had a typo and that you mean 12 million, but maybe you use a different number formatting in your country. When you say 12,00,000 and 3,00,000 new records you mean 12 million and 3 million, or 1.2 million and 300 thousands? – Alberto Martinez Sep 24 '17 at 09:48
  • I mean 1.2 million records. One of the column in my table has 2000 to 90000 character string. I taken sum of the length of that column as bytes and converted as GB. That column it self has 51GB of data. Other columns has around 1 GB. All int I taken as 4 bytes and Datetime is 8 bytes. I calculated the sum of all lengths and it came as 52 GB. Whether my calculation is correct? If No, what is the way to calculate the size. – pavan kumar reddy Bandi Sep 25 '17 at 09:30
  • OK, so the average size per row is 100KB, not 10KB. Anyway, that big column explains a lot, I'll update the question. – Alberto Martinez Sep 25 '17 at 10:46