Author: Hari Saran Manandhar
Index in SQL Server
Indexes play a vital role in the performance of SQL Server applications. Indexes are created on columns in tables or views and provide a quick way to look up data based on the values of those columns. It is an on-disk structure that comprises of keys that point to one or more columns in the table or view.
SQL Server stores data on 8KB data pages. Eight contiguous pages form an extent. A data page both in clustered or non-clustered indexes contains pointers to the next and previous pages. The following picture demonstrates that there is no fragmentation.
When a new row is inserted, SQL Server inserts a new row on the data page in case there is enough free space on that page, otherwise the following happens:
-A part of data from old data page transfers to newly allocated data page
-In order to keep the logical sorting order, pointers on both pages are updated
-SQL Server allocates new data page or even new extent
SQL Server Index Fragmentation
Fragmentation- describes numerous effects that can occur because of data modifications. There are two types of fragmentation in SQL server: Internal Fragmentation and External Fragmentation.
Internal Fragmentation
SQL Server Internal Fragmentation is caused by pages that have too much free space.
For example, at the beginning of the day we have a table with 40 pages that are 100% full, but by the end of the day we have a table with 50 pages that are only 80% full because of various transactions (insert, update and delete statements) throughout the day. This lead to an increase in the number of logical reads (50 pages instead of 40 pages) during the query execution, because the index utilizes more data pages to store data.
External Fragmentation (Logical Fragmentation)
External Fragmentation is caused by pages that are out of order. The logical order of the pages does not correspond their physical order.
For example, at the beginning of the day we have a perfectly ordered table. During the transactions there occurred hundreds of update statements possibly leaving some empty space on one page and trying to fit space into other pages. This means our storage must jump around to obtain the data needed instead of reading in one direction. This directly impacts to the query execution time, because random reading from different pages is far less efficient comparing to sequential reading.
Detecting fragmentation:
sys.dm_db_index_physical_stats data management function can be used to analyze fragmentation. The following columns in the resultset are most important:
- avg_page_space_used_in_percent shows the average percentage of the data storage space used on the page. This value allows you to see the internal index fragmentation.
- avg_fragmentation_in_percent provides you with information about external index fragmentation. For tables with clustered indexes, it indicates the percent of out-of-order pages when the next physical page allocated in the index is different from the page referenced by the next-page pointer of the current page. For heap tables, it indicates the percent of out-of-order extents, when extents are not residing continuously in data files.
- fragment_count indicates how many continuous data fragments the index has. Every fragment constitutes the group of extents adjacent to each other. Adjacent data increases the chances that SQL Server will use sequential I/O and Read-Ahead while accessing the data.
- Example Query :
SELECT OBJECT_NAME(ips.OBJECT_ID) ‘TableName’
,i.NAME ‘IndexName’
,index_type_desc ‘IndexType’
,avg_fragmentation_in_percent
,avg_page_space_used_in_percent,
ips.fragment_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘SAMPLED’) ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
AND (ips.index_id = i.index_id)
WHERE ips.avg_fragmentation_in_percent <> 0
ORDER BY avg_fragmentation_in_percent DESC
Maintenance Plan
Indexes are a key element to improve SQL Server database performance. However, simply creating an index isn’t all that needs to be done. Indexes need to maintain regularly to make sure that the performance is getting best. Index rebuilding and reorganizing are the two methods to maintain fragmented indexes and improve database performance.
These methods can be implemented using script as well as creating maintenance plan. Here we will go through SQL Server maintenance plan.
To access Maintenance Plans, in SSMS, open Object Explorer and then expand the Management folder and you will see the Maintenance Plans folder, this will be empty if no maintenance plans have been created.
Creating a SQL Server Maintenance Plan
To create a new maintenance plan, right click on Maintenance Plan and select New Maintenance Plan and the following window will open.
We need to drag and drop tasks from the Toolbox (to open the toolbox use Ctrl + Alt + X) into the gray workspace area on the bottom as shown in the below screenshot.
I have added Rebuild Index and Reorganize Index tasks from Toolbox.
Configuring SQL Server Rebuild Index Task
Double click on task to open the properties window and configure properties.
From SQL Server version 2016 and above there are lots of other properties as shown in figure below:
Reorganize Index Task
Similar to rebuilding indexes, there is an option for Reorganizing indexes as shown below. Most of the settings are the same as the Rebuild Index Task settings.
Once completed the maintenance plan is ready to execute.
Schedule or Manual Execution Maintenance Plan
The saved maintenance plan will automatically create related job in Jobs folder in Object Explorer of SSMS.
R-Click and click “Start Job at Step..” for manual execution.
Or Click on Schedule Button in maintenance plan for schedule execution.
Summary
Maintenance plans are a handy option for users who are not comfortable with T-SQL scripting and need an easy way to maintain indexes. All the required options for Index Rebuild and Index Reorganize are available in maintenance plans including online options, tempdb options, scheduling and more. Maintenance plan for Index rebuild and Index Reorganize for fragmentation is implemented in project RadioWorks for database optimization task.
If you have any questions regarding this feature, just call our friendly team on 01296 328689 or drop us an email at info@dogmagroup.co.uk. Our team will be more than pleased to discuss these with you.
