Find the size of Index in SQL Server

sp_spaceused gives the size of table and index but it gives the sum of size of all indexes on a table.What if you need to capture size of individual index on a table.Thats where the DMF sys.dm_db_index_physical_stats comes handy.

This DMF will return lot of values but if the last parameter to the DMF is 'detailed' then you get two columns that can be used to find the size of each index.They  are avg_record_size_in_bytes and record_count.
If these columns are multiplied the resultant is the size of  that index.

The query given below returns the name of Database, ObjectId, Objectname, IndexId, IndexDescription, Size of Index in MB, Last Updated Statistics Date and Avg Fragmentation.

SELECT DatabaseName, ObjectId, ObjectName, IndexId, Index_Description, CONVERT(DECIMAL(16,1), (SUM(avg_record_size_in_bytes * record_count)/ (1024.0 *1024))) AS  [Size of Index(MB)], last_updated AS [Statistic last Updated], Avg_Fragmentation_In_Percent FROM (SELECT DISTINCT DB_Name(Database_id) AS 'DatabaseName', OBJECT_ID AS ObjectId, Object_Name(Object_id) AS ObjectName, Index_ID AS IndexId, Index_Type_Desc AS Index_Description, avg_record_size_in_bytes , record_count, STATS_DATE(object_id,index_id) AS 'last_updated', Convert(Varchar,round(Avg_Fragmentation_In_Percent,3)) AS 'Avg_Fragmentation_In_Percent' FROM sys.dm_db_index_physical_stats(db_id('PM_Db'), NULL, NULL, NULL, 'detailed') Where Object_id IS NOT NULL AND Avg_Fragmentation_In_Percent <> 0) T GROUP BY DatabaseName, ObjectId, ObjectName, IndexId, Index_Description, last_updated, Avg_Fragmentation_In_Percent

What is clustered index and non-clustered index

SQL Server supports two types of indexes: clustered index and non-clustered index. Here are the main differences between them:

* One table can only have only one clustered index.
But one Table can have many non clustered index.

* No separate space is required for clustered index. It forces the data to be stored on the index key.
But separate space is required for non clustered index.

* The default index created as part of the primary key column is a clustered index.

* A table with a clustered index is called clustered table. Its rows are stored in a B-Tree structure sorted.
A table without any clustered indexes is called non-clustered table. Its rows are stored in heap structure unsorted.

Index Optimization Tips

General concepts

In this article I want to show how you can improve the speed of your queries by choosing the proper indexes, what kinds of indexes MS SQL supports and what is the advantage and disadvantage of using indexes in particular situation.

There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf nodes of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
When you create clustered or nonclustered index you can set the fill factor option to specify how full SQL Server should make each index page. When there is no free space to insert new row on the index page, SQL Server will create new index page and transfer some rows from the previous page to the new one. This operation is called page splits.
You can reduce the number of page splits by setting the appropriate fill factor option to reserve free space on each page of the index. The fill factor is a value from 1 through 100 that specifies the percentage of the index page to be left empty.
The default value for fill factor is 0. It is treated similarly to a fill factor value of 100, the difference in that SQL Server leaves some space within the upper level of the index tree for FILLFACTOR = 0. The fill factor percentage is used only at the time the index is created.
A covering index is a index, which includes all of the columns referenced in the query. So the index contains the data you are looking for and SQL Server does not have to look up the actual data in the table.
When you perform many data modification operations (INSERT, UPDATE or DELETE statements), then the table fragmentation occurs. If you want to determine the level of fragmentation, you can run the DBCC SHOWCONTIG command.

Optimization tips

· Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.

· Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.

· Try to create indexes on columns that have integer values rather than character values.

· If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.

· If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns.

· Create surrogate integer primary key (identity for example) if your table will not have many insert operations.

· Clustered indexes are more preferable than nonclustered, if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY.

· If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.

· You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index.

· You can use sp_MSforeachtable undocumented stored procedure to rebuild all indexes in your database. Try to schedule it to execute during CPU idle time and slow production periods.

sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"