Monthly Archives: March 2019

How to get the size of every index in a local SQL Server Instance

Database Administrator, dba, SQL Server, ssms, T-SQL, , , , , , , , , , ,

The code below will run against every online (local i.e. not Azure SQL Server) database (excluding the system databases bar the master database) and return the size of each index in each database. Knowing these values is especially important as it is recommended that the free space available on disk should be at least 1.5 times the size of the largest index. This is only one guideline regarding disk space however and use cases vary from database to database.

The output will include:

  • Database Name
  • Schema Name
  • Table Name
  • Index Id
  • Index Name
  • Index Size Mb

You can uncomment the last three lines to focus on nonclustered indexes.

SET NOCOUNT ON;

DECLARE @Database TABLE (DbName SYSNAME);
DECLARE @IndexStats TABLE (
	ServerName SYSNAME
	,DbName SYSNAME
	,SchemaName SYSNAME
	,TableName SYSNAME
	,IndexId INT
	,IndexType VARCHAR(12)
	,IndexName SYSNAME
	,IndexSizeMb INT
	);
DECLARE @DbName AS SYSNAME;
DECLARE @Sql AS VARCHAR(MAX);

SET @DbName = '';

INSERT INTO @Database (DbName)
SELECT NAME
FROM sys.databases
WHERE NAME NOT IN (
		'tempdb'
		,'msdb'
		,'model'
		)
	AND state_desc = 'ONLINE'
ORDER BY NAME ASC;

WHILE @DbName IS NOT NULL
BEGIN
	SET @DbName = (
			SELECT MIN(DbName)
			FROM @Database
			WHERE DbName > @DbName
			);
	SET @Sql = 'USE ' + QUOTENAME(@DbName) + ';	
	SELECT @@ServerName AS ServerName
	,''' + @DbName + ''' AS ''DbName'' 
	,OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName
	,OBJECT_NAME(i.OBJECT_ID) AS TableName
	,i.index_id AS IndexId
	,CASE WHEN i.index_id > 1 THEN ''Nonclustered'' WHEN i.index_id = 1 THEN ''Clustered'' ELSE ''Heap'' END AS IndexType
	,CASE WHEN i.NAME IS NULL THEN ''No Name'' ELSE i.Name END AS IndexName
	,(8 * SUM(a.used_pages)/1024) AS ''IndexSizeMb''
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
WHERE a.used_pages > 0
GROUP BY i.OBJECT_ID
	,i.index_id
	,i.NAME
ORDER BY IndexSizeMb DESC; 
'

	INSERT INTO @IndexStats
	EXEC (@Sql);
END

SELECT ServerName
	,DbName
	,SchemaName
	,TableName
	,IndexId
	,IndexType
	,IndexName
	,IndexSizeMb
FROM @IndexStats
--WHERE IndexType = 'Nonclustered'
--AND IndexSizeMb > 0
--ORDER BY IndexSizeMb DESC;