You can use profiler to monitor yourself. Though #t2 has no alter table against it, the insert statement involving #t2 will also recompile in addition to the insert on #t1. Restarting SQL Server will also result in all procedures being automatically recompiled.
#Sql server recompile stored procedure how to#
Though this post discusses how to manually recompile a stored procedure, SQL will also automatically recompile procedures whenever a table referenced by the stored procedure has undergone a physical design change. The stored procedure below (proc_test) has two temp tables (#t1 and #t2). From time to time this map can become outdated. In the case of temp table, if you have one alter statement on any of the temp tables, statements involving all tables will recompile. It turned out that this customer had some alter table statement on the temp table. To change the schema owner from Sql Server Management Studio: Expand your database. Select Permissions Across Multiple Schemas Within the Same Database. SQL Server: grant select access to a user in a view and not in its tables. That is what this customer complained about. Security : SQL Server Object Access From One Schema to another schema. Option 2: Open the stored procedures folder in SSMS (in the object explorer details window) We will not use parameters autosynchronisation here Whenever an SQL statement is executed in SQL Server, the relational engine first looks at the procedure cache to check that an existing execution plan for that exec dbo - Example for SQL Stored. What is not expected is that you see the same query being recompiled again and again. So you will see the recompile at least once for a query involving temp table. We wait until first time the query is executed. When the procedure is compiled, the query involving temp table is not even compiled. This is a feature called deferred compile. If you have a query involving temp table inside a stored procedure, you will always see SQL:StmtRecompile for that query first time you run the procedure. Now that we know how to monitor recompiles, let’s talk about what’s expected. You will see “SQL:StmtRecompile” event as shown below if that statement gets recompiled. When you choose event, make sure you choose “SQL:StmtRecompile” event (see below). You can use profiler to watch recompiles. In other words, one statement recompiling won’t cause the entire stored procedure to recompile.
Starting SQL Server 2005, recompile only occurs at statement level.
After digging deeper, we were able to track down the problem.īefore we discuss the root cause, let’s talk about how you can track down stored procedure recompiles. But none of the conditions seemed to match. Which documents various scenarios that will cause recompile involving temp tables. Lately we got a customer who called in and reported that a particular statement involving a temp table always got recompiled and caused performance problems.
0 kommentar(er)
