How to monitor total SQL Server indexes size June 12, 2017 by Daniel Tikvicki 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: 123456789101112 USE <database_name> GOSELECT 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 szINNER 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_idGROUP 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: 123456789101112131415161718192021 USE <database_name> GO--This part of the script creates temporary table, --which will hold results of sp_spaceused execution for each tableCREATE 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 tablesSELECT TableName AS [Table], IndexSize AS [Total indexes size] FROM #IxSizesORDER BY TableName DESCGODROP TABLE #IxSizesGO 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: 123456 --Use the previous script, with this SELECT statement insteadSELECT 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): 123456789101112131415161718 USE <database_name>GO--First part of the script creates temporary table, --which will hold results of sp_spaceused executionCREATE 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 = 1GO--Narrowing the result set to show only database name, --and total size of present indexesSELECT database_name AS [Database], index_size AS [Total indexes size] FROM #IxSizesGODROP TABLE #IxSizesGO 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, 1234567891011121314 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'GOSELECT database_name AS [Database], index_size AS [Total indexes size] FROM #IxSizesGODROP TABLE #IxSizesGO and results will display like this: Alternatively, use this query to get the size of all indexes present in all databases: 1234567891011121314151617181920 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 instanceUPDATE #IxSizesSET index_size = REPLACE(index_size,' KB','')GOSELECT SUM(CAST(index_size AS int)) AS [Total indexes size (KB)]FROM #IxSizesGODROP TABLE #IxSizesGO About Latest Posts Daniel TikvickiDaniel is a librarian who ran into a vortex of IT world, where he is levitating and learning constantly. He likes books, merely all forms of artistic expression (being a musician/poet himself), and he is underground publisher (fanzines and experimental music). Also, the points of interest include theology, mythology and pseudoscience. Daniel is currently working for ApexSQL LLC as Software sales engineer where he specializes for the BI environment. Latest posts by Daniel Tikvicki (see all) 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 Related posts: How to identify and monitor unused indexes in SQL Server Index Strategies – Part 2 – Memory Optimized Tables and Columnstore Indexes SQL Server non-clustered indexes with included columns SQL Server indexes – series intro What is the difference between Clustered and Non-Clustered Indexes in SQL Server?