Search code examples
sql-serverregexstored-proceduresdatabase-performance

SQL Server: Finding and Removing index hints in stored procedures

As we live in a World of "delivery first", we now found our self with databases that are infected by tons of index hints.

I wrote a query that might help me find out all the WITH (INDEX= in the database:

SELECT 
    DB_NAME() AS DB_NAME,
    name AS Object_Name,
    type_desc AS Object_Type,
    definition
FROM 
    sys.sql_modules 
INNER JOIN 
    sys.objects ON sys.sql_modules.object_id = sys.objects.object_id
WHERE 
    definition LIKE '%(INDEX=%';

but as the definition column is sometimes too long to read I would like to select from that column just, let's say, 30 characters before and 30 characters after the WITH (INDEX=.

enter image description here

How to select just that part of the string?

Basically in that column I just would like to see:

...INNER JOIN tblSession WITH (INDEX=indRealDateTime) ON ...

which is the part of the query I'm interested in. I want to see at a glance how painful is that index hint.

Solution

  • Demo Example,

    declare @i varchar(500)='afgdfgdfgdfg dgfdgdfg dfgdfgdfg dfgdfgdfg cvxfsdfsdfdf erwererwer (index fgfdgdf weqweqweqwe dsadsads sfsfsdfd erewwerwer 6786787 35345dfsdfgsdfsdf sdfdsfsdfdf'

DECLARE @margin INT = 15 --try 15000

SELECT SUBSTRING(@i, charindex('index', @i) - @margin, charindex('index', @i) + @margin)