Fun With sys.sql_modules in SQL Server 2005

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

 

This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s