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


Clustered Index Showing Twice


Clustered Index Showing Twice

Author
Message
paul.corujo
paul.corujo
SSC-Addicted
SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)

Group: General Forum Members
Points: 461 Visits: 301
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230578 Visits: 46346
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
paul.corujo
SSC-Addicted
SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)

Group: General Forum Members
Points: 461 Visits: 301
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230578 Visits: 46346
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
paul.corujo
SSC-Addicted
SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)

Group: General Forum Members
Points: 461 Visits: 301
I understand, I was under the impression for whatever reason that all nvarchar data types were considered LOB.

Thank you for your help.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230578 Visits: 46346
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
paul.corujo
SSC-Addicted
SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)

Group: General Forum Members
Points: 461 Visits: 301
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230578 Visits: 46346
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
paul.corujo
SSC-Addicted
SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)

Group: General Forum Members
Points: 461 Visits: 301
Understood, thanks again for your help. Its much appreciated.
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