How to Find Which SPs Use Forced Index

One of the periodical DBA task is to examine index usage statistics with sys.dm_db_index_usage_stats DMV and to find unused indexes. After that these unused or rarely used indexes can be dropped if they are really not used.

But before that you should examine if these indexes forced in any of SQL Server objects such as stored procedure, function, view or so on. Otherwise if dropped index is used in any SQL Server objects, after deletion these objects gets error during execution.

Below script can be used to determine which SQL Server objects use forced index.

--Find SQL Server Objects which use index force

create table #tmp1 (DBName sysname,object_id int, name sysname, type varchar(3), type_desc varchar(50), OriginalText varchar(max), text varchar(max))

GO

insert #tmp1

      select db_name(),sm.object_id,o.name,o.type,o.type_desc

            ,definition as OriginalText

            ,replace(

                  replace(

                        replace(

                              replace(

                                    replace(

                                          replace(replace(upper(definition),' INDEX ','(INDEX')

                                    ,'INDEX ','(INDEX'),' ','')

                              ,'CHARINDEX','')

                        ,'PATINDEX','')

                  ,'CHARINDEX','')

            ,'PATINDEX','') as text

      from sys.sql_modules sm

      left join sys.objects o on o.object_id=sm.object_id

      where o.type in ('AF','FN','P','IF','FS','FT','PC','RF','TA','TF','TR','V','X')

select *

from #tmp1 t

where t.text like '%(Index(%' or t.text like '%(Index=%'

order by t.name

To see the result set of the upper query let’s run the below demo.

--create a work database

Create database DBindexForce

GO

use DBindexForce

GO

--create a work table

Create table tblindexForce(col1 int, col2 char(10))

GO

create clustered index IX_1 on tblindexForce (col1)

GO

--Create 2 work SPs

--First one uses forced index

create proc mySP1

as

      select * from tblindexForce with(Index = IX_1)

GO

create proc mySP2

as

      select * from tblindexForce

GO

--Find SQL Server Objects which use index force

create table #tmp1 (DBName sysname,object_id int, name sysname, type varchar(3), type_desc varchar(50), OriginalText varchar(max), text varchar(max))

GO

insert #tmp1

      select db_name(),sm.object_id,o.name,o.type,o.type_desc

            ,definition as OriginalText

            ,replace(

                  replace(

                        replace(

                              replace(

                                    replace(

                                          replace(replace(upper(definition),' INDEX ','(INDEX')

                                    ,'INDEX ','(INDEX'),' ','')

                              ,'CHARINDEX','')

                        ,'PATINDEX','')

                  ,'CHARINDEX','')

            ,'PATINDEX','') as text

      from sys.sql_modules sm

      left join sys.objects o on o.object_id=sm.object_id

      where o.type in ('AF','FN','P','IF','FS','FT','PC','RF','TA','TF','TR','V','X')

select *

from #tmp1 t

where t.text like '%(Index(%' or t.text like '%(Index=%'

order by t.name

--CleanUp

Use master

GO

drop table #tmp1

GO

drop database DBindexForce

GO

Comments

• Anonymous
  November 02, 2015
  Thanks for the post, found a lot of useful information <a href="192-168-1-1admin.com/"> 192.168.1.1</a>