SQL Server indexes overview
Just like a book index, SQL Server index has a similar purpose, to provide faster searching, gathering and sorting of information from a database, without need to go through all of the objects over and over. Instead, an index provides the gateway between the table rows and query engine, in order to achieve better both database and query performance.
Indexes are not just another type of database object, because they can be also considered as a query performance optimization method, if they are created and used properly.
In this article, the focus will be on how to monitor SQL Server indexes size.
Monitor SQL Server indexes size
As database objects stored on a disk, indexes could impact negatively the storage and overall database performance.
In order to prevent unexpected SQL Server indexes growth and database storage overwhelming, the follow monitoring methods which will be mentioned below.
Each of the methods described will separately provide the solution to monitor:
- individual index size with Disk usage by table pre-defined reports in SQL Server Management Studio
- list of individual index size within a database
- cumulative size of indexes within each table in a database
- cumulative size of indexes in a specified SQL Server database or databases
Review index size in Disk usage using a pre-defined report
To review individual indexes size manually, right-click on a specific database, choose Reports -> Standard reports -> Disk usage by table:
In this case, we ran a standard report on the AdventureWorks2014 database.
Review a full list of index sizes on a whole database
Use this script to retrieve a full list of individual indexes in a database, alongside with the corresponding tables and column on which they are created:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE <database_name> GO SELECT tn.[name] AS [Table name], ix.[name] AS [Index name], SUM(sz.[used_page_count]) * 8 AS [Index size (KB)] FROM sys.dm_db_partition_stats AS sz INNER JOIN sys.indexes AS ix ON sz.[object_id] = ix.[object_id] AND sz.[index_id] = ix.[index_id] INNER JOIN sys.tables tn ON tn.OBJECT_ID = ix.object_id GROUP BY tn.[name], ix.[name] ORDER BY tn.[name] |
Retrieve index size within all tables
The following method shows a detailed result set, which retrieves the list of tables and total size of indexes (expressed in KB) for each of the table in a specified database.
Use the script below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
USE <database_name> GO --This part of the script creates temporary table, --which will hold results of sp_spaceused execution for each table CREATE TABLE #IxSizes(TableName nvarchar(128), NumberOfRows varchar(50),ReservedSpace varchar(50), TableDataSpace varchar(50),IndexSize varchar(50), unused varchar(50)) EXEC sp_msforeachtable 'insert into #IxSizes exec sp_spaceused [?]' GO --Filtering the result set to show only table names, their dedicated --spaces within a database and indexes for each of tables SELECT TableName AS [Table], IndexSize AS [Total indexes size] FROM #IxSizes ORDER BY TableName DESC GO DROP TABLE #IxSizes GO |
In this case, the query is executed against AdventureWorks2014 database, and result set is shown below:
This cumulative value (Total indexes size) summarizes the size of all indexes in the set database.
For illustrative purposes, we will show how indexes can utilize a table space (in two separate cases), with a select statement like this below:
|
1 2 3 4 5 6 |
--Use the previous script, with this SELECT statement instead SELECT TableName AS [Table], TableDataSpace AS [Table size], IndexSize AS [Total indexes size] FROM #IxSizes |
In the provided example, also executed against AdventureWorks2014 database, indexes for the StateProvince table are almost 2x the size of the table size, which is the sign of uncontrolled index growth, which should be addressed with several optimizing techniques. On the other hand, the WorkOrder table has an index which is 2x smaller than the table itself, which doesn’t appear to be a problem.
Check the total size of indexes within a particular database
Input a desired database name and execute the T-SQL script below (which incorporates the sp_spaceused stored procedure, with an additional parameter):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE <database_name> GO --First part of the script creates temporary table, --which will hold results of sp_spaceused execution CREATE TABLE #IxSizes(database_name nvarchar(128), database_size varchar(50),[unallocated space] varchar(50), reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50)) INSERT INTO #IxSizes EXEC sp_spaceused @oneresultset = 1 GO --Narrowing the result set to show only database name, --and total size of present indexes SELECT database_name AS [Database], index_size AS [Total indexes size] FROM #IxSizes GO DROP TABLE #IxSizes GO |
Note: The oneresultset parameter can be used only when mentioned stored procedure is executed against a database on SQL Server 2016 instance (used in this article).
Although this is single-database query, it is possible to list and compare all databases from a single SQL Server instance. Run this slightly modified script, mentioned earlier, on a desired SQL Server instance,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE #IxSizes(database_name nvarchar(128), database_size varchar(50),[unallocated space] varchar(50), reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50)) EXEC sp_msforeachDB 'USE [?]; INSERT INTO #IxSizes EXEC sp_spaceused @oneresultset = 1' GO SELECT database_name AS [Database], index_size AS [Total indexes size] FROM #IxSizes GO DROP TABLE #IxSizes GO |
and results will display like this:
Alternatively, use this query to get the size of all indexes present in all databases:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE #IxSizes(database_name nvarchar(128), database_size varchar(50),[unallocated space] varchar(50), reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50)) EXEC sp_msforeachDB 'USE [?]; INSERT INTO #IxSizes EXEC sp_spaceused @oneresultset = 1' GO --This update statement trims the word "KB" from rows in index_size column --in order to easily summarize and calculate the total size of indexes on a --SQL Server instance UPDATE #IxSizes SET index_size = REPLACE(index_size,' KB','') GO SELECT SUM(CAST(index_size AS int)) AS [Total indexes size (KB)] FROM #IxSizes GO DROP TABLE #IxSizes GO |
Daniel is currently working for ApexSQL LLC as Software sales engineer where he specializes for the BI environment.
- How to monitor the SQL Server tempdb database - July 6, 2017
- How to monitor total SQL Server indexes size - June 12, 2017
- How to set and use encrypted SQL Server connections - May 8, 2017