Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question has been asked before ( speed impact of using varchar in MSSQL 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!

share|improve this question
    
Yes, which is what Option A suggests. The question is, what is the performance impact if that's not how it was implemented? –  M Afifi May 11 '12 at 13:31
    
+1 I love questions like this. I know you have already marked an answer, but I am going to post my own answer, in order to highlight some of the internals and show how to look into this behavior yourself. –  Matt M May 11 '12 at 14:28
    
@MattM nothing wrong with accepting two answers ;-) –  M Afifi May 11 '12 at 14:48
    
Honestly, JNK deserves the answer on this one, because he answered the question quickly, accurately, and succinctly. My answer is just a glimpse into the internals. –  Matt M May 14 '12 at 10:35
    
Its disappointing you can't mark multiple answers as the correct one. –  M Afifi May 14 '12 at 10:36
add comment

2 Answers 2

up vote 3 down vote accepted

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.
share|improve this answer
    
thanks. So it sounds like the worst possible scenario is a page split. Rearranging the page is not very costly as it would be a single 8KB write. A page split would be two 8KB writes with whatever set of indexes/links being updated to include the new page? –  M Afifi May 11 '12 at 13:35
    
A page split would be allocation of one new page, and migrating existing data to the new page. It also increases your fragmentation. –  JNK May 11 '12 at 13:38
    
Thanks again for the detailed response. No surprises but good to understand the reason for the performance degredation. –  M Afifi May 11 '12 at 13:46
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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