varchar performance impact

Question

This question has been asked before ( Speed impact of using varchar in SQL Server 2008 ), but the answers are not detailed enough to my satisfaction. Admitedly this is not a current issue and mainly for my education.

However, I will try and make this a concrete example from some current DB schema work I am doing;

Option A

CREATE TABLE [VMware].[TblHostSystem](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    <snip/>
    [PowerStateID] [int] NOT NULL
)

ALTER TABLE [VMware].[TblHostSystem]  WITH
CHECK ADD  CONSTRAINT [FK_TblHostSystem_TblPowerState]
FOREIGN KEY([PowerStateID])
REFERENCES [VMware].[TblPowerState] ([ID])
GO

CREATE TABLE [VMware].[TblPowerState](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PowerState] [varchar](50) NOT NULL
)

Option B

CREATE TABLE [VMware].[TblHostSystem](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    <snip/>
    [PowerState] [varchar](50) NOT NULL
)

If I've chosen option B (as the previous author of the schema has), as the power of a host is something that will frequently change, what sort of churn will this create?

Some statements that I believe are true,

1. Varchars are stored in the same row/page on MSSQL 2008.
2. Varchars are not padded?

So my question is, when updating PowerState in the second example from 'poweredOn' (20 bytes) to 'poweredOff' (22 bytes) what does the DB need to do?

1. Move the row to a new page because there is now room for the extra 2 bytes?
2. Move everything after the current row 2 bytes forward because there is a cluster index on ID and the order on disk has to be maintained?
3. None of the above, the database actually allocates 102 bytes (maximum possible size of varchar(50)) and has loads of free space most of the time to accomodate such changes without massive churn?
4. Breakout the varchar(50) column to another page to avoid the massive churn caused by 1, and 2, and it doesn't pad out as 3, but it means locking potentially multiple pages per row and two reads off the disk (maybe) rather than one.

Would appreciate a detailed of what happens under the covers!

Answer 1

First questions -

• Yes they are stored on page normally (for non-(MAX) values)
• They are not padded, but there is a 2 byte per row overhead to store the length of the field

1 - IT DEPENDS on if the page is full or not. Very likely it will fit on the current page. The header record for the row needs to updated as well to update the length of the field.

2 - Data will only be rearranged at the page level for an insert or update, so nothing else gets shifted. If it fits on your current page it will, if not there will be a page split.

3 - Never as far as I know for varchar - if you want this behavior you can make it a CHAR instead.

4 - Only if the value exceeds the space on the page.

As to the design itself...

You will definitely get better performance by using the id instead of a varchar.

• An int or smallint is orders of magnitude smaller than your strings.
• Index lookups are a lot more efficient on integers than on strings
• Data stored on the page will be consistent and much smaller
• SQL likes keys! A lot of optimizations can take place when you use PK/FK relationships because the engine can infer some metadata about duplicates/distinct values, etc.

Answer 2

Short answer:

An update to a row that causes a storage expansion will force SQL Server to move the new row to the end of the page, while zeroing out the space that was previously used. If this process would end up taking more space than the page can allocate, a page split will happen.

The proof:

Disclaimer: DBCC IND and DBCC PAGE are undocumented. Use at your own risk.

Setup (Assumes existence of database named test):

USE test;
GO

SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

SET ANSI_PADDING ON;
GO

CREATE TABLE [dbo].[varchar_test]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PowerState] [varchar](50) NOT NULL,
    CONSTRAINT [pk_varchar_test] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )
    WITH 
    (
        PAD_INDEX  = OFF, 
        STATISTICS_NORECOMPUTE  = OFF, 
        IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS  = ON, 
        ALLOW_PAGE_LOCKS  = ON
    ) ON [PRIMARY]
) ON [PRIMARY];
GO

SET ANSI_PADDING OFF;
GO

We need to create some test data:

INSERT INTO dbo.varchar_test
(
    PowerState
)
SELECT 
    'PoweredOn'
GO 10
;

We need to redirect DBCC output to the client, instead of the error log:

DBCC TRACEON(3604);
GO

We need to find which page is storing the data for this table (PageType 1 is "Data"):

DBCC IND(test, varchar_test, 1);
GO

In my scenario, page 195 contains my data. Now, I need to look at the details for the page. Specifically, I need to know which offsets the rows are stored on:

DBCC PAGE(test, 1, 195, 1);
GO

Scroll to the bottom, and have a look at the offset table. In my scenario, the first row, which contains ID = 1 and PowerState = 'PoweredOn', begins at offset 0x60. The next row starts at 0x78. (24 bytes per row)

Now, I want to change the PowerState value of ID 1:

UPDATE dbo.varchar_test
    SET
        PowerState = 'PoweredOff'
WHERE dbo.varchar_test.ID = 1;
GO

Run the same DBCC PAGE command, and have a look at the offset table again. You will notice that row 0 (ID 1) has been moved to Offset 0x150. What does this tell us? SQL Server does not shift the contents of the page over to make room for an update to a row that requires more storage than initially allocated. Instead, it will write the updated row to the next available chunk of sufficient contiguous storage. Writing 24 bytes is more efficient than writing 8192 bytes, afterall.

If you want to observe a page split:

TRUNCATE TABLE dbo.varchar_test;
GO

DBCC CHECKIDENT('dbo.varchar_test', RESEED, 1);
GO

INSERT INTO dbo.varchar_test
(
    PowerState
)
SELECT 
    'PoweredOn';
GO 311

DBCC IND(test, varchar_test, 1);
GO

DBCC PAGE(test, 1, 195, 1);
GO

Row 199 (ID 200) begins at offset 0xc7 Let's update that row:

UPDATE dbo.varchar_test
    SET
        PowerState = 'PowerPowerPowerPowerPowerPowerPowerPowerPowerPower'
WHERE dbo.varchar_test.ID = 200;
GO

DBCC IND will show that a new page was written. In my scenario, it is page 198. If you look at DBCC PAGE for both 195 and 198, you will see that the offset table has been halved for page 195, and the remainder was moved to 198.

Hope this helps explain the behavior.

Have a good one,

Matt