Free Book!
Another free indexing books, more awesome!
In last week’s post, I talked about how I’ve got a number of copies of Expert Performance Indexing for SQL Server; that I co-wrote with Grant Fritchey (Blog | @gfritchey). Since these books don’t help anyone sitting on my desk, I’m giving them away over the next few weeks.
The Question
For week two, the topic is moving up a chapter to index storage fundamentals. The topic for discussion in the comments this time is…
What aspect of understanding the physical structure of indexes has proven useful in solving performance issues? Do you have a story of fragmentation? Forwarded records? Large object allocations? Ever used DBCC or page allocations to directly solve a performance issue?
If you have a story to share in this area, leave it in a comment below. At the end of a week, I’ll select one of the comments and send that author a copy of the book.
June, 17 Update
The winner for the book this week is Todd Kleinhans. He talked about using DBCC PAGE to recover missing data from a database. Something that takes a lot of patience and time, but works when you need the data.
The other comments was by SQLDBAPro, who talked about how he’s used the structure of the non-clustered index to better understand how to build indexes.
Strate SQL 
Digging SQL server page and index internals I came to know some interesting facts about the way NC indexes store the CL index key details. In a unique non-clustered index, the clustered index key is added to the leaf level of the non-clustered B-Tree structure. In a non-unique non-clustered index, the clustered index key is added to the leaf and non-leaf levels of non-clustered B-Tree structure. These findings helped me correct my lot of mistakes that I was doing while creating NC indexes. Earlier, I was adding CL index key in INCLUDE/key columns of NC indexes. But knowing these facts I immediately came to know about my mistake that CL index column entry in NC index is redundant as it will be present by default in case of clustered tables. I know this is not related to the questions you asked but I am just sharing my experience.
LikeLike
Thanks for the comment, SQLProdDBA. The ways in which non-clustered indexes store clustered index key columns is something that I often see people have issues with. It can have a big impact on the index design and on understanding how and why indexes are used at times.
LikeLike
Had an underpowered server with three instances on it. We encountered db corruption on the largest table w/ a clustered index for the primary key (PK) on one of the instances. Running a select against the corrupted table for a given PK range would cause an instant stack dump. Fiddling with DBCC PAGE and what the error log was capturing helped me to narrow down what I could extract into a new table. Corruption was not contiguous so had to use page ids logged in error log to find PK values from the pages and then run queries until stack dump occurred. Rinse and repeat. It was determined by MS that we had probably encountered memory corruption as storage was clean. They validated my approach so that felt good. Lost maybe 20,000 rows. A couple of years later I took/watched a PluralSight course by Paul Randal on corruption and saw a cool trick on how you could extract data from the non-clustered indexes to then re-populate a table. Had I known about that trick I could have used it and maybe had zero data loss as that was a heavily indexed table.
LikeLike
Thanks for the comment, Todd. Yeah, the ability to access rows corrupt on the clustered index via the non-clustered indexes is a pretty cool trick. Being able to recover the data you did is still pretty awesome, I’ve been lucky enough to be able to recover corruption in the past with rebuilding non-clustered indexes.
LikeLike