If you ever need to look for some specific text within any of your stored procedures, you can use the first query shown below. You can look for any stored procedure that has WITH RECOMPILE set (at the procedure level) by running the second query.  Using WITH RECOMPILE causes the entire SP to be recompiled each time it is executed, which can be CPU intensive, but can also solve problems caused by "parameter sniffing".

If you have a stored procedure that uses very different query plans based on the input parameter, yet is not called too frequently, using WITH RECOMPILE is a good option.

— Look for text in a stored procedure
    SELECT object_id, definition, is_recompiled
    FROM sys.sql_modules
    WHERE definition LIKE ‘%GetNewsForFeed%’

— Find stored procedures that have WITH RECOMPILE set
    SELECT object_id, definition, is_recompiled
    FROM sys.sql_modules
    WHERE is_recompiled = 1