2

I have come across a sizing issue with my database tables becoming too large. Some digging has shown that it is how MS SQL stores the information.

I created two test tables and filled them with data:

CREATE TABLE INVOICEDOCUMENTSTEST1 (
  INVOICEINDEXNUMBER numeric(10, 0) NOT NULL DEFAULT (0),
  CONSTRAINT PK_INVOICEDOCUMENTSTEST1 PRIMARY KEY CLUSTERED (INVOICEINDEXNUMBER))

DECLARE @i INT;
SELECT @i = 1;
SET NOCOUNT ON
WHILE @i <= 235605
BEGIN
  INSERT INTO INVOICEDOCUMENTSTEST1(INVOICEINDEXNUMBER) VALUES (@i);
  SELECT @i = @i + 1;
END;

CREATE TABLE INVOICEDOCUMENTSTEST2 (
  INVOICEINDEXNUMBER numeric(10, 0) NOT NULL DEFAULT (0),
  [INVOICETAXTYPE] [varchar](15) NULL,
  [INVOICETYPE] [varchar](20) NULL,
  [INVOICESTATUS] [varchar](25) NULL DEFAULT ('Entering'),
  [INVOICEDATE] [datetime] NULL,
  [INVOICETIME] [datetime] NULL,
  [CREATEDATE] [datetime] NULL,
  [CREATETIME] [datetime] NULL,
  [CREATEBY] [varchar](50) NULL,
  [POSTDATE] [datetime] NULL,
  [POSTTIME] [datetime] NULL,
  [POSTEDBY] [varchar](50) NULL,
  [JOBCODE] [varchar](30) NULL,
  [LINKCODEFORWARD] [varchar](30) NULL,
  [CUSTACCOUNTCODE] [varchar](50) NULL,
  [CUSTCOMPANYNAME] [varchar](50) NULL,
  [CUSTDELIVERYADDRESS1] [varchar](50) NULL,
  [CUSTDELIVERYADDRESS2] [varchar](50) NULL,
  [CUSTDELIVERYSUBURB] [varchar](50) NULL,
  [CUSTDELIVERYSTATE] [varchar](50) NULL,
  [CUSTDELIVERYPOSTCODE] [varchar](10) NULL,
  [CUSTDELIVERYCOUNTRY] [varchar](50) NULL,
  [CUSTORDERNUMBER] [varchar](20) NULL,
  [CUSTCONTACT] [varchar](20) NULL,
  [CUSTDEFAULTPRICECODE] [varchar](10) NULL DEFAULT ('Price01'),
  [CUSTCASHSALEACCOUNTONLY] [varchar](5) NULL,
  [CUSTANALYSISCODE] [varchar](50) NULL,
  [SALESPERSON] [varchar](50) NULL,
  [SALESPERSONANALYSISCODE] [varchar](50) NULL,
  [EXTAXINVOICE] [varchar](5) NULL DEFAULT ('FALSE'),
  [EXTAXNUMBER] [varchar](20) NULL,
  [COMMENTS1] [varchar](100) NULL,
  [COMMENTS2] [varchar](100) NULL,
  [COMMENTS3] [varchar](100) NULL,
  [COMMENTS4] [varchar](100) NULL,
  [COMMENTS5] [varchar](100) NULL,
  [CUSTINVOICEADDRESS1] [varchar](50) NULL,
  [CUSTINVOICEADDRESS2] [varchar](50) NULL,
  [CUSTINVOICESUBURB] [varchar](50) NULL,
  [CUSTINVOICESTATE] [varchar](50) NULL,
  [CUSTINVOICEPOSTCODE] [varchar](10) NULL,
  [CUSTINVOICECOUNTRY] [varchar](50) NULL,
  [COURIERCOMPANY] [varchar](50) NULL,
  [COURIERCONSIGNMENTNOTE] [varchar](50) NULL,
  [FREIGHTCOMPANY] [varchar](50) NULL,
  [PAYMENTTERMS] [varchar](50) NULL,
  [PAYMENTREQUIREDBY] [datetime] NULL,
  [REQUIREDBY] [datetime] NULL,
  [REQUIREDAFTER] [datetime] NULL,
  [SHIPPINGDESCRIPTION1] [varchar](50) NULL,
  [SHIPPINGDESCRIPTION2] [varchar](50) NULL,
  [SHIPPINGCARTONQUANTITY] [numeric](10, 0) NULL DEFAULT (0),
  [SHIPPINGDATE] [datetime] NULL,
  [SHIPPINGDGDECLARATION] [varchar](10) NULL DEFAULT ('Does not'),
  [SHIPPINGTIME] [datetime] NULL,
  [SHIPPINGUNITS] [varchar](20) NULL DEFAULT ('Cartons'),
  [NOTES] [ntext] NULL,
  [CUSTCUSTOMERSLEDGERCODE] [varchar](50) NULL,
  [CUSTACCRUALSLEDGERCODE] [varchar](50) NULL,
  [CUSTGLOBALDISCOUNT] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALSUBTOTAL] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALSUBTOTALCALCORD] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALSUBTOTALCALCDEL] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALDISCOUNT] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALDISCOUNTCALCORD] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALDISCOUNTCALCDEL] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALFREIGHT] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALTAX] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALTAXCALCORD] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALTAXCALCDEL] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALROUNDINGAMOUNT] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALTOTAL] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALTOTALCALCORD] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALTOTALCALCDEL] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALPAID] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALCHANGE] [numeric](14, 4) NULL DEFAULT (0),
  [SUPPLYLOCATION] [varchar](10) NULL,
  [HASBEENPRINTED] [varchar](5) NULL DEFAULT ('FALSE'),
  [PRINTTHISTIME] [varchar](5) NULL DEFAULT ('FALSE'),
  [TRANSACTIONMODE] [varchar](10) NULL DEFAULT ('Online'),
  [PREVTOTALFREIGHT] [numeric](14, 4) NULL DEFAULT (0),
  [LINEROUND] [varchar](5) NULL DEFAULT ('TRUE'),
  [SOURCEREFERENCE] [varchar](30) NULL,
  [LINECOUNT] [numeric](10, 0) NULL DEFAULT (0),
  [SHIPPINGINSTRUCTIONS] [varchar](50) NULL,
  [SHIPPINGPACKEDBY] [varchar](50) NULL,
  [INVENTORYFREIGHTANALCODE] [varchar](50) NULL,
  [LINKCODEBACKWARD] [varchar](30) NULL,
  [PREVTOTALCHANGE] [numeric](14, 4) NULL DEFAULT (0),
  [USEGST] [varchar](5) NULL DEFAULT ('FALSE'),
  [TOTALFREIGHTGST] [numeric](14, 4) NULL DEFAULT (0),
  [PREVTOTALFREIGHTGST] [numeric](14, 4) NULL DEFAULT (0),
  [GSTINVOICETYPE] [varchar](20) NULL DEFAULT ('Tax Invoice'),
  [CUSTPHONENUMBER] [varchar](25) NULL,
  [CUSTFAXNUMBER] [varchar](25) NULL,
  [LASTEDITDATE] [datetime] NULL,
  [LASTEDITTIME] [datetime] NULL,
  [LASTEDITBY] [varchar](50) NULL,
  [CUSTGSTSALESTYPE] [varchar](10) NULL DEFAULT ('Including'),
  [GSTPRICING] [varchar](10) NULL,
  [GSTTRANSFER] [numeric](10, 0) NULL DEFAULT (0),
  [ORIGINALINVOICENUMBER] [varchar](20) NULL,
  [ORIGINALDOCUMENTTYPE] [varchar](30) NULL,
  [CUSTEMAILADDRESS] [varchar](200) NULL,
  [CUSTMOBILENUMBER] [varchar](25) NULL,
  [TOTALSURCHARGE] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALSURCHARGECALCDEL] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALSURCHARGECALCORD] [numeric](14, 4) NULL DEFAULT (0),
  [CUSTFREIGHTCHARGE] [numeric](14, 4) NULL DEFAULT (0),
  [CUSTFREIGHTFREETHRESHOLD] [numeric](14, 4) NULL DEFAULT (0),
  [CUSTSURCHARGECHARGE] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALSURCHARGEGST] [numeric](14, 4) NULL DEFAULT (0),
  [PREVTOTALSURCHARGE] [numeric](14, 4) NULL DEFAULT (0),
  [PREVTOTALSURCHARGEGST] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALSURCHARGEGSTCALCORD] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALSURCHARGEGSTCALCDEL] [numeric](14, 4) NULL DEFAULT (0),
  [INVENTORYSURCHARGEANALCODE] [varchar](50) NULL,
  [INVOICEPRIORITY] [varchar](10) NULL DEFAULT ('Normal'),
  [SHIPPINGPACKERHOLDINGLOC] [varchar](250) NULL,
  [CUSTOMERID] [numeric](10, 0) NOT NULL DEFAULT ((0)),
  [DELIVERYINSTRUCTIONS] [ntext] NULL,
  [PAYMENTNOTES] [ntext] NULL,
  [SHIPPINGNOTES] [ntext] NULL,
  [FREIGHTANDSURCHARGEOLDPOST] [varchar](5) NULL DEFAULT ('FALSE'),
  [TOTALFREIGHTTHRESHOLDREMOVED] [numeric](14, 4) NULL DEFAULT ((0)),
  [CUSTGSTLEDGERCODE] [varchar](50) NULL,
  [SALESPERSONNAME] [varchar](200) NULL,
  [SHIPPINGHASBEENPICKED] [varchar](5) NULL DEFAULT ('FALSE'),
  [SHIPPINGHASBEENPACKED] [varchar](5) NULL DEFAULT ('FALSE'),
  [SHIPPINGHASBEENDELIVERED] [varchar](5) NULL DEFAULT ('FALSE'),
  [SHIPPINGPICKEDBY] [varchar](250) NULL,
  [SHIPPINGPICKDATE] [datetime] NULL,
  [SHIPPINGPICKTIME] [datetime] NULL,
  [SHIPPINGPACKDATE] [datetime] NULL,
  [SHIPPINGPACKTIME] [datetime] NULL,
  [SHIPPINGDELIVEREDBY] [varchar](250) NULL,
  [SHIPPINGDELIVERYDATE] [datetime] NULL,
  [SHIPPINGDELIVERYTIME] [datetime] NULL,
  [INVOICEPRIORITYVALUE] [numeric](10, 0) NULL DEFAULT ((3)),
  [SHIPPINGPACKER] [varchar](250) NULL DEFAULT ('ANYONE'),
  [SHIPPINGPICKER] [varchar](250) NULL DEFAULT ('ANYONE'),
  [SHIPPINGDELIVERYRUN] [varchar](250) NULL DEFAULT ('ANYONE'),
  [CUSTALLOWBACKORDERS] [varchar](5) NULL DEFAULT ('TRUE'),
  [QUOTECOMMENT] [varchar](100) NULL,
  [QUOTESTATUS] [varchar](20) NULL DEFAULT ('Open'),
  [QUOTESTATUSCOMMENT] [varchar](100) NULL,
  [CUSTALLOWSHORTSUPPLY] [varchar](10) NULL DEFAULT ('ALL'),
  [PICKINGINPROGRESSBY] [varchar](100) NULL,
  [PACKINGINPROGRESSBY] [varchar](100) NULL,
  [MANFIESTED] [varchar](5) NULL DEFAULT ('FALSE'),
  CONSTRAINT PK_INVOICEDOCUMENTSTEST2 PRIMARY KEY CLUSTERED (INVOICEINDEXNUMBER))

DECLARE @i INT;
SELECT @i = 1;
SET NOCOUNT ON
WHILE @i <= 235605
BEGIN
  INSERT INTO INVOICEDOCUMENTSTEST2(INVOICEINDEXNUMBER) VALUES (@i);
  SELECT @i = @i + 1;
END;

The first table is a mini version of the second, with only the primary index created.

I have inserted 235605 rows as that's what I have in the live database.

I have a query to get the table and index size:

SELECT
  T.NAME AS TABLENAME,
  '' AS INDEXNAME,
  P.ROWS AS ROWCOUNTS,
  SUM(A.TOTAL_PAGES) * 8 AS TOTALSPACEKB,
  SUM(A.USED_PAGES) * 8 AS USEDSPACEKB,
  (SUM(A.TOTAL_PAGES) - SUM(A.USED_PAGES)) * 8 AS UNUSEDSPACEKB
FROM
  SYS.TABLES T
INNER JOIN
  SYS.INDEXES I ON T.OBJECT_ID = I.OBJECT_ID
INNER JOIN
  SYS.PARTITIONS P ON I.OBJECT_ID = P.OBJECT_ID AND I.INDEX_ID = P.INDEX_ID
INNER JOIN
  SYS.ALLOCATION_UNITS A ON P.PARTITION_ID = A.CONTAINER_ID
LEFT OUTER JOIN
  SYS.SCHEMAS S ON T.SCHEMA_ID = S.SCHEMA_ID
WHERE
  T.NAME NOT LIKE 'dt%'
  AND T.IS_MS_SHIPPED = 0
  AND I.OBJECT_ID > 255
GROUP BY
  T.NAME, S.NAME, P.ROWS

UNION ALL

SELECT
  OBJECT_NAME(I.OBJECT_ID) AS TABLENAME,
  I.NAME AS INDEXNAME,
  0 AS ROWCOUNTS,
  8 * SUM(A.USED_PAGES) AS TOTALSPACEKB,
  8 * SUM(A.USED_PAGES) AS USEDSPACEKB,
  0 AS UNUSEDSPACEKB
FROM
  SYS.INDEXES AS I JOIN
  SYS.PARTITIONS AS P ON P.OBJECT_ID = I.OBJECT_ID AND P.INDEX_ID = I.INDEX_ID JOIN
  SYS.ALLOCATION_UNITS AS A ON A.CONTAINER_ID = P.PARTITION_ID
WHERE
  OBJECT_NAME(I.OBJECT_ID) NOT LIKE 'sys%%'
  AND OBJECT_NAME(I.OBJECT_ID) NOT LIKE 'dt%%'
  AND OBJECT_NAME(I.OBJECT_ID) NOT LIKE 'filestream%%'
  AND OBJECT_NAME(I.OBJECT_ID) NOT LIKE 'filetable%%'
  AND OBJECT_NAME(I.OBJECT_ID) NOT LIKE 'plan_%%'
  AND OBJECT_NAME(I.OBJECT_ID) NOT LIKE 'queue_%%'
  AND OBJECT_NAME(I.OBJECT_ID) NOT LIKE 'sqlagent_%%'
GROUP BY
  I.OBJECT_ID,
  I.INDEX_ID,
  I.NAME

ORDER BY 1, 2

When I run the size query across these two tables, I get the following:

TableName             indexname                 RowCounts   TotalSpaceKB
INVOICEDOCUMENTSTEST1                             235605         7560
INVOICEDOCUMENTSTEST1 PK_INVOICEDOCUMENTSTEST1         0         7504
INVOICEDOCUMENTSTEST2                             235605       210584
INVOICEDOCUMENTSTEST2 PK_INVOICEDOCUMENTSTEST2         0       210496

This shows that the row data itself only takes up a fraction of the data. The index on only that field takes approx 7.5mb. However with those extra fields on the table, it expands the primary key out to 210mb, even though all those fields are blank and the same field is the only one in the primary key.

This appears to be an impact of how MS SQL want to access the data. If the primary key on Table 2 is created as 'nonclustered' then the index is smaller but there is a 'hidden' index created that is the same size as a clustered index anyway.

I have found that with the size of some of my tables, that even though the primary key is on a BCD file, the indexes are increased the database to 2-3x the size of the data.

In one case it's pushing the database over the 10gb Express limit so it's causing a financial problem as well.

Is there any other way to structure the table so that you don't give up so much space to the index?

  • 2
    So you create a very narrow table and an extremely wide table.. then insert the same amount of data and expect there to be no size difference? Every column you add to the table has overhead. If you expect 75% of all rows to be mostly null columns, then SPARSE may be for you... however looking at the DDL I highly doubt that will give you any space savings. Even so, 200MB is nothing. – Sean Gallardy Feb 6 '17 at 4:40
  • 1
    Your table looks like it should be split up into at least 6-7 smaller tables. Please, please split this into multiple tables. Also, is there a reason you're storing both the date and time in two separate datetime fields? A better practice would be to unpack the date pieces as needed. – SQL_Underworld Feb 6 '17 at 5:01
  • @SeanGallardy without understanding the pre-alloation of space in the rows being the problem rather than the index being the problem, yes I did expect them to be similar. I falsely assumed an empty row was just that. Based on napkin maths, each row is 900 bytes, which I was not aware of. – Darren Feb 6 '17 at 6:22
  • @SQL_Underworld splitting the table would not save any space, due to the same logic as has been outlined in the answer. This is a legacy app from old flat file databases, so changes are hard, especially with thousands of linked and custom reports that rely on those fields. – Darren Feb 6 '17 at 6:24
  • @Darren - Right, it was impossible to know it's the backend to a legacy app, however, since that information wasn't provided. Does the database contain data as old as legacy app itself? If so, can you archive any of it? – SQL_Underworld Feb 6 '17 at 6:43
4

SQL is pre-allocating the space for the fixed-length datatypes in your table (datetime 8b per field per row, numeric(10/14, x) 9b per field per row), and possibly 'stubs' for the variable length datatypes. It doesn't matter if they're null or not. This is by design so that the rows are inserted and updated efficiently. The varchar fields with defaults will also have space allocated to them appropriate to the size of the default. It also means your table is going to get a LOT bigger once you start populating the currently-null varchar fields.

A clustered index and the 'hidden' index you refer to (also known as a heap) aren't actually indexes as such; they're actually the table itself, and so will take up the space required to store the table data.

To try and compact your data, you can try to use some smaller datatypes in places. For example, date and time in place of datetime where appropriate, smaller precision numeric fields (numeric(9, x) and lower is 5 bytes), using integer types tinyint/smallint/int instead of numeric(10,0) for whole numbers, and using a bit field to represent true/false instead of varchar(5) (I count 11 true/false fields, which can be covered by 2b worth of bit fields instead of the 66-77b they currently occupy).

Beyond that, there appears to be a lot of normalisation that can be done on the table which could reduce the table size; e.g. can you combine invoice date & invoice time into a single datetime or datetime2 field? Address, staff/user details, comments and many others can be pushed out into lookup tables and a foreign key added to this table. There also seems to be a lot of calculated values which may be better created as calculated columns (or the logic moved into the app if there is one) rather than stored as an actual field.

Finally, you can look into sparse columns, where null values do not take up any space. However looking at the table I don't think they'll help much, as it looks like only a low number of columns will actually contain null data in normal use.

  • Thanks, I was mislead by the index hiding the 'heap' (which I did discover with the non clustered index but still didn't know what it was). Due to the existing application logic splitting out into seperate tables is hard and would require a rewrite of most of the forms. Unfortuntately a lot of the fields are there due to reporting requirements as well. – Darren Feb 6 '17 at 6:27
  • As mentioned above, numeric(10) is a poor choice for PK column data type, as it just barely exceeds the 9-precision cap for 5 bytes. Int provides nearly as many numbers for 5 bytes less, and bigint provides significantly more for 1 byte less. Not only will it reduce the column size per row, but for the nodes in the index B-tree. – Arin Taylor Feb 6 '17 at 10:36
  • Thanks @ArinTaylor. Currently the app maintains compatibility between Firebird and SQL Server. I am dumping the Firebird option now that SQL Server has a larger Express version DB limit (way back it was too small), then migrating the Firebird clients to SQL Server. As part of that process I will change the field types over as well, since I won't need to maintain any cross-database code. I have a comprehensive set of libraries to deal with database modifications. – Darren Feb 7 '17 at 4:27
1

The index isn't the problem. You can see this yourself by creating the data as a heap (no clustered index) and comparing the space used by both tables.

sp_spaceused 'INVOICEDOCUMENTSTEST2_HEAP';

GO

sp_spaceused 'INVOICEDOCUMENTSTEST2';

GO

╔════════════════════════════╦════════╦═══════════╦═══════════╦════════════╦════════╗
║            name            ║  rows  ║ reserved  ║   data    ║ index_size ║ unused ║
╠════════════════════════════╬════════╬═══════════╬═══════════╬════════════╬════════╣
║ INVOICEDOCUMENTSTEST2_HEAP ║ 235605 ║ 235720 KB ║ 235664 KB ║ 8 KB       ║ 48 KB  ║
║ INVOICEDOCUMENTSTEST2      ║ 235605 ║ 210632 KB ║ 209432 KB ║ 1048 KB    ║ 152 KB ║
╚════════════════════════════╩════════╩═══════════╩═══════════╩════════════╩════════╝

For your example data, putting a clustered index on the table actually reduced the overall space needed for the table.

Storing less redundant data as Gareth Lyons suggested is a good idea. Another idea is to use page compression. Page compression is available in SQL Server Express starting with the 2016 SP1 edition. Page compression implements row compression which offers a number of benefits for your table structure:

It reduces the metadata overhead that is associated with the record. This metadata is information about columns, their lengths and offsets. In some cases, the metadata overhead might be larger than the old storage format.

It uses variable-length storage format for numeric types (for example integer, decimal, and float) and the types that are based on numeric (for example datetime and money).

NULL and 0 values across all data types are optimized and take no bytes.

Prefix compression and dictionary compression may also save you space. I won't reproduce the details for those algorithms in this answer.

Applying compression is usually not a neutral change from a CPU point of view. CPU utilization can increase or decrease after applying compression. All you can do is test. For your sample data, which probably isn't realistic, the table with page compression is 10X smaller than the original table. As explained above, compression can offer big space savings when a lot of columns are NULL.

╔══════════════════════════════════╦════════╦══════════╦══════════╦════════════╦════════╗
║               name               ║  rows  ║ reserved ║   data   ║ index_size ║ unused ║
╠══════════════════════════════════╬════════╬══════════╬══════════╬════════════╬════════╣
║ INVOICEDOCUMENTSTEST2_COMPRESSED ║ 235605 ║ 21064 KB ║ 20960 KB ║ 96 KB      ║ 8 KB   ║
╚══════════════════════════════════╩════════╩══════════╩══════════╩════════════╩════════╝

For your real data I expect the compression ratio to not be as high. You also may need to move the ntext columns to another table to get the best compression ratio. By the way, ntext is deprecated. Switch to NVARCHAR(MAX).

  • Thanks for the extra information. Compression won't be an option as not all clients are on 2016, but might be something I can look at later. A lot of the data is duplicated so this would help. – Darren Feb 6 '17 at 6:31
  • Good point on compression, I'd just assumed it wasn't available in Express even in 2016 SP1! – Gareth Lyons Feb 6 '17 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.