Showing posts with label Indexes. Show all posts
Showing posts with label Indexes. Show all posts

Tuesday, June 06, 2017

Having fun with maxed out uniqifiers on a non unique clustered index



I was reading the post SQL Server Non-Unique Clustered Indexes Are Evil! by the Lone DB

In that post, the person created the following table


USE boo
GO

CREATE TABLE ClusteredIndexCrash ( i TINYINT NOT NULL ) ;
CREATE CLUSTERED INDEX ClusteredIndexCrashIdx ON dbo.ClusteredIndexCrash( i ) ;


As you can see, the table has a non unique clustered index

If you were to fill this table with the same value 2147483648 times, you will get an error

So if you run this first (this will take a while believe me)


INSERT INTO dbo.ClusteredIndexCrash
SELECT TOP 1000000
    1
FROM
    syscolumns c1 , syscolumns c2 , syscolumns c3 ;

GO 2146

Then you run the following statement


INSERT INTO dbo.ClusteredIndexCrash
SELECT TOP 1000000
    1
FROM
    syscolumns c1 , syscolumns c2 , syscolumns c3 ;


You would get the following error..straight from the beast himself apparently

Msg 666, Level 16, State 2, Line 1
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 72057594039173120. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.


I will be using DBCC PAGE and DBCC IND in this blog post, if you want to learn how to use these yourself, take a look at How to use DBCC PAGE


I decided to look in the pages of SQL Server itself. I grabbed all the pages by running DBCC IND


DBCC IND ('boo','ClusteredIndexCrash',  1);


That returned 4806619  rows, these are all the pages that exist for this table

Here is part of the output that matters, the highlighted row where NextPageFID is 0 is what we want, this is the last page.



Click on the image for a larger view


Then I grabbed the last page id from that result set and looked at the page by executing these commands


DBCC TRACEON (3604);
GO
DBCC PAGE (boo, 1, 4810967, 3) with TABLERESULTS;



Here is part of the output in text, I highlighted the uniqifier for you


PAGE HEADER: Slot 158 Offset 0xa40 Length 16 Record Type PRIMARY_RECORD

PAGE HEADER: Slot 158 Offset 0xa40 Length 16 Record Attributes NULL_BITMAP VARIABLE_COLUMNS

PAGE HEADER: Slot 158 Offset 0xa40 Length 16 Record Size 16

Slot 158 Offset 0xa40 Length 16 Memory Dump @0x00000000E832AA40 0000000000000000: 30000500 01020000 01001000 fdffff7f 0...........ýÿÿ.

Slot 158 Offset 0xa40 Length 16 Slot 158 Column 0 Offset 0xc Length 4 Length (physical) 4 UNIQUIFIER 2147483645

Slot 158 Offset 0xa40 Length 16 Slot 158 Column 1 Offset 0x4 Length 1 Length (physical) 1 i 1

Slot 158 Offset 0xa40 Length 16 Slot 158 Offset 0x0 Length 0 Length (physical) 0 KeyHashValue (5eeffbfe6491)

PAGE HEADER: Slot 159 Offset 0xa50 Length 16 Record Type PRIMARY_RECORD

PAGE HEADER: Slot 159 Offset 0xa50 Length 16 Record Attributes NULL_BITMAP VARIABLE_COLUMNS

PAGE HEADER: Slot 159 Offset 0xa50 Length 16 Record Size 16

Slot 159 Offset 0xa50 Length 16 Memory Dump @0x00000000E832AA50 0000000000000000: 30000500 01020000 01001000 feffff7f 0...........þÿÿ.

Slot 159 Offset 0xa50 Length 16 Slot 159 Column 0 Offset 0xc Length 4 Length (physical) 4 UNIQUIFIER 2147483646

Slot 159 Offset 0xa50 Length 16 Slot 159 Column 1 Offset 0x4 Length 1 Length (physical) 1 i 1

Slot 159 Offset 0xa50 Length 16 Slot 159 Offset 0x0 Length 0 Length (physical) 0 KeyHashValue (bedbd571a02d)




Here is the same output as an image from SSMS




Click on the image for a larger view



I decided to play around with this now that I had the table maxed out

What would happen if I deleted a bunch of rows? Would I still get the same error? I deleted 100 rows

DELETE TOP (100)
FROM  ClusteredIndexCrash


Now I tried to insert 1 row..

INSERT INTO dbo.ClusteredIndexCrash
SELECT 1

Msg 666, Level 16, State 2, Line 1
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 72057594039173120. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

So same issue, doesn't reuse uniqifiers


Would an index rebuild fix this?


ALTER INDEX ClusteredIndexCrashIdx ON ClusteredIndexCrash REBUILD


-- 20 minutes later.......



INSERT INTO dbo.ClusteredIndexCrash
SELECT 1


Msg 666, Level 16, State 2, Line 1
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 72057594039566336. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.



Nope, nothing changed


What about a truncate?


TRUNCATE TABLE ClusteredIndexCrash


Now when I run DBCC IND...


DBCC IND ('boo','ClusteredIndexCrash',  1);

This returns nothing.. that is fine..after all this table is empty


Let's try adding a row


INSERT INTO dbo.ClusteredIndexCrash
SELECT 1


No problem, error is gone


I can now again use DBCC IND like before


DBCC IND ('boo','ClusteredIndexCrash', 1);

But since I know I only have 1 row now, I can also use the sys.fn_PhysLocCracker function together with the %%physloc%% virtual column, it will actually give me the page id the row is located on

Here is the query

SELECT top 10 *,%%physloc%% AS PhysicalLocationOfRow
FROM ClusteredIndexCrash
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS FPLC

This is the output from that query

i file_id page_id slot_id PhysicalLocationOfRow
1 1 7668633 0 0x9903750001000000


Running the DBCC PAGE command again with the pageid from the query above


DBCC TRACEON (3604);
GO
DBCC PAGE (boo, 1, 7668633, 3) with TABLERESULTS;



And here is the output from DBCC PAGE



PAGE HEADER: Slot 0 Offset 0x60 Length 9 Record Type PRIMARY_RECORD

PAGE HEADER: Slot 0 Offset 0x60 Length 9 Record Attributes NULL_BITMAP

PAGE HEADER: Slot 0 Offset 0x60 Length 9 Record Size 9

Slot 0 Offset 0x60 Length 9 Memory Dump @0x00000000C41EA060 0000000000000000: 10000500 01020000 01 .........

Slot 0 Offset 0x60 Length 9 Slot 0 Column 0 Offset 0x0 Length 4 Length (physical) 0 UNIQUIFIER 0

Slot 0 Offset 0x60 Length 9 Slot 0 Column 1 Offset 0x4 Length 1 Length (physical) 1 i 1

Slot 0 Offset 0x60 Length 9 Slot 0 Offset 0x0 Length 0 Length (physical) 0 KeyHashValue (fa469958a30a)


Here it is also as an image




Click on the image for a larger view


As you can see the uniqifier is now 0

So it looks like the uniqifier behaves like an identity property per value, a truncate will reseed it back to 0 but when you delete values uniqifiers are not reused

So maybe we would need a DBCC CHECKUNIQIFIER command  :-)


So how important is this..will you ever run into this?  I can see this becoming an issue if you have a staging table which you don't drop or truncate but you constantly insert and delete data from it

Maybe you should drop the table once in a while?


Have you ever maxed out the uniqifier?

Wednesday, May 10, 2017

Not sure that I like the message from Online Resumable Index Rebuild in SQL Server 2017


I was messing around with the Online Resumable Index Rebuild, this is new in CTP 2 of SQL Server 2017. I don't know that I like the output from an resumable index rebuild

Let's take a look at what I did
First I created this table

CREATE TABLE dbo.TestIndexRebuild(
 name nvarchar(35) NULL,
 number int NOT NULL,
 type nchar(3) NOT NULL,
 low int NULL,
 high int NULL,
 status int NULL,
 somebigchar char(2000)
) ON [PRIMARY]

CREATE CLUSTERED INDEX CI_TestIndexRebuild ON TestIndexRebuild(name,number)

I made the table wide by adding a 2000 character column, I then added a clustered index to the table

I then pumped in a bunch of data
INSERT INTO TestIndexRebuild
SELECT *,REPLICATE('A',2000)  
FROM master..spt_values
GO 500


I then executed the following
ALTER INDEX CI_TestIndexRebuild on TestIndexRebuild 
REBUILD WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=1) 

Here is the output I got after 1 minute

Msg 3643, Level 16, State 1, Line 19
The operation elapsed time exceeded the maximum time specified for this operation. The execution has been stopped.
Msg 596, Level 21, State 1, Line 18
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 18
A severe error occurred on the current command.  The results, if any, should be discarded.

I don't really like that, index rebuild has been paused because elapsed time has exceeded the maximum time displayed in black instead of red would have been fine with me. I don't need to see that the session is in a kill state or that a severe error occurred

I then executed the same command again

ALTER INDEX CI_TestIndexRebuild on TestIndexRebuild 
REBUILD WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=1) 

Here is the output

Warning: An existing resumable operation with the same options was identified for the same index on 'TestIndexRebuild'. The existing operation will be resumed instead.

While that was running in a second window, I executed the following

ALTER INDEX CI_TestIndexRebuild on TestIndexRebuild  ABORT 

All you get as output is

Command(s) completed successfully.

However, in the widow where you executed the resumable index rebuild you get this

Msg 1219, Level 16, State 1, Line 1
Your session has been disconnected because of a high priority DDL operation.
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

If you execute the ABORT again while the index is not being rebuilt, you get

Msg 10638, Level 16, State 2, Line 1
ALTER INDEX 'ABORT' failed. There is no pending resumable index operation for the index 'CI_TestIndexRebuild' on 'TestIndexRebuild'.

That makes sense


With a PAUSE Same thing happens as with ABORT, when you pause the index rebuild


ALTER INDEX CI_TestIndexRebuild on TestIndexRebuild  PAUSE

All you get as output is

Command(s) completed successfully

But you get those other messages in the original window


You can also execute a PAUSE followed by an ABORT, you will only get one set of messages, no error is displayed in the window where you executed the code below



ALTER INDEX CI_TestIndexRebuild on TestIndexRebuild  PAUSE 
GO
ALTER INDEX CI_TestIndexRebuild on TestIndexRebuild  ABORT 





I think the kill state and severe error occurred is a little over the top.
What is your opinion?


Now having said all that, I do like the resumable index rebuilds, it pretty much mimics the defragment/reorganize functionality. It continues from where it was when if was running last

There are some more things you can specify, for example, here is a sample command

ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP=2, MAX_DURATION= 240 MINUTES, WAIT_AT_LOW_PRIORITY (MAX_DURATION=10, ABORT_AFTER_WAIT=BLOCKERS)) ;


Read more about this in section J of ALTER INDEX


Thursday, March 15, 2007

Online Indexing Operations in SQL Server 2005 Whitepaper Available For Download

TechNet has made availabe a whitepaper that deals with Online Indexing Operations in SQL Server 2005. Introduced in SQL Server 2005 Enterprise Edition, the online index feature provides a powerful way to perform maintenance operations such as rebuilding or creating indexes in a production system without sacrificing DML concurrency. This paper provides a detailed discussion of the index process and provides guidelines and best practices for implementing this feature in a production environment.

Included in this document:

• Introduction

• Data Structures and Concepts

• Overview of the Online Index Build Algorithm

• Capacity Planning and Concurrency Considerations for Index Create and Rebuild Operations

• Online Index Best Practices

• Conclusion

• Appendix A: Performance Study

• Appendix B: Diagnostics

• Appendix C: Log Measurement Script

• Appendix D: Online Index Limitations


Download this whitepaper (Word doc,25 pages) here: http://www.microsoft.com/technet/prodtechnol/sql/2005/onlineindex.mspx

Wednesday, February 28, 2007

Using ALTER INDEX…REBUILD To Rebuild A Clustered Index Does Not Rebuild Its Nonclustered Indexes By Default On SQL Server 2005

How many times have you asked/been asked the following question: Does rebuilding a clustered index rebuild nonclustered indexes?

Well Ken Henderson has the answer for you on his blog.

On SQL Server 2005, using ALTER INDEX…REBUILD to rebuild a clustered index
does not rebuild its nonclustered indexes by default


Read the rest here: http://blogs.msdn.com/khen1234/archive/2007/02/27/does-rebuilding-a-clustered-index-rebuild-nonclustered-indexes.aspx

Monday, October 17, 2005

Do Not Drop And Create Indexes On Your Tables

When you do this the nonclustered indexes are dropped and recreated twice, once when you drop the clustered index and then again when you create the clustered index.

Use the DROP_EXISTING clause of the CREATE INDEX statement, this recreates the clustered indexes in one atomic step, avoiding recreating the nonclustered indexes since the clustered index key values used by the row locators remain the same.

Here is an example:

CREATE UNIQUE CLUSTERED INDEX pkmyIndex ON MyTable(MyColumn)
WITH DROP_EXISTING