Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding PERSISTED Computed Column = INDEX Defragmentation? Expand / Collapse
Author
Message
Posted Tuesday, September 6, 2011 8:50 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 15, 2014 3:02 AM
Points: 912, Visits: 1,500
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
Post #1170811
Posted Wednesday, September 7, 2011 12:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1170845
Posted Wednesday, September 7, 2011 1:04 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 15, 2014 3:02 AM
Points: 912, Visits: 1,500
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
Post #1170853
Posted Wednesday, September 7, 2011 1:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1170880
Posted Wednesday, September 7, 2011 2:38 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 3:47 AM
Points: 731, Visits: 2,042
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/
Post #1170899
Posted Thursday, September 8, 2011 1:06 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 15, 2014 3:02 AM
Points: 912, Visits: 1,500
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,


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Post #1172094
Posted Thursday, September 8, 2011 1:17 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1172100
Posted Thursday, September 8, 2011 1:21 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1172101
Posted Thursday, September 8, 2011 2:16 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 15, 2014 3:02 AM
Points: 912, Visits: 1,500
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
Post #1172121
Posted Thursday, September 8, 2011 3:00 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1172154
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse