Get Information of Index of Tables and Indexed Columns

Creating Index in Sql

 

CREATE INDEX employee_first_last_name_idx ON
  employee (first_name, last_name);

Droping Index in Sql

Drop  index employee_first_last_name_idx;

Knowledge of T-SQL inbuilt functions and store procedure can save great amount of time for developers. Following is very simple store procedure which can display name of Indexes and the columns on which indexes are created. Very handy stored Procedure.
USE AdventureWorks;
GO
EXEC sp_helpindex 'Person.Address'
GO

Above SP will return following information.
IndexName – IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
Index_Description – nonclustered, unique located on PRIMARY
Index_Keys – AddressLine1, AddressLine2, City, StateProvinceID, PostalCode

Let me know if you think this kind of small tips are useful to you.

Either we can use DBCC Command also

 

The DBCC SHOWCONTIG command shows you the index information. The example below checks the "indexname" index on the table "tablename" in the database "dbname".

DBCC SHOWCONTIG('dbname.dbo.tablename', 'indexname')

Some example output is shown below. This particular example was from a very fragmented index from a table with around 10 million records. Each day around 1 million records are updated/and or inserted and records 60 days or older are deleted.

DBCC SHOWCONTIG scanning 'tablename' table...
Table: 'tablename' (949578421); index ID: 1, database ID: 12
TABLE level scan performed.
- Pages Scanned................................: 333557
- Extents Scanned..............................: 41805
- Extent Switches..............................: 262556
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 15.88% [41695:262557]
- Logical Scan Fragmentation ..................: 75.11%
- Extent Scan Fragmentation ...................: 13.04%
- Avg. Bytes Free per Page.....................: 2853.9
- Avg. Page Density (full).....................: 64.74%

A low percentage for scan density is bad. A high percentage for logical scan fragmentation is bad.
To defragment your index you need to use the DBCC DBREINDEX or DBCC INDEXDEFRAG commands. DBCC DBREINDEX allows you to rebuild all indexes at once, but is an offline operation so the tables cannot be used while it is running. DBCC INDEXDEFRAG must be called for each index you want to defragment but you can continue to use the tables.
The syntax for DBCC DBREINDEX is as follows to rebuild the above index:

DBCC DBREINDEX('dbname.dbo.tablename', 'indexname')

or all indexes:

DBCC DBREINDEX('dbname.dbo.tablename')

You can also change the fill factor for the index, which affects how much space is left in each page with a third parameter. To make it e.g. 70, you could do this for all indexes in the table:

DBCC DBREINDEX('dbname.dbo.tablename', '', 70)

After doing the above on the example table and rebuilding the index with a fill factor of 70, running DBCC SHOWCONTIG again gave the following, much better, result:

DBCC SHOWCONTIG scanning 'tablename' table...
Table: 'tablename' (949578421); index ID: 1, database ID: 12
TABLE level scan performed.
- Pages Scanned................................: 297679
- Extents Scanned..............................: 37210
- Extent Switches..............................: 37209
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [37210:37210]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.16%
- Avg. Bytes Free per Page.....................: 2335.3
- Avg. Page Density (full).....................: 71.15%