First, run this script to figure out the worst offenders:
create table #t ( name nvarchar(128), rows varchar(50), reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50) ) declare @id nvarchar(128) declare c cursor for select name from sysobjects where xtype='U' open c fetch c into @id while @@fetch_status = 0 begin insert into #t exec sp_spaceused @id fetch c into @id end close c deallocate c select * from #t order by convert(int, substring(data, 1, len(data)-3)) desc drop table #t
Just look at those beasts. The first two tables account for 4GB of Index space to 1.3GB of data space and the second one a whooping 1.3 GB of index space to 450MB of data.
Now, let’s see what we can do to reduce their size.
Reducing Index Fragmentation
As you know, SQL Server stores on-disk table data on the 8KB data pages. Each data page contains data for one or multiple rows. With the exception of index creation or rebuild, SQL Server tries to populate pages in full during normal data modification operations. When data does not fit, for example, when the data pages does not have enough space to accommodate the new row, SQL Server performs the page split operation. In the nutshell, SQL Server allocates another data page and moves about half of the data from original to the new page, which frees up some space to accommodate the new row on the original data page.
Page split operations lead to the index fragmentation, which exists in two kinds. External fragmentation means that the logical order of the pages does not match their physical order, and/or logically subsequent pages are not located in the same or adjacent extents (extent is the group of 8 pages). Such fragmentation forces SQL Server to jump around reading the data from the disk, which makes read-ahead less efficient and increases the number of physical reads required. Moreover, it increases random disk I/O, which is far less efficient when compared to sequential I/O in the case of magnetic hard drives.
Internal fragmentation, on the other hand, means that data pages in the index have free space. As a result, the index uses more data pages to store data. It also increases the number of reads during query execution and amount of memory in buffer pool to cache index pages.
A small degree of internal fragmentation is not necessarily bad. It reduces page splits during insert and update operations when data is inserted into or updated from different parts of the index. Nonetheless, a large degree of internal fragmentation wastes index space and reduces the performance of the system. Moreover, for indexes with ever-increasing keys, for example on identity columns, internal fragmentation is not desirable because the data is always inserted at the end of the index.
You can monitor both, internal and external fragmentation with sys.dm_db_index_physical_statsDMV. Internal fragmentation can be monitored with avg_page_space_used_in_percent column. Lower value in the column indicates higher degree of internal fragmentation.
Let’s take a look at the example and analyze internal fragmentation of one of the indexes with the script below. For simplicity sake, I am using relatively small table; however, you would obviously like to focus on the largest indexes during the tuning process.
select index_id, partition_number, alloc_unit_type_desc ,index_level, page_count, avg_page_space_used_in_percent from sys.dm_db_index_physical_stats ( db_id() /*Database */ ,object_id(N'dbo.tblERPInvoiceData') /* Table (Object_ID) */ ,1 /* Index ID */ ,null /* Partition ID – NULL – all partitions */ ,'detailed' /* Mode */ )
Figure 3 illustrates partial output of the script. The table is partitioned and, as result, you will see separate rows in the result – one per partition per allocation unit.
You can remove internal fragmentation by rebuilding the index. To rebuild all indexes in the table, run the following command:
ALTER INDEX ALL ON dbo.tblStatementLines
After close to an hour of waiting for the rebuild to complete, I noticed a dramatic drop in index size, showing a 20-40% saving in space.