Tag Archives: Index

Did you know…

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

I have modified the index fragmentation script to now disregard anything with less than 8 pages.  This will hopefully prevent anyone else from being stumped for a bit.

sc.name AS [Schema Name],
DB_NAME(ps.database_id) AS [Database Name],
OBJECT_NAME(ps.object_id) AS [Table Name],
ix.name AS [Index Name],
ps.avg_fragmentation_in_percent AS [Frag %],
ps.page_count AS [Page Count],
ix.is_disabled AS [Disabled],
ix.is_hypothetical AS [Hypothetical],
WHEN ps.avg_fragmentation_in_percent > 30 AND ix.type <> 0 AND ix.name IS NOT NULL
THEN 'ALTER INDEX ' + ix.name + ' ON ' + DB_NAME(ps.database_id) + '.' + sc.name + '.' + OBJECT_NAME(ps.object_id) + ' REBUILD'
WHEN ps.avg_fragmentation_in_percent < 30 AND ix.type <> 0 AND ix.name IS NOT NULL
THEN 'ALTER INDEX ' + ix.name + ' ON ' + DB_NAME(ps.database_id) + '.' + sc.name + '.' + OBJECT_NAME(ps.object_id) + ' REORGANIZE'
ELSE 'ALTER TABLE ' + DB_NAME(ps.database_id)+ '.' + sc.name + '.' + OBJECT_NAME(ps.object_id) + ' REBUILD'
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.tables tb ON tb.object_id = ps.object_id
INNER JOIN sys.schemas sc ON tb.schema_id = sc.schema_id
INNER JOIN sys.indexes ix ON ps.object_id = ix.object_id
AND ps.index_id = ix.index_id
WHERE ps.database_id = DB_ID() AND avg_fragmentation_in_percent > 5 AND ps.page_count > 8
ORDER BY page_count DESC

Check Index Fragmentation…Further

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.

This new revision of the script will output the TSQL that you can use to either Rebuild, Reorganize the index or in the case of heaps, rebuild the table.  The scripts that are generated are based on > 30% fragmentation, < 30% fragmentation, and Index Type = 0 (Heap).  The entire script only reports indexes/heaps that are > 5% fragmentation

UPDATE: 02122014 – I just discovered a bug (or error) in this script. The script is not accounting for heaps. I found this out while I was performing some load testing experiments (yay for testing). I have changed the output of the rebuild statement while I debug

UPDATE: 09032014 – OK, so I think I got this bad boy working. I ended up adding a check for when the table is a heap by the sys.indexes.type_desc = ‘HEAP’

sc.name AS [Schema Name] ,
DB_NAME(ps.database_idAS [Database Name] ,
ISNULL(ix.name'HEAP'AS [Index Name] ,
ps.avg_fragmentation_in_percent AS [Frag %] ,
ps.page_count AS [Page Count] ,
ix.is_disabled AS [Disabled] ,
ix.is_hypothetical AS [Hypothetical] ,
ix.fill_factor ,
CASE WHEN ps.avg_fragmentation_in_percent 30
AND ix.TYPE <> 0
THEN 'ALTER INDEX ' ix.name ' ON ' DB_NAME(ps.database_id)
'.' sc.name '.' OBJECT_NAME(ps.OBJECT_ID)
WHEN ps.avg_fragmentation_in_percent 30
AND ix.TYPE <> 0
THEN 'ALTER INDEX ' ix.name ' ON ' DB_NAME(ps.database_id)
'.' sc.name '.' OBJECT_NAME(ps.OBJECT_ID)
WHEN ix.type_desc 'HEAP'
THEN 'ALTER TABLE ' DB_NAME(ps.database_id) + '.' sc.name
ELSE 'If you have reached this message, please email [email protected] with as much information as possible'
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.schemas sc ON tb.schema_id sc.schema_id
INNER JOIN sys.indexes ix ON ps.OBJECT_ID ix.OBJECT_ID
AND ps.index_id ix.index_id
WHERE   ps.database_id DB_ID()
avg_fragmentation_in_percent 5
ORDER BY page_count DESC

Getting Fragmentation % of a Table

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.

How do I know my current level of fragmentation?  You will need to use the dmv function sys.dm_db_index_physical_stats

Below is a simple query that will call the function and return the fragmentation % for a specific table when the Fragmentation is above 5%.  The reason I’m only looking for items with fragmentation above 5% is because Microsoft states that the benefit from removing fragmentation below 5% is almost always vastly outweighed by the cost of reorganizing or rebuilding.  I have also added a couple commented out lines that can give you more information when joined on the sys.indexes table, but that is outside the scope of this post.

DB_NAME(ps.database_id) AS [Database Name],
OBJECT_NAME(ps.object_id) AS [Table Name],
ix.name AS [Index Name],
ps.avg_fragmentation_in_percent AS [Frag %]
--page_count AS [Page Count],
--ix.is_disabled AS [Disabled],
--ix.is_hypothetical AS [Hypothetical],
FROM sys.dm_db_index_physical_stats(DB_ID('DATABASENAME'),OBJECT_ID('SchemaName.TableName'), NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes ix ON ps.object_id = ix.object_id
WHERE avg_fragmentation_in_percent > 5
ORDER BY avg_fragmentation_in_percent DESC

Depending on the size of your table this could take awhile, as always test this out on a staging server.

Now what do you do with anything you find?  That depends on your environment, remember that when designing solutions, most of us work for a single company, in which case our solutions can be as closed box as possible.  The only performance that matters is the performance that signs your paycheck.  With that said lets go over some options.

First you could have your routine index maintenance using Michelle Ufford’s Index Maintenance Script.  I use this weekly, its great, and it’s like clockwork, but what about in-between?  “In-between…” you slowly mutter under your breath.  HA.  Yes, in-between.

Ask yourself this question, Do my indexes get fragmented a day after the maintenance, or 2 days, or 3 days, or…or…or…or you don’t know do you?

One thing you can do is automate the above script and save the data to one of your admin tables.  Then analyze it.  If some of your indexes are reaching > 5% a inbetween, could you defrag them individually?  You can use DBCC INDEXDEFRAG (DatabaseName, “schema.tablename”, indexName) in order to defrag a single index.  Perhaps a solution here is to write a job that looks at this nightly snapshot and defrags as needed?

According to microsoft DBCC INDEXDEFRAG is an online operation, therefore DBCC INDEXDEFRAG does not block running queries or updates.  So this might not be a bad operations to run nightly and keep fragmentation low on your high volume tables.

Analyzing unused indexes

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:

1.  Do not use any scripts that do not take into consideration or show reads vs. writes

2.  How long has your server been up?  A restart of the instance will clear out your stats, so perhaps that index isn’t really 0 reads and n writes.

3.  Disable the index first, drop the index later.

4.  Have you looked into the plan cache to see if there are any plans cached that use that index?  If so, have you read through the SQL to see what might happen?

First off you need to get Brent Ozar’s sp_BlitzIndex.  You will find this tool to be extremely valuable.  I run it at the start of each week and save the results into a spreadsheet for analysis.   I would become familiar with the @mode=0 and @mode=2.  

Mode 2 is for collecting all the index info.  This mode offers all the details you could want from all the indexes across your database.  The results can be overwhelming, I would suggest you get into the habit of dumping these queries into a spreadsheet.  Another great suggestion is to dump the results into a DBA table (you know, the one you collect your own metrics and stats in), and append a timestamp column (or something so that you can identify when the stats where collected), and start playing with SSRS.

Mode 0 is your standard default run of sp_BlitzIndex.  If you are in the same boat that some of us have been in, the one where you inherited databases that were clearly using the index shotgun, this mode will return a wealth of information.  I normally copy all of this into a spreadsheet weekly, then I start dissecting.  I start with multiple index personalities as part of my analysis.  This is a great place to quickly identify indexes that can be combined together.  Looking at the index definition column and the secret_columns, you can quickly see if two similar indexes can be combined resulting in a single better index, less writes on your system, and less space usage.

Another tool I use is a modified version of Pinal Dave’s unused index script.  I’ve changed a few things in the script to taste.  First I changed the columns from KB To MB.  I’m not sure about you, but I normally work in environments where things are sized in GB so it’s just quicker for my brain to process thousands of MB vs KB.  Next, I’ve added a column that contains the TSQL to disable the index (I left the drop TSQL column, just in case you need it).  Finally, I added a couple comments at the bottom and you can play with those options to view the results differently.  I normally run the script a few various ways, record the results and compare them later.  You can view/download the script here

When you are undergoing index tuning using the standard, DisableDrop/Analyze/Create/Analyze cycle, I would highly recommend that you disable indexes first, keep them disabled for some amount of time before dropping.  I always keep logs of what indexes I disabled on what day (IX_Disabled_DBName_DATE.sql) and I always keep logs of what indexes were dropped, and their create statements.  Just some simple DBA CYA.

These are just a couple of tools and tips that you can use to help you on the path of SQL TAO.  I hope you found this article informative and helpful.