Here we go again, guys. Lets take care of our indexes this time.
Fragmentation occurs in case of large number of DML Statements on a table which has indexes.
When any record is deleted from the table which has any index, it creates a memory gap(empty space)
which causes internal fragmentation.Fragmentation can also be caused due to page split, which is
the way of building B-Tree dynamically acoording to the new records coming into the table.
Taking care of fragmentation levels and maintaining them is one of the major over-head for indexes
-Since Indexes slow down DML operations, we do not have lot of indexes on OLTP but it’s recommended to
have many different indexes on OLAP(Data warehouse)
Types of fragmentation
1.Internal fragmentation
It is the fragmentation in which leaf node of a Balance Tree of the index is not filled to its fullest capacity and contains
empty space. If there are many Non Clustered Index on Clustered Index then the internal fragmentation cascades to all Non Clustered Indexes, as corresponding key values must also be deleted from the leaf nodes on the Non Clustered Index.Internal fragmentation is mainly at leaf nodes and in some cases cascades to intermediate and root level nodes.
2.External Fragmentation
It is the fragmentation in which the logical ordering of the pages does not match the physical ordering of the pages in the hard disk. Most of the pages for the Balance Tree structure are scattered in hard disk. External fragmentation is a serious problem as it leads to many I/O operations to fetch data taking long which drops down the performance of SELECT sharply.
Important Index properties.
Fill Factor is the percentage of space on each leaf-level page to be filled with data when creating or rebuilding the index.The remainder of free space is to be reserved for future growth. It ranges between 1 and 100.Default value is 0 which means fill completely. Lets see the code to create index and specify the fill factor,
CREATE INDEX <indexname> ON TABLE (columnname)
WITH (FILLFACTOR = <number>)
If the index already exists, we can rebuilt it and specify the fill factor like this,
ALTER INDEX <indexname> ON <tablename> REBUILD
WITH (FILLFACTOR = <number>)
When the fill factor is properly chosen, it can reduce large number of page splits which leads to reduction in external fragmentation.If it’s too high, it will cause page splits, so choose an optimal value but a very low value can cause internal fragmentation.
So an optimal value should be chosen based on amount of DML operations.
Detecting index fragmentation
SQL server provides a dynamic management function (DMF) called sys.dm_db_index_physical_stats to analyze the fragmentation in indexes across the entire sql server. It needs the following parameters Database ID, Object ID (id of the table or view on which the index is created) ,index id, partition number, mode. For example, this code will retrieve indexes on Adventureworks database and specific to the Employee table
SELECT
FROM sys.dm_db_index_physical_stats
( DB_ID(N’AdventureWorks’)
, OBJECT_ID(N’AdventureWorks.HumanResources.Employee’)
, NULL — index_id; NULL -> ALL indexes
, NULL — partition_number; NULL -> ALL
,’DETAILED’ — mode: NULL (the default) | DEFAULT |
) — ‘LIMITED’ | ‘SAMPLED’ | ‘DETAILED’
go
From these result that showed up, you can see the following
–Avg_fragmentation_in_percent – level of external fragmentation
–avg_page_space_used_in_percent — how much page capacity is full
–capacity not full indicates internal fragmentation
Remove Fragmentation
Rebuilding-Rebuilding an index drops and recreates the index. This removes fragmentation, compacts the pages based on the specified
fill factor setting, and reorders the index rows in contigous pages.
ALTER INDEX <INDEXNAME> ON <TABLENAME> REBUILD
Reorganization- it defragments the leaf level of indexes by physically reordering the leaf-level pages to match the logical order and also compacts the pages.
ALTER INDEX <INDEXNAME> ON <TABLENAME> REORGANIZE
if avg_fragmentation_in_percent > 5 and <= 30 then reorganize
if avg_fragmentation_in_percent > 30 then Rebuild
–View the current fragmentation levels for a particular index, say in adventures and on the table employee.
SELECT index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(‘AdventureWorks’),
OBJECT_ID(‘HumanResources.Employee’),NULL, NULL, ‘DETAILED’)
WHERE index_id <> 0; –does not return information about heaps
Now lets perform the follow task
–Rebuild all indexes on the table.
–Create the indexes with a fill factor of 90.
–Allow the index operation to take place ONLINE, meaning when the index rebuilding is happening people can still retrieve data from the index. If this option is not stated, the data will not be available until the rebuild is done.
ALTER INDEX ALL ON HumanResources.Employee REBUILD
WITH (FILLFACTOR = 90, ONLINE = ON)
PAD INDEX
if set to on results in fill factor being applied to intermediate level pages as well.otherwise fill factor is only applied at the leaf nodes.
Rebuilding indexes–Rebuilding results in dropping and recreating the index so outside processes not able to access the table and performance can suffer.
Statistics
It stores information about how data is distributed in tables and indexes. It is a histogram that depicts the distribution of values in the columns of the tables as well as indexes. This information is used by the query optimizer to determine which indexes to use in compiling the execution plan.Statistics are used to determine the cost of using an index to ruan a query. Let see some codes for statistics,
–Update statistics on all indexes of the table
UPDATE STATISTICS tablename
–Update statistics on a particular index
UPDATE STATISTICS tablename indexname
–Manually create statistics on a column
CREATE STATISTICS statname ON tablename(columnname)
Note: After reorgnize or rebuild, it’s good to update the statistics because the query optimizer might still be using the old statistics to choose which index to use to execute the query. Updating the statistics will purge the changes and give the most up to date information on the performance of the indexes.
I hope this short tutorial helps to take care of those indexes. I’ll be touching Common Table Expressions(CTE), Pivot and Unpivot tables in the next tutorial. Keep in touch. Peace.
