7

The company I work for has a couple of SQL Server databases with tables containing +- 500.000.000 rows. We are running Enterprise editions of SQL Server 2008R2 and 2014.

Big data types

When I look at the used data types in the largest table I see a lot of BIGINT columns. Examining the data in those columns with a script from Thomas Larock and scripting the MIN() and MAX() values myself I concluded that data in those BIGINT columns can easily be fitted in INT or even SMALLINT/TINYINT columns. (I'm aware that some columns could need the range of BIGINT in the future,so I'm not blindly changing all the data types without talking to developers first)

When comparing the possible savings when changing the datatypes it seems the table could be half the size it is currently (not even considering indexes and other tables). These numbers are without any data compression.

ROW Compression

On the large table ROW Compression is enabled. I'm wondering what the actual impact of 'downsizing' the columns datatypes might be, keeping in mind that ROW compression is using only the bytes that are needed. For example, if a value can be stored in 1 byte, storage will take only 1 byte.

Actual question

Would it help to downsize data types, so the ROW compression uses less resources? Or is it save to say 'because ROW compression is enabled there is no difference between BIGINT, INT or SMALLINT data types'?

4

As the documentation you alredy have linked states, ROW compression uses only the bytes that are needed. Once ROW compression is in use, the CPU cycles used to convert from/to int or bigint are the same: I would not worry about that.

BTW, if you're not sure whether int/bigint has an impact on the database size or not (it hasn't), you can see for yourself with a quick and dirty repro:

USE tempdb;
GO

CREATE TABLE SomeTable (
    SomeColumn bigint
)
GO

ALTER TABLE [dbo].[SomeTable] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);

INSERT INTO SomeTable 
SELECT TOP 10000000 ROW_NUMBER() OVER (ORDER BY(SELECT NULL))
FROM sys.all_columns AS A1
CROSS JOIN sys.all_columns AS A2;
GO

-- Rebuild the heap, so that pages compact nicely
ALTER TABLE dbo.SomeTable 
REBUILD 
WITH 
(
    MAXDOP = 1, 
    ONLINE = OFF,
    FILLFACTOR = 100,
    PAD_INDEX = OFF
);
GO

SELECT SUM(page_count)
FROM sys.dm_db_index_physical_stats(
        DB_ID(),
        OBJECT_ID('SomeTable'),
        DEFAULT,
        DEFAULT,
        'detailed'
    ) AS ips;


-- 21009 pages used


ALTER TABLE [dbo].[SomeTable] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);
GO


SELECT SUM(page_count)
FROM sys.dm_db_index_physical_stats(
        DB_ID(),
        OBJECT_ID('SomeTable'),
        DEFAULT,
        DEFAULT,
        'detailed'
    ) AS ips;

-- 13587 pages used


ALTER TABLE SomeTable ALTER COLUMN SomeColumn int;

-- Rebuild the heap, so that pages compact nicely
ALTER TABLE dbo.SomeTable 
REBUILD 
WITH 
(
    MAXDOP = 1, 
    ONLINE = OFF,
    FILLFACTOR = 100,
    PAD_INDEX = OFF
);
GO


SELECT SUM(page_count)
FROM sys.dm_db_index_physical_stats(
        DB_ID(),
        OBJECT_ID('SomeTable'),
        DEFAULT,
        DEFAULT,
        'detailed'
    ) AS ips;

-- 13587 pages used (same as bigint)
  • Thank you for the repro. Can you tell me more about the usage of CPU? – Ruud van de Beeten Nov 3 '15 at 12:15
  • Say the table contains 32,000 rows with an ID column (BIGINT) with values ranging from 1 to 32,000. In this case all values would fit in a SMALLINT. The values from 1 to 255 would be compressed to 1 byte. The other values would be compressed to an SMALLINT. All rows would be compressed, and thus cost CPU. When the datatype is changed to a SMALLINT, I would think SQL Server knows it can only compress the values from 1 to 255, and only uses CPU for those rows. Is this correct? – Ruud van de Beeten Nov 3 '15 at 12:25
  • @RuudvandeBeeten the CPU cost is usually very low for ROW compression. It's just a different way of storing the data in the page, there's no prefix/dictionary matching as with PAGE compression. Seriously: don't worry about that at all. – spaghettidba Nov 3 '15 at 13:40

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.