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';
GOReference : Pinal Dave (https://blog.sqlauthority.com), BOL
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
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.