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

Clustered Index Showing Twice Expand / Collapse
Author
Message
Posted Wednesday, January 19, 2011 1:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 12, 2013 9:14 AM
Points: 67, Visits: 299
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
Posted Wednesday, January 19, 2011 2:09 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: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
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
Posted Wednesday, January 19, 2011 3:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 12, 2013 9:14 AM
Points: 67, Visits: 299
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
Posted Wednesday, January 19, 2011 3:46 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: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
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
Posted Thursday, January 20, 2011 7:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 12, 2013 9:14 AM
Points: 67, Visits: 299
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
Posted Thursday, January 20, 2011 7:59 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: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
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
Posted Thursday, January 20, 2011 8:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 12, 2013 9:14 AM
Points: 67, Visits: 299
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
Posted Thursday, January 20, 2011 8:22 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: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
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
Posted Thursday, January 20, 2011 8:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 12, 2013 9:14 AM
Points: 67, Visits: 299
Understood, thanks again for your help. Its much appreciated.
Post #1050849
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse