Clustered Index Showing Twice

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Clustered Index Showing Twice

Clustered Index Showing Twice

Rate Topic

Display Mode

Topic Options

Author

Message

paul.corujo

Posted Wednesday, January 19, 2011 1:29 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, September 27, 2012 9:47 AM
Points: 67, Visits: 295

Hi All,

I have a table with a primary key clustered index created on the id column. Recently a column of type nvarchar has been added to that table and the online reindex job has failed. When I check sys.dm_db_index_physical_stats it shows the following:
ObjectID /Indexid /partition /type /alloc_unit_type_desc
1494296383 1 1 CLUSTERED INDEX IN_ROW_DATA
1494296383 1 1 CLUSTERED INDEX LOB_DATA

Why would this be when the index is explicitly on only the id column?

Thank you all in advance,
Paul

Post #1050345

GilaMonster

Posted Wednesday, January 19, 2011 2:09 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

The clustered index IS the table. It has at the leaf level all the columns in the table. Hence you added a LOB column to the clustered index (which is why physical stats now shows a LOB_DATA allocation unit) hence the online index rebuild fails.

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 #1050372

paul.corujo

Posted Wednesday, January 19, 2011 3:32 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, September 27, 2012 9:47 AM
Points: 67, Visits: 295

Thank you for your response. My maintenance plan script identifies which indexes can be rebuilt online and takes all indexes with over 30% fragmentation and rebuilds them. This is a pretty standard script. On all of my dev boxes with the same db this script runs fine. In production it does not. This is also not the first nvarchar column, there already were several columns with this data type in the table.

Can you think of any reason why this fails? Almost all of the tables contain LOB columns and the script has run fine for some time now.

Thanks again for all of your help, it is much appreciated.

Post #1050421

GilaMonster

Posted Wednesday, January 19, 2011 3:46 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

nvarchar is not a LOB data type. nvarchar(MAX) is. I don't know how the script works, but it must be misidentifying that this table has a LOB 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 #1050424

paul.corujo

Posted Thursday, January 20, 2011 7:55 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, September 27, 2012 9:47 AM
Points: 67, Visits: 295

I understand, I was under the impression for whatever reason that all nvarchar data types were considered LOB.

Thank you for your help.

Post #1050813

GilaMonster

Posted Thursday, January 20, 2011 7:59 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

No. LOB data types are:
TEXT (deprecated)
NTEXT (deprecated)
IMAGE (deprecated)
VARCHAR(MAX)
NVARCHAR(MAX)
VARBINARY(MAX)
XML

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 #1050817

paul.corujo

Posted Thursday, January 20, 2011 8:05 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, September 27, 2012 9:47 AM
Points: 67, Visits: 295

Perfect, now if it is possible to avoid these data types should I see performance benefits? I am wondering if the developers just choose these as arbitrary values without knowing they were needed to meet the business rules.

Post #1050822

GilaMonster

Posted Thursday, January 20, 2011 8:22 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

Yes, they're probably stored out of row and if they are, reading them (the LOB column, not the rest of the row) incurs additional reads.

That said, if you need them, use them. If you need to store 40k of text in a column, use varchar(max) or nvarchar(max)

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 #1050845

paul.corujo

Posted Thursday, January 20, 2011 8:24 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, September 27, 2012 9:47 AM
Points: 67, Visits: 295

Understood, thanks again for your help. Its much appreciated.

Post #1050849

« Prev Topic | Next Topic »

Permissions