When it comes to SQL Server performance tuning, understanding the size of indexes on your database is crucial. Knowing the size of indexes can help you identify potential performance bottlenecks and optimize your database for better query execution.

Today, we will discuss a simple script that can be used to find the size of all the indexes on your database. This script will provide you with valuable insights into the size of each index, allowing you to make informed decisions about index optimization.

Here is the script:

SELECT
    OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName,
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID, i.index_id, i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID), i.index_id

This script retrieves the schema name, table name, index name, index ID, and size of each index in kilobytes. By joining the necessary system tables, it calculates the size of each index based on the used pages.

Once you have executed this script, you will have a comprehensive list of all the indexes in your database along with their respective sizes. This information can be used to identify large indexes that may be impacting performance or to prioritize index optimization efforts.

If you are interested in further optimizing your SQL Server performance, I offer an Index Tuning and Strategy Guidance 99-minute session. In this session, we will analyze every single index on your database, identify useless indexes, propose impactful new indexes, and provide guidance on index-related settings. Additionally, I will teach you and your team how to read execution plans and demonstrate how to create proper indexes for your system.

By the end of the session, you will have all the necessary scripts and knowledge to optimize indexes on your own in the future.

Remember, understanding the size of indexes is just one aspect of SQL Server performance tuning. It is important to regularly monitor and optimize your database to ensure optimal performance.