Daniel Tikvicki

How to monitor total SQL Server indexes size

June 12, 2017 by

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:

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:

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:

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):

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,

and results will display like this:

Alternatively, use this query to get the size of all indexes present in all databases:

Daniel Tikvicki
Indexes, Monitoring

About Daniel Tikvicki

Daniel 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.