Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Find the size of Index in SQL Server

As we know, sp_spaceused gives the size of table and index but it gives the sum of size of all indexes on a table. What if you need to capture size of individual index on a table? Thats where the following two queries comes handy:

Query 1:
Uses sys.indexes and sys.dm_db_partition_stats DMF to calculate the size of individual index on a table.

USE [<DatabaseName>]
GO

--Query 1
SELECT i.[name] AS IndexName
      ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB 
FROM sys.dm_db_partition_stats AS s 
JOIN sys.indexes AS i 
ON s.[object_id] = i.[object_id] 
AND s.[index_id] = i.[index_id] 
GROUP BY i.[name] 
ORDER BY i.[name] 
GO

 
Sample output of the first query when executed against AdventureWorks database:

Query 2:
Uses sys.dm_db_index_physical_stats and sys.dm_db_partition_stats to calculate the size of individual index on a table. This query is more reliable as compared to first query because it uses DMFs.

USE [<DatabaseName>]
GO

--Query 2
SELECT [DatabaseName]
      ,[ObjectId]
      ,[ObjectName]
      ,[IndexId]
      ,[IndexDescription]
      ,CONVERT(DECIMAL(16,1)
                ,(SUM([avg_record_size_in_bytes] * [record_count])
                        / (1024.0 *1024))) AS  [IndexSize(MB)]
      ,[lastupdated] AS [StatisticLastUpdated]
      ,[AvgFragmentationInPercent] 
FROM (SELECT 
        DISTINCT DB_Name(Database_id) AS 'DatabaseName'
       ,OBJECT_ID AS ObjectId, Object_Name(Object_id) AS ObjectName
       ,Index_ID AS IndexId
       ,Index_Type_Desc AS IndexDescription
       ,avg_record_size_in_bytes 
       ,record_count
       ,STATS_DATE(object_id,index_id) AS 'lastupdated'
       ,CONVERT([varchar](512)
                 ,round(Avg_Fragmentation_In_Percent,3)) AS 'AvgFragmentationInPercent' 
      FROM sys.dm_db_index_physical_stats(db_id('PM_Db'), NULL, NULL, NULL, 'detailed') 
      WHERE OBJECT_ID IS NOT NULL 
        AND Avg_Fragmentation_In_Percent <> 0) T 
GROUP BY DatabaseName
        ,ObjectId
        ,ObjectName
        ,IndexId
        ,IndexDescription
        ,lastupdated
        ,AvgFragmentationInPercent

 
Sample output of the second query when executed against AdventureWorks database:

I hope you will find this information useful :)


Comments

Leave a comment on the original post [basitaalishan.com, opens in a new window]

Loading comments...