Adding PERSISTED Computed Column = INDEX Defragmentation?

Question

Adding PERSISTED Computed Column = INDEX Defragmentation?

Andre Guerreiro

SSCertifiable

Points: 7319
More actions
September 6, 2011 at 8:50 pm

#390354

Hi, guys.
I'm not sure this is documented but I thought I'd share this with you and maybe someone will light the path ahead. 🙂
I was trying to create a simple test table:
IF OBJECT_ID('dbo.TablePerson') IS NOT NULL
DROP TABLE dbo.TablePerson;
GO
CREATE TABLE dbo.TablePerson
(
IdPerson INT IDENTITY,
Name VARCHAR(100) NOT NULL,
LastName VARCHAR(100) NOT NULL,
FullName AS Name + ' ' + LastName,
CONSTRAINT PK_TablePerson PRIMARY KEY(IdPerson )
);
INSERT INTO dbo.TablePerson VALUES('Andre', 'Guerreiro');
GO 1000
That would return a satisfying avg_fragmentation_in_percent value:
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.TablePerson'), 1, DEFAULT, DEFAULT);
Then I wanted to change the computed column and turn it into a PERSISTED computed column:
ALTER TABLE dbo.TablePerson
DROP COLUMN FullName;
GO
ALTER TABLE dbo.TablePerson
ADD FullName AS Name + ' ' + LastName PERSISTED;
GO
But after running the sys.dm_db_index_physical_stats function again I get 98% fragmentation in the primary key. Is that normal?
Do I have to use "ALTER INDEX PK_TablePerson ON dbo.TablePerson REBUILD;" after adding any persisted computed column? I'm not sure I get what's going on internally.
I'd love if someone could clarify this behaviour for me.
Thanks in advance.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Sure, it makes sense. You've widened the rows (by adding another column), SQL's going to have to split pages whenever the rows will no longer fit.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 2

Surely but almost 100% fragmentation? The table has only a few columns and any row doesn't even have 500 bytes each. Perhaps I'm missing an important point here? Would that column be making every index page split to the point of almost 100% fragmentation?

Thank you for your help.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If every page is full (not uncommon), adding a column could easily make every single page split because there won't be space on the full pages for the new column.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 4

When you make a computed column as persisted, it would consume more space in disk. If Persisted property is off then calculated column will be just a virtual column. No data for this column will be stored on disk and values will be calculated every time when referenced in a script. If this property is set active then data of computed column will be stored on disk.

You may check the page count and fragment count to see the same.

http://www.mssqltips.com/sqlservertip/1682/using-computed-columns-in-sql-server-with-persisted-values/

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 5

GilaMonster (9/7/2011)
If every page is full (not uncommon), adding a column could easily make every single page split because there won't be space on the full pages for the new column.

I think I see what you mean. I'm probably confusing the concept of free space for new rows (defined with FILLFACTOR) with new space for existing rows. Please check to see if my thinking is right.

If my clustered index's fill factor is set to 80% then 20% will be free for new rows. Adding a new column will not use that space and the page needs to be split to acomodate the new column but also the 20% free space will need to be kept. Would that be correct?

Thank you for all your help.

@sqlzealot-81: Thank you. I will take a look at that article. I'm still thinking if PERSISTED computed columns are worth using. 🙂

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

codebyo (9/8/2011)
If my clustered index's fill factor is set to 80% then 20% will be free for new rows. Adding a new column will not use that space and the page needs to be split to acomodate the new column but also the 20% free space will need to be kept. Would that be correct?

No.

Fill factor is set on rebuilds only. Anything that needs to grow/insert on the page will use the free space..

You could still easily end up splitting. In your example there's no fill factor set, so unless it's an overall database setting (bad idea) those pages will be 100% fill. Even if they are left 20% full...

Your rows are about 20 bytes in size. (int = 4, varchar = size of data + 2). So simplifying the maths, ignoring row headers, null bitmaps, the slot array and other such details, and with 20% fill factor that's 6448/20 rows per page. 320 for a nice round number. There will be 1660 bytes left free.

Persisting the column widens the row by 17 bytes. To widen all the rows on a page needs 17*320 bytes. That's 5 440 bytes, more than 3 times the space that's actually left free on the page. That page, and every other page in that index is splitting, possibly more than once.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Bear in mind, this only happens when you add the column. Not something you'll be doing every day and not much different from adding a normal column.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 8

Thank you for the detailed explanation, GilaMonster.

I will study your post a little more and you're right.

In the company I work for we don't have DBAs so it's basically us developers doing the dirty work. After some analysis I found out that almost all of the indexes which are highly fragmented are ones which had columns added somewhen.

Thanks again.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

codebyo (9/8/2011)
In the company I work for we don't have DBAs so it's basically us developers doing the dirty work. After some analysis I found out that almost all of the indexes which are highly fragmented are ones which had columns added somewhen.

Unless you're adding columns every second week, most of the fragmentation should be from inserts and updates, or maybe (horror) from database shrinks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass