IKR! Feels like I haven’t been posting since, forever, not really…more like a couple months.
Tag Archives: Index
Did you know…
Your hero for the day!
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).
Check Index Fragmentation…Further
Pipboy level utility!
I found a really cool example for getting all the fragmentation on a database. The example was so cool that I decided to adapt my previous script to this one and made a couple adjustments.
Getting Fragmentation % of a Table
Pipboy level utility!
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.
Analyzing unused indexes
Pipboy level utility!
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: