SQL Server - How to find the largest sql index and table size

14 June,2012 by Jack Vamvas

Question: How can I find the largest sql table or sql  index in a database? I also want to report on the size of indexes and tables?

Answer:  Here are two different ways . Method 1 is more flexible – and you can build into reporting and capacity planning. Method 2 utilises the SQL Server Standard Report

Method 1

use myDB
GO
CREATE TABLE #TableSpaceUsed

(
           
           Table_name NVARCHAR(255),
           Table_rows INT,
           Reserved_KB VARCHAR(20),
           Data_KB VARCHAR(20),
           Index_Size_KB VARCHAR(20),
           Unused_KB VARCHAR(20)

)

INSERT INTO #TableSpaceUsed

EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT Table_name,Table_Rows,
CONVERT(INT,SUBSTRING(Index_Size_KB,1,LEN(Index_Size_KB) -2)) as indexSizeKB, 
CONVERT(INT,SUBSTRING(Data_KB,1,LEN(Data_KB) -2)) as dataKB, 
CONVERT(INT,SUBSTRING(Reserved_KB,1,LEN(Reserved_KB) -2)) as reservedKB, 
CONVERT(INT,SUBSTRING(Unused_KB,1,LEN(Unused_KB) -2)) as unusedKB
FROM #TableSpaceUsed
ORDER BY dataKB DESC
DROP TABLE #TableSpaceUsed

 

Method 2: Use the Disk Usage by Tables Report  .

 

To use: Right Click on Database on SSMS > Reports > Standard Reports > Disk Usage by Table

Read More

SQL Server - Calculate table size with existing data

Storage Checklist FAQ

Calculate sql row size  


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server - How to find the largest sql index and table size


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer