SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding PERSISTED Computed Column = INDEX Defragmentation?


Adding PERSISTED Computed Column = INDEX Defragmentation?

Author
Message
Andre Guerreiro
Andre Guerreiro
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1335 Visits: 1515
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. Smile

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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86653 Visits: 45254
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
Andre Guerreiro
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1335 Visits: 1515
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,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86653 Visits: 45254
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
Latheesh NK
SSC Eights!
SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)

Group: General Forum Members
Points: 925 Visits: 2164
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
Andre Guerreiro
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1335 Visits: 1515
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. Smile

Best regards,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86653 Visits: 45254
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86653 Visits: 45254
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
Andre Guerreiro
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1335 Visits: 1515
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,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86653 Visits: 45254
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search