Determine Index size of a particular table in SQL Server

You can use below T-SQL query to determine size of Index

Either method, will help you to determine Size of Indexes of a particular Table

Note: Please replace SchemaName.TableName in below query with the appropriate schema and table name whose index size you want to find

/* Method 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
WHERE s.[object_id] = object_id('SchemaName.TableName')
GROUP BY i.name
ORDER BY i.name



/* Method 2 */
SELECT    
       i.name              AS IndexName,    
       SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(    
       db_id(), object_id('SchemaName.TableName'), NULL, NULL, 'DETAILED') 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




Comments

Post a Comment

Popular posts from this blog

How to Troubleshoot Subscription issue in Reporting Services

Tips on troubleshooting Subscription issue in Reporting Services 1 – Look at the Reporting Services Log File Error Message from Log File: Email Provider has no server or pickup directory specified, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;   ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: Email Provider has no server or pickup directory specified, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;   INFO: Notification 20f7b482-eb74-4b51-9e53-9f0609034e72 completed.  Success: False, Status: Failure writing file Startup :  The report server has encountered a configuration error. , DeliveryExtension: Report Server FileShare, Report: Startup, .......      2 - Run below query agains...
Read more

Request failed with HTTP status 503 Server Error

Image
Problem: When 100's of users ae trying to access Reporting services report then, SSRS throws error as below : "Request failed with HTTP status 503 Server Error" and how to resolve this error Solution: From experience, this issue can be caused by multiple reasons Problem might be related to: 1 - TCP/IP Port Exhaustion: When a client initiates a TCP/IP socket connection to a server, the client typically connects to a specific port on the server and requests that the server respond to the client over a short-lived TCP or UDP port. The default range of short-lived ports used by client applications is 1025 through 5000. Under certain conditions, it is possible that the available ports in the default range are exhausted. 2 - If you are using any custom assembly method in any of your report (say for example PrivateFontCollection class or anything else), if yes then you might want to review that 3 - Change MaxActiveReqForOneUser from its default value o...
Read more

MDX Queries - Current Day - Month and Year

Problem: Query to get Current Day - Month and Year in MDX /* MDX for retrieving current Year */ WITH MEMBER [Measures].[Current Year] AS     VBAMDX. Format (VBAMDX.Now(), "yyyy" ) SELECT   {[Measures].[Current Year]} ON COLUMNS FROM [Analysis Services Tutorial]; /* Current Month */ WITH MEMBER [Measures].[Current Month] AS     VBAMDX. Format (VBAMDX.Now(), "MM" ) SELECT   {[Measures].[Current Month]} ON COLUMNS FROM [Analysis Services Tutorial]; /* MDX for retrieving current Date */ WITH MEMBER [Measures].[Current Date] AS     VBAMDX. Format (VBAMDX.Now(), "dd" ) SELECT   {[Measures].[Current Date]} ON COLUMNS FROM [Analysis Services Tutorial];
Read more