Earlier I had ran puzzle where I asked question regarding size of index table for each index in database over here SQL SERVER – Size of Index Table – A Puzzle to Find Index Size for Each Index on Table. I had received good amount answers and I had blogged about that here SQL SERVER – Size of Index Table for Each Index – Solution. As a comment to that blog I have received another very interesting comment and that provides near accurate answers to original question. Many thanks to Rama Mathanmohan for providing wonderful solution.
SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
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
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id
Let me know if you have any better script for the same.
Reference: Pinal Dave (https://blog.sqlauthority.com)
22 Comments. Leave new
Hi Pinal,
I do not say better, but maybe another approach to enthusiasts in powershell and SQLSPX library would be:
1 – All indexes in all tables and all databases
Get-SqlDatabase -sqlserver “Yourserver” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused
2 – All Indexes in all tables and especific database
Get-SqlDatabase -sqlserver “Yourserver” “Yourdb” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused
3 – All Indexes in especific table and database
Get-SqlDatabase -sqlserver “Yourserver” “Yourdb” | Get-SqlTable “YourTable” | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused
and to output to txt.. pipe Out-File
Get-SqlDatabase -sqlserver “Yourserver” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused | out-file c:IndexesSize.txt
If you have one txt with all your servers, can be for all of them also.
Lets say you have all your servers in servers.txt: something like
NameServer1
NameServer2
NameServer3
NameServer4
We could Use :
foreach ($Server in Get-content c:tempservers.txt) {
Get-SqlDatabase -sqlserver $Server | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused
}
:)
Hi Laerte Junior,
This is excellent comment! I really like it.
Kind Regards,
Pinal
Hi there. What units is “spaceused” in? I cannot find any documentation on this…
Pinal,
Here is one I wrote. This one gives the filegroup the object belongs to and filters out system objects.
SELECT
fg.name as FileGroup,
obj.name as table_name,
ISNULL(ind.name,”) as index_name,
ind.type_desc,
used_page_count,
used_page_count/128 AS used_page_size_MB,
reserved_page_count,
reserved_page_count/128 AS reserved_page_size_MB
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.indexes AS ind
ON ps.object_id = ind.object_id
and ps.index_id = ind.index_id
INNER JOIN sys.objects as obj
ON ind.object_id=obj.object_id
INNER JOIN sys.filegroups as fg
ON fg.data_space_id=ind.data_space_id
WHERE obj.type’S’
ORDER BY obj.name, ind.name
Thanks,
Jeremy
Hi Pinal,
I used
SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS ‘Indexsize(KB)’
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
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id
this your query to get the index size which is giving 20496 KB
for the same m trying now sp_spaceused SP again to get the index size then for total database it is giving me 808KB There is a vast difference.
AND one more thing
this size which from this query we are getting is calculated for data pages which stores data not which stores index
i want seperate index size which not includes data.
i made a crosscheck and that time i used query
EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?'”
used query is giving me a table size 20336 KB and index size for that table 160 KB
in a nutshell,
from ur query i m getting index size 20496 KB and from sp_spaceused i m getting index for that atble 160 KB
If you wish i can send you the snaps for that in ur email.
and dont mind if directly or indirectly i had written something not properly as i had only 6 months experience i m fresher
THANKS and REGARDS
Kamesh Shah
If you are using clustered index the size of the index will be the size of the table itself.
Please check your index if that is clustered.
i have send you snaps in email
THANKS and REGARDS
Kamesh Shah
hi Sir,
It is now giving absolute correct result.i made that index non clustered and now perfect result.
Amazing hands on SQL SERVER Sir.HATS OFF to you..
i have drawn conclusion that if there is a clustered index then size of index would be displayed index size + table size
but if the index is non clustered then size of index will be separate from table size and
sir note that not even a single insert of mine is overflowing the page size that is 8060 bytes so would be in a single partition and no furhter allocation.
but why sizes are consolidated and separated ?? see as far as i know then it has to do something with mix and uniform extents or else concept is there??
My second perception on this is (just what i know)
–> Clustered index consist a data rows in a leaf node thats why its size is consolidated with table size
–> non cluster index contains a row locator at leaf node thats why index structure is totally separated from table data structure
my question is like why clustered index size is calculated like=table size+index size
and non clustered is like only table size different and index size different???
THANKS AND REGARDS
Kamesh Shah
clustered index is table itself.
Thnx sir
its very much clear now about index sizes.
THANKS AND REGARDS
Kamesh Shah
We can use sp_estimate_data_compression_savings to know the size of each index.
Thanks
Rohit
hello plz help to get nonclustered index size without any data occupied i mean wat the above queries giving size of each nonclustered index size with data in the columns but i need query to find nonclustered size without any data added in the columns then the size comes within bytes not kb
by running
“SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS ‘Indexsize(KB)’
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
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id”
Why some indexname are null ?
Create a temp table, use msforeachtable to query the indicies on each table and save the results back to your temp table.
CREATE TABLE #indexSizes(ID int, Name nvarchar(4000), SizeInKB int, Comments nvarchar(4000))
exec sp_MSforeachtable ‘insert into #indexSizes(ID, Name, SizeInKB, Comments) exec sp_MSIndexSpace ”?” ‘
select * from #indexSizes
drop table #indexSizes
Hi Pinal,
Thanks for this excellent post!
i read it completely, found simply great.
A big Thanks to Laerte Junior also, he added nice inputs!
We can also get the details regarding Index by system stored procedure.
sp_estimate_data_compression_savings
[ @schema_name = ] ‘schema_name’
, [ @object_name = ] ‘object_name’
, [@index_id = ] index_id
, [@partition_number = ] partition_number
, [@data_compression = ] ‘data_compression’
[;]
Example:-
sp_estimate_data_compression_savings ‘dbo’, ‘dep’, NULL, NULL, ‘row’
Go
sp_estimate_data_compression_savings ‘dbo’, ’emp’, NULL, NULL, ‘row’
Note:
1.Compression and sp_estimate_data_compression_savings are available only in the SQL Server 2008 Enterprise and Developer editions.
2. If the existing data is fragmented, you might be able to reduce its size without using compression by rebuilding the index. For indexes, the fill factor will be applied during an index rebuild. This could increase the size of the index.
Regards,
Girijesh
Hi,
I am looking for a script which will give me the tablename,indexname,size of the index, allocated size, fragmentation percent for a database
can you please help me.
Hi Pinal,
Thanks for Information…
But it shows both database table size and index size..
it’s being a long time since the creation for this post but I wanted to share my script too:
WITH CteIndex
AS
(
SELECT
reservedpages = (reserved_page_count)
,usedpages = (used_page_count)
,pages = (
CASE
WHEN (s.index_id pages THEN CASE WHEN ct.index_id < 2 THEN pages ELSE (usedpages – pages) END ELSE 0 END) * 8, 15, 0) + ' KB')
FROM CteIndex ct
INNER JOIN sys.objects o ON o.object_id = ct.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , NULL) ps ON ps.object_id = o.object_id
AND ps.index_id = ct.index_id
ORDER BY name ASC
I see the same behavior: the size reported is not the index-only size
Here is the one i’m using , i added some details regarding the rows number, pages , space used etc
–*********************************************************************************
— Retrieve Tablename, IndexName, IndexType, PrimKey, Nb rows, Indexsize
— REMINDER:
— 1/ A clustered index has always the same number of rows as the table itself.
— 2/ The NON CLUSTERED INDEX will REALLY give the index size (same as properties/storage at table level)
— 3/ HEAP Table is a Table that doesn’t have a Clustered Index
–*********************************************************************************
USE
GO
— Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
SELECT
–t.NAME AS TableName,
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
i.type_desc,
i.is_primary_key,
p.[rows],
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as ‘Indexsize(MB)’,
(sum(a.used_pages) * 8) as ‘Indexsize(KB)’,
–(sum(a.data_pages) * 8) / 1024 as DataSpaceMB,
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY
i.OBJECT_ID,i.index_id,i.name,i.type_desc,i.is_primary_key,p.[rows]
–t.name,i.index_id,i.name,i.type_desc,i.is_primary_key,p.[rows]
ORDER BY
–4 DESC
OBJECT_NAME(i.OBJECT_ID),i.index_id
–t.name
–WHERE
–AND OBJECT_NAME(p.object_id) = ‘YourTableName’
–t.name = ‘YourTableName’
Hello Pinal, thanks for the good script.
I have developed another script that gives me a little bit different values for index Size.
If I compare the index size values returned by SSMS GUI (in terms of pages) my script is more precise. I simply use the sys.dm_db_index_physical_stat (it returns the size already) and NOT the sys.allocation_units.
select object_name(Ix.object_id), Ix.name, ix.index_id, ps.page_count
from sys.dm_db_index_physical_stats(db_id(),null,null,NULL,’DETAILED’) PS
INNER JOIN sys.indexes IX
ON ix.object_id = PS.object_id
AND ix.index_id = Ps.index_id
order by 4 desc
I would like to have your opinion.
Thanks
Saverio
This is good one.