Last post Jan 22, 2020 02:23 AM by Yongqing Yu
Jan 21, 2020 08:11 AM|Ch.Hashmat.Khan|LINK
We have face some issue as our enterprise application performance get really poor/slow and increase latency time, when investigating we found that there is multiple execution plan of a stored procedure that cause this, how to avoid this issue, for right
now restarting SQL Server / Machine work back to normal.
Jan 21, 2020 08:48 AM|Khuram.Shahzad|LINK
sp_recompile will dump the existing query plan and recompile the procedure. Or you can restart SQL and that will clear the entire execution plan cache.
WITH RECOMPILE is going to generate a new plan EVERY time you execute it.
IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.uspProductByVendor;
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'
SET NOCOUNT ON;
SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
FROM Purchasing.Vendor AS v
JOIN Purchasing.ProductVendor AS pv
ON v.BusinessEntityID = pv.BusinessEntityID
JOIN Production.Product AS p
ON pv.ProductID = p.ProductID
WHERE v.Name LIKE @Name;
Jan 22, 2020 02:23 AM|Yongqing Yu|LINK
According to your description, this is usually because stored procedures cause slower performance.
To speed up the response of your program, you have multiple ways to try to modify the stored procedure:
Try using using the hint OPTIMIZE FOR UNKNOWN. If it works, this may be better than forcing a recompile every time.
At the end of your query, add
OPTION (OPTIMIZE FOR (@now UNKNOWN))
You can refer to this link : https://stackoverflow.com/a/24016676
Or you can try other ways in the following link:
SQL Server: Query fast, but slow from procedure