This past weekend I was in Madison, WI for SQLSaturday #118 and it was a fantastic event. Jes Borland ( b | t ) and her team did a great job of hosting their first SQLSaturday and I was honored to present twice. I presented a new session, DBCC Commands: The Quick and the Dangerous, and then my statistics session, You’re My Density: Making Sense of Statistics. Both presentations went well, were well attended and had some great questions. I received good feedback from some attendees as well, which is always appreciated.
There were three things that I wanted to follow up on from my presentations, and I’ll do that in this post and the next two.
The first item was mentioned in the DBCC session. One of the commands I discuss in the session is DBCC CHECKDB, and I have a demo that shows corruption of a nonclustered index and how you can fix it. If corruption strikes, you’re lucky if it only strikes in a nonclustered index, as it can be fixed without any data loss. However, fixing the corruption is not the end of the issue. You then need to make sure you perform root-cause analysis to understand why you had corruption to begin with (e.g. run checkdisk, look for other errors indicating problems with storage).
In order to fix the corruption you have to create a new copy of the index. When you normally rebuild an index, the engine uses the existing copy of the index to create the new copy. In the case of corruption in the index, it cannot do that. I initially thought that I would have to drop and then create the index, and I didn’t love that option because if I didn’t know the index definition, I had to go look it up. While it’s definitely possible, I was looking for something that required less work. Remember, if you’re using SQL 2005 or higher, using sp_helpindex to look at index doesn’t tell you everything, as sp_helpindex doesn’t provide any information about included columns or filters (you need Kimberly Tripp’s sp_helpindex script for that).
Then I tested disabling the index and rebuilding it, and that worked! Problem solved, as I didn’t have to figure out the index definition. So I did a demo showing this when I was talking about the corruption in the nonclustered index, and Luke Jian ( b | t ) mentioned that you had to be careful if you had a unique constraint in your index, as the time between the drop and create (or disable and rebuild) would leave the table vulnerable for data that violated the constraint to be inserted. It was an excellent point and I’m glad he mentioned it. Luke went on to say that this had come up during one of the SQLskills Immersion Events he had attended (and he’s attended IE1, IE2 and IE3 might I add!) and another attendee had suggested wrapping the drop and create into a transaction to prevent an invalid insert. It was a great learning moment for everyone, including me, and I thought it worth a post.
Remember, in this particular example I’m only working with corruption in the nonclustered index. Do not disable a a clustered index that is corrupt, and do not drop a clustered index that is corrupt, as you will lose the entire table.
-- create a database for testing
CREATE DATABASE [NCI_Corruption]
ON PRIMARY (
NAME = N'NCI_Corruption',
FILENAME = N'C:\Databases\SQL2008R2\NCI_Corruption.mdf' ,
SIZE = 4096KB ,
FILEGROWTH = 1024KB )
LOG ON (
NAME = N'NCI_Corruption_log',
FILENAME = N'C:\Databases\SQL2008R2\NCI_Corruption_log.ldf' ,
SIZE = 1024KB ,
FILEGROWTH = 1024KB );
ALTER DATABASE [NCI_Corruption] SET RECOVERY SIMPLE;
USE NCI_Corruption;
GO
-- create a table
CREATE TABLE MovieInfoHistory (
MovieName VARCHAR(800),
ReleaseDate SMALLDATETIME,
Rating VARCHAR(5)
);
-- create the NCI
CREATE UNIQUE NONCLUSTERED INDEX NCI_MovieName ON dbo.MovieInfoHistory (MovieName);
-- add some data
INSERT INTO dbo.MovieInfoHistory (
MovieName, ReleaseDate, Rating
)
VALUES
('Caddyshack', '1980-07-25', 'R'),
('Bill & Ted''s Excellent Adventure', '1989-02-17 00:00:00', 'PG'),
('Apollo 13', '1995-05-30 00:00:00', 'PG'),
('The Hunt for Red October', '1990-03-02 00:00:00', 'PG'),
('A Few Good Men', '1994-12-11 00:00:00', 'R'),
('The Natural', '1984-05-11 00:00:00', 'PG'),
('The Truman Show', '1998-06-05 00:00:00', 'PG-13'),
('All The President''s Men', '1976-04-09 00:00:00', 'R'),
('The Right Stuff', '1983-10-21 00:00:00', 'PG-13');
-- find a page in the NCI to corrupt
DBCC IND ('NCI_Corruption', 'dbo.MovieInfoHistory', 2);
-- take the database offline
USE master;
GO
ALTER DATABASE NCI_Corruption SET OFFLINE;
-- corrupt the Nonclustered Index (I tend to use a hex editor)
-- bring the database back online
USE master;
GO
ALTER DATABASE NCI_Corruption SET ONLINE;
USE NCI_Corruption;
GO
-- verify the corruption
DBCC CHECKTABLE ('dbo.MovieInfoHistory');
-- disable and rebuild the index within a transaction
BEGIN TRANSACTION;
ALTER INDEX NCI_MovieName ON dbo.MovieInfoHistory DISABLE;
ALTER INDEX NCI_MovieName ON dbo.MovieInfoHistory REBUILD;
COMMIT;
-- verify the corruption is gone
DBCC CHECKTABLE ('dbo.MovieInfoHistory');
-- clean up
USE master;
GO
DROP DATABASE NCI_Corruption;
[…] I mentioned in my last post, one of the sessions I presented last weekend at SQLSaturday #118 was on statistics, and an […]