July 12, 2009 at 3:21 am
Comments posted to this topic are about the item Optimizing Indexes
July 13, 2009 at 2:02 am
I must be missing something:
Documentation states that
REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.
http://msdn.microsoft.com/en-us/library/ms188388(SQL.90).aspx
Also testing it directly in SQL2K5 resolves to the same thing:
USE tempdb;
CREATE TABLE dbo.Test (
PKCol int IDENTITY (1,1) NOT NULL
,DataCol varchar(50) NOT NULL
,CONSTRAINT PK_Test PRIMARY KEY CLUSTERED(PKCol)
);
INSERT dbo.Test VALUES ('Test');
CREATE UNIQUE NONCLUSTERED INDEX UQ_DataCol ON dbo.Test (DataCol) WITH (ALLOW_PAGE_LOCKS = OFF)
ALTER INDEX UQ_DataCol ON dbo.Test REORGANIZE
DROP TABLE dbo.Test;
(1 row(s) affected)
Msg 2552, Level 16, State 1, Line 11
The index "UQ_DataCol" (partition 1) on table "Test" cannot be reorganized because page level locking is disabled.
Any hints as to what I have missed in the QOTD?
Best Regards,
Chris Büttner
July 13, 2009 at 2:37 am
Christian Buettner (7/13/2009)
I must be missing something:Documentation states that
REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.
http://msdn.microsoft.com/en-us/library/ms188388(SQL.90).aspx
Also testing it directly in SQL2K5 resolves to the same thing:
USE tempdb;CREATE TABLE dbo.Test (
PKCol int IDENTITY (1,1) NOT NULL
,DataCol varchar(50) NOT NULL
,CONSTRAINT PK_Test PRIMARY KEY CLUSTERED(PKCol)
);
INSERT dbo.Test VALUES ('Test');
CREATE UNIQUE NONCLUSTERED INDEX UQ_DataCol ON dbo.Test (DataCol) WITH (ALLOW_PAGE_LOCKS = OFF)
ALTER INDEX UQ_DataCol ON dbo.Test REORGANIZE
DROP TABLE dbo.Test;
(1 row(s) affected)
Msg 2552, Level 16, State 1, Line 11
The index "UQ_DataCol" (partition 1) on table "Test" cannot be reorganized because page level locking is disabled.
Any hints as to what I have missed in the QOTD?
The question says that "You have determined that for this index the ALLOW_PAGE_LOCKS is NOT set to OFF so the reorganize option is the correct method.
July 13, 2009 at 2:59 am
So I missed the "not" - verrrry tricky! 😀
If it was intended to be tricky, then I admit it was well done.
If not, I assume rephrasing to "ALLOW_PAGE_LOCKS is set to ON" would have made more sense.
Thanks for the clarification and of course very nice QOTD - I wasn't aware of that reorg limitation.
Best Regards,
Chris Büttner
July 13, 2009 at 3:02 am
Christian Buettner (7/13/2009)
So I missed the "not" - verrrry tricky! 😀If it was intended to be tricky, then I admit it was well done.
If not, I assume rephrasing to "ALLOW_PAGE_LOCKS is set to ON" would have made more sense.
Thanks for the clarification and of course very nice QOTD - I wasn't aware of that reorg limitation.
I misread the question and got it wrong too :rolleyes:
July 13, 2009 at 5:56 am
Agree with previous comments regarding inability to reorganize with the page locks option off. You don't have any option but to rebuild or drop/recreate the index with Allow_page_locks on. Why put that in the question if it isn't taken into account?
July 13, 2009 at 7:49 am
Hmm
July 13, 2009 at 8:25 am
Christian Buettner
If it was intended to be tricky, then I admit it was well done
The question was not intended to be tricky, but to draw attention to an easily overlooked requirement. As a matter of fact a bold clue to the correct answer was
Using Microsoft's BOL recommended guidelines to remedy this fragmentation
chad.losee
The correct answer is not in one's knowledge of indexes but in understanding poor grammar.
My goal with the question was to expand the user's knowledge of the nuances of maintaining efficient indexing, nothing more nothing less. I thank you all for your comments/code snippets as they will assist me in composing additional QODs.
Edited 10:27 to correct spelling error
July 13, 2009 at 12:16 pm
Christian Buettner (7/13/2009)
So I missed the "not" - verrrry tricky! 😀If it was intended to be tricky, then I admit it was well done.
If not, I assume rephrasing to "ALLOW_PAGE_LOCKS is set to ON" would have made more sense.
Thanks for the clarification and of course very nice QOTD - I wasn't aware of that reorg limitation.
I had it wrong as well because of "NOT set to OFF"
July 13, 2009 at 12:47 pm
Chris Houghton (7/13/2009)
Christian Buettner (7/13/2009)
I must be missing something:Documentation states that
REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.
http://msdn.microsoft.com/en-us/library/ms188388(SQL.90).aspx
Also testing it directly in SQL2K5 resolves to the same thing:
USE tempdb;CREATE TABLE dbo.Test (
PKCol int IDENTITY (1,1) NOT NULL
,DataCol varchar(50) NOT NULL
,CONSTRAINT PK_Test PRIMARY KEY CLUSTERED(PKCol)
);
INSERT dbo.Test VALUES ('Test');
CREATE UNIQUE NONCLUSTERED INDEX UQ_DataCol ON dbo.Test (DataCol) WITH (ALLOW_PAGE_LOCKS = OFF)
ALTER INDEX UQ_DataCol ON dbo.Test REORGANIZE
DROP TABLE dbo.Test;
(1 row(s) affected)
Msg 2552, Level 16, State 1, Line 11
The index "UQ_DataCol" (partition 1) on table "Test" cannot be reorganized because page level locking is disabled.
Any hints as to what I have missed in the QOTD?
The question says that "You have determined that for this index the ALLOW_PAGE_LOCKS is NOT set to OFF so the reorganize option is the correct method.
Hmm. The book "Professional SQL Server 2005 Performance Tuning" by Steven Wort, et al, says on page 54, third paragraph under Index Fragmentation:
"The best way to remedy index fragmentation is with the ALTER INDEX command. This should be appended with the fully qualified index and schema names and then either REBUILD or REORGANIZE. The reorganize operation is more lightweight, but it can't resolve inter-leaved data and index pages (external fragmentation). An index rebuild is an I/O intensive operation that will resolve both internal and external fragmentation and can be carried out online with Enterprise or Developer editions of SQL Server 2005." ...
Since rebuild should take care of more kinds of fragmentation, I selected rebuild instead of reorganize.
July 13, 2009 at 1:01 pm
An extract from the question as published
jpowers
Using Microsoft's BOL recommended guidelines to remedy this fragmentation
- emphasis added for this post.
Please note that the question specifically cited as a reference BOL. Also the question specifies a limited percentage of fragmentation.
What does
"Professional SQL Server 2005 Performance Tuning" by Steven Wort, et al,
say in regards to various degrees of fragmentation?
July 13, 2009 at 3:52 pm
What does BOL say about fragmentation level? My experience is that you should reorganize when you have a fragmentation between >10% and <=30% (rebuild when it is above 30%) as long as you have set allow page lock to ON
(on vacation far away from SQL server, but not from SQLServerCentral)
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
July 13, 2009 at 4:54 pm
Hey Bitbucket, thanks for teaching me something new.
July 13, 2009 at 5:17 pm
hakan.winther
In answer to your question:
BOL link
http://msdn.microsoft.com/en-us/library/ms189858(SQL.90).aspx
Which recommends:
avg_fragmentation_in_percent value Corrective statement
> 5% and 30% ALTER INDEX REBUILD WITH (ONLINE = ON)*
September 17, 2009 at 7:39 am
ohh, I missed that little word "not" as well !!!!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply