T-SQL Query to find Index size of all tables

This query gives results in 2 parts by making use of a undocumented stored procedure sp_MSIndexSpace

1. The size of each individual index of a table
2. The total size of index on a table

IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name='TempIndexSpace')
BEGIN
DROP TABLE tempdb..TempIndexSpace
END
CREATE TABLE tempdb..TempIndexSpace 
(ObjectName nvarchar(100)
 ,IndexID int
 ,IndexName nvarchar(100)
 ,[IndexSize(KB)] int
 ,Comments nvarchar(max))
exec sp_msforeachtable 'INSERT INTO tempdb..TempIndexSpace (IndexID,IndexName,[IndexSize(KB)],Comments) EXEC sp_MSIndexSpace [?];
UPDATE tempdb..TempIndexSpace SET ObjectName=''?'' WHERE ObjectName IS NULL'

-- This gives output per index
SELECT * FROM tempdb..TempIndexSpace

-- This gives output per table
SELECT  ObjectName,SUM([IndexSize(KB)]) AS [Index Size (KB)] FROM tempdb..TempIndexSpace
GROUP BY ObjectName

T-SQL Query to find the list of Indexed Views in a Database

Here is the query which can be used for getting the list of Indexed views in a Database.

USE <DatabaseName>

GO

SELECT * FROM sys.objects

WHERE type='V'

and OBJECTPROPERTY(object_id,'IsIndexed')=1

Covering Index or Index with Included Columns

Covering Index or Index with included Columns is a new functionality which is introduced in SQL Server 2005 onwards and is an extension to Non-Clustered Indexes.

From SQL Server 2005 onwards, the functionality of the Non-Clustered indexes can be extended by adding non-key columns to the leaf level of the Non-Clustered index. By including non-key columns, we can create Non-Clustered indexes that cover more queries.

This is because the non-key columns have the following benefits:

• They can be data types that are not allowed as index key columns.
• They are not considered by the Database Engine when calculating the number of index key columns or index key size.

So creating Covering Indexes can significantly improve the query performance because all the columns in the query are included in the index itself, either as key or nonkey columns. And only the index pages and not the data pages will be used in retrieving the data.

Covering indexes can bring in a lot of performance to the query, because it can save a huge amount of I/O operations.

How to Create a Covering Index:

Using T-SQL:

/*This query will create a non-clustered index by name IX_Address_PostalCode

on PostalCode Column and includes the non-key columns AddressLine1,

AddressLine2, City, StateProvince */

USE AdventureWorks;

GO

CREATE NONCLUSTERED INDEX IX_Address_PostalCode

ON SalesLT.Address (PostalCode)

INCLUDE (AddressLine1, AddressLine2, City, StateProvince);

GO

/* And the below Query will be covered by the index and gives more performance */

SELECT AddressLine1, AddressLine2, City, StateProvince, PostalCode

FROM SalesLT.Address

WHERE PostalCode BETWEEN '85000' and '90000';

GO

Using GUI:

This article is also available in pdf format for downloading.

Please Click here to get your copy.

Index Recommendations

This Query when executed gives the recomendation to your indexes whether it has to reindexed or defraged.
This query also gives the fragmentation level of your indexes.

Select db_name(DB_ID()) as DBName,SS.name as SchemaName,SO.name as TableName, SI.name as Indexname,index_type_desc as IndexType ,avg_fragmentation_in_percent as FragmentationPercentage,
(case when avg_fragmentation_in_percent between 10 and 30 then 'Defrag'
when avg_fragmentation_in_percent > 30 Then 'Reindex'
Else 'Can be Ignored Currently'
End) as Recomendation
from sys.dm_db_index_physical_stats(DB_ID(DB_NAME()), null, null, null, 'DETAILED') IPS ,
sys.indexes SI ,sys.objects SO , sys.schemas SS
where IPS.index_id=SI.index_id and IPS.object_id=SI.object_id and
SI.object_id=SO.object_id and SO.schema_id=SS.schema_id and
IPS.index_type_desc in ('NONCLUSTERED INDEX', 'CLUSTERED INDEX')
Order by Recomendation desc