Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Inte
l X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our min
ds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WI
TH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then P
roperties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fr
esh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarch
ar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
AlinCan you post the actual DDL (including indexes) and the ALTER INDEX statemen
t you used?
--
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message ne
ws:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Inte
l X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our min
ds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WI
TH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then P
roperties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fr
esh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarch
ar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin|||Hi Andrew
Thank you for your time.
We tried the following:
1. Manually creating the index and checking the Allow online processing of D
ML statements...
2. The following DDL statements:
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo]
.[table1]') AND name = N'index1')DROP INDEX [index1] ON [dbo].
[table1]
CREATE NONCLUSTERED INDEX [index1] ON [dbo].[table1]
([field1] ASC,[field2] ASC,[field3] ASC,[field4] ASC,[fi
eld5] ASC,[field6] ASC)
WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP
_KEY = OFF, ONLINE = ON) ON [PRIMARY]
3. We tried the above statement with only ONLINE = ON option, to avoid possi
ble conflicts with backward compatibility keywords (as stated in BOL).
4. We also tried ALTER INDEX ALL ON dbo.table1 REBUILD WITH (ONLINE = ON);
Database compatibility level is 90.
All of the above with the same result.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%
23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statemen
t you used?
--
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message ne
ws:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Inte
l X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our min
ds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WI
TH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then P
roperties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fr
esh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarch
ar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin|||We also have a clustered index on that table, we also tried to recreate that
index with ONLINE = ON, BOL stating that if a clustered index is created wi
th ONLINE = ON, all non-clustered indexes will be rebuilt with ONLINE = ON.
Same outcome.
Thx again.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%
23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statemen
t you used?
--
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message ne
ws:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Inte
l X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our min
ds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WI
TH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then P
roperties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fr
esh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarch
ar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin|||I think there is a general bug somewhere... this is not the only option that
gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a while
ago, but at the time did not catch the online option as well. You've
spurred me to finally enter a bug report after retrieving my notes from back
then:
http://connect.microsoft.com/SQLSer...=29128
8
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
(Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our
minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD
WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then
Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a
fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max),
nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin|||Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
not sure if index is created with online = on, as it doesn't came up this
way when querying sys.indexes (with all the joins towards sys.index_columns
and sys.columns) or at least I didn't saw that option listed anywhere.
So my guess is that somehow that that option (along with many more, as you
also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
the index is created as stated in my statement. So I think that there is no
workaround available. One can only assume that the index is created as one
expected, but there is no way to get a confirmation.
I will also test on Katmai tomorrow and post my findings on connect.
Thanks for the heads up related to Katmai.
Regards,
Alin
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23z9%23CgD2HHA.3760@.TK2MSFTNGP03.phx.gbl...
>I think there is a general bug somewhere... this is not the only option
>that gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a
>while ago, but at the time did not catch the online option as well. You've
>spurred me to finally enter a bug report after retrieving my notes from
>back then:
> http://connect.microsoft.com/SQLSer...=291
288
> --
> Aaron Bertrand
> SQL Server MVP
>
>
>
> "Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
> news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi list
> Did a search on all SQL related newsgroups, but didn't find anything.
> So, here's my problem:
> We are running SQL 2005 Enterprise edition.
> SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
> (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
> 1)".
> We want to rebuild our indexes on 2 tables with the ONLINE option set to
> ON. They are currently set to OFF. We tried everything that came across
> our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename>
> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
> Apparently, the statement executes with no errors, but index is not
> created if ONLINE set to ON. When scritping the indexes or right-clicking
> and then Properties, they all appear with ONLINE = OFF.
> We also tried to isolate the problem, by creating a fresh new database, a
> fresh new table and then play around with indexes. Same outcome.
> Also tested this on a developer edition, with same outcome.
> Table doesn't have any LOB columns (text, ntext, image, varchar(max),
> nvarchar(max)).
> Any ideas, welcome.
> Thanks in advance for any thoughts.
> Regards,
> Alin
>|||I guess having those options during the CREATE INDEX statement only affect
the initial creation of the index, and do not represent any long-term
setting that "sticks."
My guess is that it will come back as "by design." E.g. you can't specify
up front that every time you change the index, those settings will be true.
If you want to rebuild the index and rebuild it online, you will need to
specify that in the ALTER INDEX command.
Then again, if I want to script the exact CREATE INDEX statement that I ran
yesterday, I can't see what harm it would have to maintain the properties
with the index as it was created, even if you will still need to specify
those options explicitly when using ALTER. Right now it doesn't seem
possible using the tools to re-generate the exact same CREATE INDEX script
that I ran yesterday...
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
> Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
> not sure if index is created with online = on, as it doesn't came up this
> way when querying sys.indexes (with all the joins towards
> sys.index_columns and sys.columns) or at least I didn't saw that option
> listed anywhere.
> So my guess is that somehow that that option (along with many more, as you
> also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
> the index is created as stated in my statement. So I think that there is
> no workaround available. One can only assume that the index is created as
> one expected, but there is no way to get a confirmation.
> I will also test on Katmai tomorrow and post my findings on connect.
> Thanks for the heads up related to Katmai.
> Regards,
> Alin|||I agree, but I still wondering why this option is not reflected in the index
properties, when right-click the index, then on properties.
My feeling is that this is a bug in the UI. Also, you cannot retrieve this
piece of information from any of the DMV's that shows any index options. Or
maybe it's just me
, I don't know. I will further test it tomorrow, but asof today, these are my conclusions (including your opinions on this).
I will come back with more news tomorrow.
Again, thanks for your efforts.
Regards,
Alin Selicean
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eELWJeE2HHA.5772@.TK2MSFTNGP02.phx.gbl...
>I guess having those options during the CREATE INDEX statement only affect
>the initial creation of the index, and do not represent any long-term
>setting that "sticks."
> My guess is that it will come back as "by design." E.g. you can't specify
> up front that every time you change the index, those settings will be
> true. If you want to rebuild the index and rebuild it online, you will
> need to specify that in the ALTER INDEX command.
> Then again, if I want to script the exact CREATE INDEX statement that I
> ran yesterday, I can't see what harm it would have to maintain the
> properties with the index as it was created, even if you will still need
> to specify those options explicitly when using ALTER. Right now it
> doesn't seem possible using the tools to re-generate the exact same CREATE
> INDEX script that I ran yesterday...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
> news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
>|||>I agree, but I still wondering why this option is not reflected in the
>index properties, when right-click the index, then on properties.
Because I don't think it's a "permanent" property, it is merely describing
how it should initially be created. Once the index is created, they're
going to argue that whether it was created online or not last week is not a
permanent property of the index. It's kind of like driving a car off the
lot with premium fuel, and then later expecting the car to know it should
only accept premium fuel.
I agree somewhat with your sentiments that this information should be
preserved somehow, but it is more for the purposes of re-generation of
identical scripts, rather than any property I expect the index to maintain.
A|||> permanent property of the index. It's kind of like driving a car off the
> lot with premium fuel, and then later expecting the car to know it should
> only accept premium fuel.
A better analogy would be expecting the car to somehow know that it at one
time used premium fuel.
A
No comments:
Post a Comment