When to optimize stored procedures to avoid recompiles?
One of the most overlooked areas in optimizing SQL Server and Transact-SQL is the recompilations of stored procedures. Recently started looking at this issue where I worked and stored procedures gathered from other clients and I noticed that there is an issue with SQL developers not taking recompiles into account when they create new code. When
Which is the stored procedure in SQL Server?
Use the sp_executesql stored procedure instead of the EXECUTE statement. The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code.
Do you use the prefix SP _ in stored procedure name?
Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database.
How to reduce network traffic with stored procedures?
Include the SET NOCOUNT ON statement in your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement. This can reduce network traffic due to the fact that your client will not receive the message indicating the number of rows affected by a Transact-SQL statement. 3.
How to recompile the stored Proc in SQL?
With Recompile will recompile a new plan for the entire stored proc ,everytime its run..
Can a SQL profiler tell if a stored procedure recompiles?
With the use of SQL Profiler it is very easy for a DBA or developer to determine if a stored procedure abnormally recompiles before it is ever put into a test or production environment. It is also very easy for production DBAs to determine if they have a problem with stored procedures already in production.
Is there a way to avoid SQL Server recompile?
There are several ways to avoid this specific recompile. Luckily you have two paths to take: using sp_executesql and using the KEEPFIXED PLAN query hint that is found in SQL Server 7.0 SP3 and SQL Server 2000.