I have noticed that after inserting many rows in one table many times the stored procedure on that table executes slower or degrades. This happens quite often after BCP or DTS. I prefer to recompile all the stored procedure on the table, which has faced mass insert or update. sp_recompiles marks stored procedures to recompile when they execute next time.
Example:
----Following script will recompile all the stored procedure on table Sales.Customer in AdventureWorks database.
USE AdventureWorks;
GO
EXEC sp_recompile N'Sales.Customer';
GO
----Following script will recompile specific stored procedure uspGetBillOfMaterials only.
USE AdventureWorks;
GO
EXEC sp_recompile 'uspGetBillOfMaterials';
GOWant to try this yourself? Here is how to install AdventureWorks.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
6 Comments. Leave new
Hi,
could you please tell me how to find the sps which are marked for recompile.
Thanks in advance
Here is a quick way:
SELECT NAME
FROM sysobjects
WHERE type = ‘P’ AND uid = 1
ORDER BY Name
I’m trying to find out if I can use any of the following to select between one of two CTEs in a master data set.
Which one will work best.
IIF()
Switch()
CASE()
Code starts out with With CTE### as
(
select (fields)
from (
select (fields)
from
where (filters)
groub by (groups)
)Pull
Pivot
(Sum()
for month in
(1,2,3,….) as Pvt###
Group by ()
Union all
select (fields)
from (
select (fields)
from
where (filters)
groub by (groups)
)Pull
Pivot
(Sum()
for month in
(1,2,3,….) as Pvt###
Group by ()
)
Select ()
from CTE###
group by ()
Order by ()
__________
thanks
Dear sir ,
in one company i faced interview for T-SQL developer
in there technical person asked me what is recompilation
of store procedure ? i m not able to give answer so please
let me know answer of this question
regards
sharad
Hello Sarad,
Please visit my blog:
http://blog.sqlauthority.com/2010/02/18/sql-server-plan-recompilation-and-reduce-recompilation-performance-tuning/
Regards,
Pinal Dave
The function or expression “like” in a query from .Net SqlClient Data Provider in [usf] submitted from 15814-68893 by utsav can cause index suppression resulting in poor performance due to a scan being performed instead of a seek.