I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server: Automated database object recompiling after cleaning plan cache with FMTONLY

DBCC FREEPROCCACHE; and DBCC DROPCLEANBUFFERS; are commands that clean the cached execution plans from memory.

The consequence of running those statements is that all your objects (sprocs, functions, views) must be recompiled which can be quite costly.

There is no way around that. And if you want to recompile 100 sprocs you have to execute each one and each one returns data.

I didn't want this so i created this script that uses a not well know SET option called

SET FMTONLY ON

which returns only schema and no data to the client. Since returning data can take a long time this thing is pretty fast.

 

This script clears the plan cache and recompiles every stored procedure, function and view in the database and returns only schema and no data.

If nothing else it's a one time process that can be automated. All parameters are null, so if you want to optimize plans for special

parameter values you'll have to do it by hand.

Run this, copy/paste the results into a new query window and run it:

 

SELECT  'DBCC FREEPROCCACHE; DBCC DROPCLEANBUFFERS;' AS statements 
UNION ALL
SELECT  'EXEC(''SET FMTONLY ON; ' + 
        CASE 
            WHEN routine_type = 'scalar_fn' THEN
                 'SELECT ' + QUOTENAME(obj_schema) + '.' + 
                 QUOTENAME(obj_name) + ' (' +  LEFT(params, LEN(params)-1) + ')'
            WHEN routine_type = 'table_fn' THEN
                 'SELECT * FROM ' + QUOTENAME(obj_schema) + '.' + 
                 QUOTENAME(obj_name) + ' (' +  LEFT(params, LEN(params)-1) + ')'
            WHEN routine_type = 'procedure' THEN
                 'EXEC ' + QUOTENAME(obj_schema) + '.' + 
                 QUOTENAME(obj_name) + ' ' + LEFT(params, LEN(params)-1)
            WHEN routine_type = 'view' THEN
                 'SELECT * FROM ' + QUOTENAME(obj_schema) + '.' + QUOTENAME(obj_name)
        END + '; SET FMTONLY OFF; '')' AS statements 
FROM 
(
    SELECT  routine_type, obj_schema, obj_name, RTRIM(REPLICATE('NULL, ', COUNT(*))) AS params
    FROM    
    (
        SELECT  r.specific_schema AS obj_schema, r.specific_name AS obj_name, 'scalar_fn' AS routine_type
        FROM    INFORMATION_SCHEMA.ROUTINES r
                JOIN INFORMATION_SCHEMA.PARAMETERS p ON r.specific_schema = p.specific_schema
                                                              AND r.specific_name = p.specific_name
        WHERE   routine_type = 'FUNCTION' 
                AND parameter_mode = 'IN'
                AND routine_body = 'SQL'
                AND r.data_type <> 'TABLE'
        UNION ALL
        SELECT  r.specific_schema AS obj_schema, r.specific_name AS obj_name, 'procedure' AS routine_type
        FROM    INFORMATION_SCHEMA.ROUTINES r
                JOIN INFORMATION_SCHEMA.PARAMETERS p ON r.specific_schema = p.specific_schema
                                                              AND r.specific_name = p.specific_name
        WHERE   routine_type = 'PROCEDURE'         
               AND routine_body = 'SQL'
        UNION ALL
        SELECT  r.specific_schema AS obj_schema, r.specific_name AS obj_name, 'table_fn' AS routine_type
        FROM    INFORMATION_SCHEMA.ROUTINES r
                JOIN INFORMATION_SCHEMA.PARAMETERS p ON r.specific_schema = p.specific_schema
                                                              AND r.specific_name = p.specific_name
        WHERE   routine_type = 'FUNCTION' 
                AND parameter_mode = 'IN'
                AND routine_body = 'SQL'
                AND r.data_type = 'TABLE'
        UNION ALL
        SELECT  table_schema AS obj_schema, table_name AS obj_name, 'view' AS routine_type
        FROM    INFORMATION_SCHEMA.VIEWS
    ) t1
    GROUP BY obj_schema, obj_name, routine_type
) t2

 

kick it on DotNetKicks.com
 

Print | posted on Thursday, August 02, 2007 5:21 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server: Automated database object recompiling after cleaning plan cache with FMTONLY

This is another *poor* solution. You want to compile most of your statements with typical values or the plans you get will be totally *wrong*

8/6/2007 3:57 PM | noeld
Gravatar

# re: SQL Server: Automated database object recompiling after cleaning plan cache with FMTONLY

maybe you should read what i said about parameters in the post.
and again do you have a better solution?
8/6/2007 3:59 PM | Mladen
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET