CODEWITHSUNDEEP
sql-serverindexingsqldatatypesfillfactor
Eric J. Price
Eric J. Price

Reputation: 2785

Page Split caused by update to Binary(128) Column

I have a table with a Binary(128) column. I chose Binary(128) over Varbinary(128) so it would reserve all 128 bytes and prevent page splits when updating the binary value. For testing purposes I have a FILLFACTOR of 100 on my index.

I found that even with the column set to a Binary(128) value it would still cause page splits when performing updates to this column.

Anyone have any idea why?

Here is some code to test with...

--      Create working dataset
Create  Table   TestTable (tID Int Identity, TestBinary Binary(128));
Create  Clustered Index hmmmm On TestTable (TestBinary) With (FillFactor=100);

With    recur As
(   
        Select  Convert(Binary(128),0x01) As val, 1 As incr
        Union   All
        Select  Convert(Binary(128),incr + 0x01), incr + 1
        From    recur
        Where   incr < 100
)
Insert  TestTable (TestBinary)
Select  Convert(Binary(128),Convert(Int,r.val) + Convert(Int,r2.val) + Convert(Int,r3.val)) As TestBinary
From    recur r
Cross   Join    recur r2
Cross   Join    recur r3;

--      Rebuild Index as needed while testing
Alter   Index [hmmmm] On [dbo].[TestTable] Rebuild

--      Check index fragmentation
SELECT  Db_Name() As DatabaseName,
        o.id,
        s.name, 
        o.name, 
        page_count,
        record_count,
        index_type_desc,
        index_id,
        index_depth,
        index_level,
        avg_fragmentation_in_percent,
        fragment_count,
        avg_fragment_size_in_pages,
        avg_page_space_used_in_percent
From    sys.dm_db_index_physical_stats (DB_ID(), Object_ID('dbo.TestTable'), NULL , NULL, 'Detailed') n
Join    sysobjects o
        On  n.object_id = o.id
Join    sys.schemas s
        On  o.uid = s.schema_id;

--      Update the records
Update  t
Set     TestBinary = Convert(Binary(128),(Convert(Int,TestBinary) + 10000))
From    TestTable t

If you perform a large update with the FILLFACTOR at 100 it causes severe fragmentation, but if I have the FILLFACTOR at 90 everything is good. I thought the non-var datatypes were supposed to reserve memory so you don't run into this issue. Where is the storage inflation coming from?

Thanks!

Upvotes: 0

Views: 306

Answers (1)

Mark S. Rasmussen
Mark S. Rasmussen

Reputation: 35486

It may not be inflation, but rather moving. As you've made a clustered index on your binary(128) column, the data will be sorted according to the value.

As you update the value, the physical record may have to be stored on another page, to maintain the order. If that other page is full, it will have to be split.

Upvotes: 3

Related Questions