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
| |
|
|