Tag Archives: Index

Did you know…

15_The_Superhuman_Gambit

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).

Continue reading

Getting Fragmentation % of a Table

01_Vault_101_Citizenship_Award

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.

Continue reading

Analyzing unused indexes

01_Vault_101_Citizenship_Award

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:

Continue reading