Category Archives: Indexes

Script to find the size of all Indexes in a database

Indexes, Scripts October 1, 2013 1 Comment

Continuing with my interest with Indexes, i wrote this script that finds the size of all indexes in a database along with the table and the filegroup on which the index resides. I have used the AdventureWorks2012 database as an example. Please replace the DB name for which you want to find the information.

USE AdventureWorks2012
go
IF OBJECT_ID('tempdb..#Indexdata', 'U') IS NOT NULL
DROP TABLE #Indexdata

DECLARE
@SizeofIndex BIGINT, @IndexID INT,
@NameOfIndex nvarchar(200),@TypeOfIndex nvarchar(50),
@ObjectID INT,@IsPrimaryKey INT,
@FGroup VARCHAR(20)

create table #Indexdata (name nvarchar(50),
IndexID int, IndexName nvarchar(200),
SizeOfIndex int, IndexType nvarchar(50),
IsPrimaryKey INT,FGroup VARCHAR(20))
DECLARE Indexloop CURSOR FOR
SELECT idx.object_id, idx.index_id, idx.name, idx.type_desc
,idx.is_primary_key,fg.name
FROM sys.indexes idx
join sys.objects so
on idx.object_id = so.object_id JOIN sys.filegroups fg
ON idx.data_space_id = fg.data_space_id
where idx.type_desc != 'Heap'
and so.type_desc not in ('INTERNAL_TABLE','SYSTEM_TABLE')

OPEN Indexloop

FETCH NEXT FROM Indexloop
INTO @ObjectID, @IndexID, @NameOfIndex,
@TypeOfIndex,@IsPrimaryKey,@FGroup

WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @SizeofIndex = sum(avg_record_size_in_bytes * record_count)
FROM sys.dm_db_index_physical_stats(DB_ID(),@ObjectID,
@IndexID, NULL, 'detailed')

insert into #Indexdata(name, IndexID, IndexName, SizeOfIndex,
IndexType,IsPrimaryKey,FGroup)
SELECT TableName = OBJECT_NAME(@ObjectID),
IndexID = @IndexID,
IndexName = @NameOfIndex,
SizeOfIndex = CONVERT(DECIMAL(16,1),(@SizeofIndex/(1024.0 * 1024))),
IndexType = @TypeOfIndex,
IsPrimaryKey = @IsPrimaryKey,
FGroup = @FGroup

FETCH NEXT FROM Indexloop
INTO @ObjectID, @IndexID, @NameOfIndex,
 @TypeOfIndex,@IsPrimaryKey,@FGroup
END
CLOSE Indexloop
DEALLOCATE Indexloop

select name as TableName, IndexName, IndexType,
SizeOfIndex AS [Size of index(MB)],
case when IsPrimaryKey = 1 then 'Yes' else 'No' End as [IsPrimaryKey]
,FGroup AS [File Group]
from #Indexdata order by SizeOfIndex DESC

Snapshot of the output below:
Indexsize

Related articles
Advertisement

Find all unused indexes in a database

Indexes, Schema, Scripts September 3, 2013 Leave a comment

While doing an index analysis activity i had to find the indexes are hardly being used in our production DB’s. I wrote this script to acheive the same

SELECT case when os.is_published = 1 then 'Replicated' else 'Not Replicated'
end as ReplicatedorNot,
OBJECT_NAME(idx.object_id) AS TableName ,
idx.name as Index_name,
istat.user_lookups,
istat.user_scans,
istat.user_seeks,
istat.user_updates
FROM sys.dm_db_index_usage_stats AS istat
JOIN sys.indexes AS idx ON idx.index_id = istat.index_id
join sys.objects os
on idx.object_id = os.object_id
AND idx.object_id = istat.object_id
WHERE
istat.database_id = DB_ID('databasename') -- type the name of the Db
and object_name(idx.object_id) not like 'Ms%'
and object_name(idx.object_id) not like 'sys%'
and object_name(idx.object_id) not like 'id_%'
and object_name(idx.object_id) not like 'conflict%'
and object_name(idx.object_id) not like 'Any other tables that you like to avoid%'
AND idx.is_unique_constraint = 0 -- no unique indexes
AND idx.is_primary_key = 0
AND idx.is_disabled = 0
AND idx.type > 1 -- don't consider heaps/clustered index
AND ( ( istat.user_seeks + istat.user_scans +istat.user_lookups )  <  istat.user_updates
or (istat.user_seeks = 0 AND istat.user_scans = 0 AND istat.user_lookups = 0))
order by (istat.user_seeks + istat.user_scans +
istat.user_lookups)
%d bloggers like this: