The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

Posts Tagged ‘capacity’

Seek & Understand Top Indexes by Size in your SQL database

Posted by Sudarshan Narasimhan on June 3, 2016


This post is Part 2 of my series of posts on Index Maintenance for a DBA. So here goes.

Often, when you are performing capacity planning or upgrades or working with 3rd party vendor on database schema upgrades, you need to look into the existing indexes on the database. One of the key things you need to consider is disk space. Aside from the actual “data” in the tables, indexes also occupy space depending on the type of index. Here is a handy script that I use to quickly understand how many indexes are present for each table and the size of each index, in any given SQL database.

If you link this data back to my post “Seek & Destroy Duplicate Indexes in your SQL Database“, you will be on your way to hopefully free up some space and realize space cost savings.

This query only reports on user objects (object_id>99) and filters out HEAP tables. There are some exceptions to the filter on user objects (like service broker tables, CDC tables etc) which will still be considered as “user objects” though it supports a system functionality.

USE [msdb]  /*Replace with your Database Name */
GO
SELECT 
    DB_NAME() DatabaseName,
    OBJECT_SCHEMA_NAME(i.OBJECT_ID) SchemaName,
    OBJECT_NAME(i.OBJECT_ID ) TableName,
    i.name IndexName,
    i.type_desc as [IndexType],
    i.is_primary_key as [PrimaryKeyIndex],
    i.is_unique as [UniqueIndex],
    i.is_disabled as [Disabled],
    SUM(s.used_page_count) / 128.0 IndexSizeinMB,
    CAST((SUM(s.used_page_count)/128.0)/1024.0 as decimal(10,6))  IndexSizeinGB
FROM sys.indexes AS i
INNER JOIN sys.dm_db_partition_stats AS S
    ON i.OBJECT_ID = S.OBJECT_ID AND I.index_id = S.index_id
WHERE i.object_id>99 and i.type_desc  'HEAP'
GROUP BY i.OBJECT_ID, i.name, s.used_page_count, i.type_desc, i.is_primary_key, i.is_unique, i.is_disabled
ORDER BY s.used_page_count DESC
GO

 

Script Result
Here is a sample output from my test system showing all the indexes on MSDB database.

Index Size

 

Stay tuned for more index related scripts in this series.

-Sudarshan (TheSQLDude)

Advertisements

Posted in DB Maintenance, Indexing, T-SQL | Tagged: , , , | 1 Comment »