Are Your Indexes Being Used Effectively?

In this fast moving world, data is the heart and soul of any enterprise. As the data is growing very rapidly day by day, the biggest challenge which enterprises face today is to store the data in such a way that it can be retrieved quickly whenever required. The most common thought which comes in the mind of database administrators who basically works on performance improvement is to add indexes for tables to improve the data retrieval. However adding too many indexes on a table can sometimes reduce the performance of the table considerably. So it is very important for the database administrator to know whether the indexes created on the tables are used effectively or not. If there are indexes created on a table and they are not used, then they should be drop, as having unwanted index will slow down Insert, Update or Delete operations on the underlying tables.

It has been always a challenge for database administrators to figure out which indexes on a table are helpful and which aren't. In SQL Server 2005, Microsoft introduced Dynamic Management Views (DMV) which return server state information that can be used by developers or database administrators to monitor the health of a SQL Server Instance and identify potential performance issues. Dynamic Management Views basically reflect all the activities on the instance of SQL Server since the last restart of SQL Server. All the Dynamic Management Views exist in the SYS schema and they can be easily identify as they follow the naming convention of dm_*. The list of all the Dynamic Management Views that are available on SQL Server 2005 and higher versions can be obtained by running the below TSQL code.

USE master
GO
SELECT * FROM sys.sysobjects WHERE NAME LIKE 'dm_%'
GO

Unfortunately in the SQL Server editions prior to SQL Server 2005 there is no easy way to identify indexes which are helpful and which aren't. In SQL Server 2000 the only way to identify if an index is being used or not was to capture a workload in profiler and then run it against the Index Tuning Wizard.

 1/6    1 2 3 4 5 6 下一页 尾页