Clustered Index Showing Twice

Question

Clustered Index Showing Twice

paul.corujo

SSCommitted

Points: 1891
More actions
January 19, 2011 at 1:29 pm

#233073

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
149429638311CLUSTERED INDEXIN_ROW_DATA
149429638311CLUSTERED INDEXLOB_DATA
Why would this be when the index is explicitly on only the id column?
Thank you all in advance,
Paul

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

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

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

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, 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

paul.corujo SSCommitted Points: 1891 More actions · Answer 2

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.

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

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, 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

paul.corujo SSCommitted Points: 1891 More actions · Answer 4

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

Thank you for your help.

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

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, 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

paul.corujo SSCommitted Points: 1891 More actions · Answer 6

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.

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

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, 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

paul.corujo SSCommitted Points: 1891 More actions · Answer 8

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