IKR! Feels like I haven’t been posting since, forever, not really…more like a couple months.
Did you know that you can’t defrag an index with < 8 pages?
After reading this…now you do. This was some obscure information that I never really thought about until I was analyzing some indexes on our system. I have reports sent to me nightly that check for fragmentation and I noticed that a handful were consistently staying fragmented.
This is simply because SQL server will not defrag anything with less than 8 pages. Without doing any research on the matter my assumption is that this has to do with extents being 8 pages (feel free to add to this concept while I try to find some google time).
The next set of posts are more for my own sanity and documentation. Sometimes I slip up on my terminology, but most of the time my definitions are correct. This tells me that I need to start posting more as a learning reinforcement.
I’m going to start with index rebuild vs. reorganization. The concepts are fairly simple to understand. Over time operations such as inserts, updates and deletes cause index information to become scattered in the database or fragmented. This happens when the logical ordering of pages are different than the physical ordering on the inside the data file. When fragmentation becomes high you can bet that performance will degrade.
Indexing…a DBAs boon and bane all rolled into one. If you can truly be one with the IX then you will be on the path of SQL TAO. If the IX rejects you and you cannot find a way to tame it…then you will surely be on the path of unemployment. These are the hard facts about indexing.
On several occasions I have found myself needing to cleanup a messy index situation, more often than not, unused indexes are sitting around gathering dust (not being read) and getting in the way (accumulating writes). Through my travels as a student of SQL TAO here are some key points to remember: