Home > T - SQL > SQL Server Indexes – Pros and Cons (Part 3)

SQL Server Indexes – Pros and Cons (Part 3)

SQL Server Indexes – Pros and Cons (Part 2)

Optimizing Indexes in SQL Server: Although there are many bright faces of indexes but every coin have two sides. So it’s always advisable to implement indexes wisely on a table. Here we will check out different perspectives to view indexes on a given table.

  • By SYS.INDEXES
SELECT * FROM SYS.INDEXES WHERE OBJECT_ID = OBJECT_ID('dbo.EmployeeDim');

Output:

Output 1

Here type specifies the type of the index used i.e.

0              =             Heap (No indexes)

1              =             Clustered Index

2              =             Non Clustered Index

  • By SP_HELPINDEX
EXEC sp_helpindex 'dbo.EmployeeDim'

Output:

Output 2

  • By SYSINDEXES system table:
SELECT * FROM SYSINDEXES WHERE id = OBJECT_ID('dbo.EmployeeDim');

Output:

Output 3

Here indid column represents the index key id, if indid value is 0 then there is no index for the given table.

  • By IND() function:
Declare @DBID Int, @TableID Int
Select @DBID = DB_ID('OLAP'), @TableID = OBJECT_ID('EmployeeDim')
 
DBCC ind(@DBID, @TableID, -1)


Checking Fragmentation of indexes: Continuous or frequent update operations on database leads to Index fragmentation resulting downgrade on performance. The update operations may be INSERT, UPDATE or DELETE operations. Here, the index pages are not aligned continuously. So it’s advisable to keep track of the fragmentation for smooth operation of the database.

SQL Server 2005 introduces two commands for this:

  • DBCC SWOWCONTING command: This command gives detail information about the usage of an index of a given table.
DBCC SHOWCONTIG ('dbo.EmployeeDim' )

Output:

DBCC SHOWCONTIG scanning 'EmployeeDim' table...
Table: 'EmployeeDim' (1797581442); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7825.0
- Avg. Page Density (full).....................: 3.32%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

By default it will show the information about the index having id = 1. But to see the fragmentation information for a specific index, use the below command:

DBCC SHOWCONTIG ('dbo.EmployeeDim', 'IX_EmployeeDim' )

Output:

DBCC SHOWCONTIG scanning 'EmployeeDim' table...
Table: 'EmployeeDim' (1797581442); index ID: 2, database ID: 13
LEAF level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 8047.0
- Avg. Page Density (full).....................: 0.58%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The main thing here to note is Scan Density [Best Count:Actual Count] and Logical Scan Fragmentation. A lower Percentage of Scan Density is not good and a higher percentage of Logical Scan Fragmentation is also not good.

Whenever you found Scan Density lower than 50% and Logical Scan Fragmentation more than 50%, perform Index Rebuild or Reorganize operation.

USE OLAP
Go
DBCC DBREINDEX('dbo.EmployeeDim', 'IX_EmployeeDim')
--TableName, IndexName
  • SYS.DM_DB_INDEX_PHYSICAL_STATS DMV (Dynamic Management View): It’s a function that takes tablename as a parameter and shows all the information regarding the indexes of the table. Although it has many useful information but we are going to focus on only one column named ‘avg_fragmentation_in_percent’ which will tell us the percentage of fragmentation occurred for an index.

Parameter Information:

Parameter_name Type Length
@DatabaseId smallint 2
@ObjectId int 4
@IndexId int 4
@PartitionNumber int 4
@Mode nvarchar 40
select * from sys.dm_db_index_physical_stats(DB_ID('OLAP'), OBJECT_ID('dbo.EmployeeDim'), NULL, NULL, NULL);

Output:

Output 4

Perform rebuilding of index when the value of the column ‘avg_fragmentation_in_percent’ reaches above 20%.

More details about Fragmentaion and Optimization of indexes will be described in the upcoming posts.

FILL Factor: This is one of the properties of the index, which is basically used for fine tuning of the index performance. It indicates how densely the index is packed when created. This property can only be set while creating the index. It determines the percentage of space on each leaf-level page that has to be filled with data, keeping the rest for future use. The basic idea here is if the table is going to have frequent Updates and Inserts statements; keep the Fill Factor as low as possible resulting in more space for future keys. But if the table is static or read only, then set the Fill Factor as high as possible, so as to reduce the space needed for indexing.  You can set the fill-factor value starting from 1 to 100 (in percentage).

Index manipulation commands:

  • ALTER INDEX REBUILD:
ALTER INDEX IndexName ON SchemaName.TableName
REBUILD;

For all the indexes:

ALTER INDEX ALL ON SchemaName.TableName
REBUILD;
  • ALTER INDEX REORGANIZE:
ALTER INDEX IndexName ON SchemaName.TableName
REORGANIZE;
  • Rename an Index:
EXEC sp_rename 'TableName.OldIndexName', 'NewIndexName'
  • Delete an Index:
DROP Index TableName.IndexName

Note: Dropping indexes on Primary key is not possible as it is being used for PRIMARY KEY constraint enforcement.



Best practices for Indexing:

  • Create index on columns that are frequently used for data retrieval or in filter conditions. For example in SELECT statement and WHERE clause or in ORDER BY, GROUP BY, and DISTINCT clauses. As in our first example, if most of the queries are referencing emp_name column instead of emp_id column, then it is wise to create index on emp_name instead of emp_id i.e. create indexes based on use.
  • Every table should have a clustered index otherwise it will act as heap table only even if it has non-clustered indexes, resulting performance degradation.
  • Create indexes on selective columns only that are frequently used.
  • Try to keep the keys of clustered index as small as possible in size. (i.e. int, smallint)
  • Optimize cluster index for ranged data i.e. data that are retrieved using BETWEEN, >, >=, <, and <=.
  • Creative covering indexes are a wise decision for most frequently used queries.
  • In case of composite indexes, order the columns according to the restrictiveness i.e. smaller quantity first as described in the composite index example above.
  • Create index on all foreign keys which may be helpful in joining operations.
  • Use multiple narrow indexes (i.e. on smaller number of columns, may be single column index) rather than a few wide indexes (composite indexes).
  • Avoid using indexes that are rarely used or else drop them.
  • Identify poorly performing queries with the help of SQL Profiler, Query Execution plan, and try to optimize it.
  • Last but not the least; remove indexes while performing BULK insert or heavy insert operations especially in case of Clustered Index as it will perform additional operations for rearranging the indexes for each insertion of the record. Instead, drop indexes before BULK insert and recreate it after insertion operation finishes.
Categories: T - SQL
  1. September 28th, 2011 at 14:08 | #1

    One of the best articles I read about SQL Server indexes.

  2. Ravi
    September 29th, 2011 at 16:56 | #2

    Great.
    Really, one of the best artiles with simple examples.

  3. Aravind
    March 10th, 2012 at 11:18 | #3

    Speachless!!!!!!!!!! Extremly well described the Index….I enjoyed learning this article and now I can proudly say that I understand what is an index,advantages,disadvantages and use…Thanks a lot!!!!!!!

  1. No trackbacks yet.
You must be logged in to post a comment.