Rebuilding/Reorganizing clustered or non-clustered indexes

Question

Rebuilding/Reorganizing clustered or non-clustered indexes

chileu17

Hall of Fame

Points: 3093
More actions
September 25, 2008 at 11:31 am

#191418

Hi everyone, I found this in a website
"...Another point to note is that an index (clustered or non-clustered) cannot be built online if it contains LOB data (text, ntext, varchar(max), nvarchar(max), varbinary(max), image and xml data type columns)..."
I would like to know if somebody knows why this is. My first guess is because those types of fields are way too big and it would take too much time and resources in general to get it done. Am I right? or is it anything to do with SQL Server 2K5 restrictions?
I am worried because on my development enviroment I am rebuiling some indexes and it's been more than 10 hours now, and nothing yet :unsure: Any suggestions or whatever is welcomed. Thanks in advance!!!!!

Viewing 6 posts - 1 through 6 (of 6 total)

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

The first thing I would do is: "exec sp_who2 active" just to see if there's anything happening. Your rebuild be be block by another process.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

noeld SSC Guru Points: 96590 More actions · Answer 2

chileu17 (9/25/2008)
Hi everyone, I found this in a website
"...Another point to note is that an index (clustered or non-clustered) cannot be built online if it contains LOB data (text, ntext, varchar(max), nvarchar(max), varbinary(max), image and xml data type columns)..."
I would like to know if somebody knows why this is. My first guess is because those types of fields are way too big and it would take too much time and resources in general to get it done. Am I right? or is it anything to do with SQL Server 2K5 restrictions?
I am worried because on my development enviroment I am rebuiling some indexes and it's been more than 10 hours now, and nothing yet :unsure: Any suggestions or whatever is welcomed. Thanks in advance!!!!!

While reorganizing a clustered index where LOB is present in the leaf pages, LOB-COMPACTION is performed. That operation does NOT happens if you try to rebuild the clustered index in "online" mode. That's why.

The time it requires to rebuild an index in online mode is far greater than in offline mode because the access to the underlying data must be maintained at all times to ensure that your users can read-write while the "new" physical structures are being rebuild. That is totally independent from having LOB data or not.

You should check IO subsystem to see if you are hitting a bottleneck while online rebuild is happening OR if you are being blocked by any other operations.

good luck

* Noel

chileu17 Hall of Fame Points: 3093 More actions · Answer 3

I didn't have any other processes running on my server when I started my indexes rebuilt.

And hhhmmm :ermm: excuse my question :ermm: but how do I check if I am having an IO bottleneck????

noeld SSC Guru Points: 96590 More actions · Answer 4

chileu17 (9/25/2008)
I didn't have any other processes running on my server when I started my indexes rebuilt.
And hhhmmm :ermm: excuse my question :ermm: but how do I check if I am having an IO bottleneck????

Check Disk queue lenght Performance counter as well as AVG Sec/read (read latency) AVG Sec /write (write latency)

High values in those counters indicates that you have an IO Bottleneck.

* Noel

chileu17 Hall of Fame Points: 3093 More actions · Answer 5

the problem is that I can't get a connection to my sql server. My SSMS is not able to get a connection to my server because as my SSMS says my server is not responding. When I check my taskmanager, it says that everything is fine, and that my services are up and running :ermm: should I restart my development server? would I loose any data? Would there be any other way to do this?

Thanks for your help.