WHERE so.type = 'V' AND sed.referenced_id = OBJECT_ID('Person. INNER JOIN sys.sql_expression_dependencies AS sed SELECT DISTINCT 'EXEC sp_refreshview ''' + name + '''' The following example creates a script that refreshes the metadata for all views that have a dependency on table Person.Person. Creating a script that updates all views that have dependencies on a changed objectĪssume that the table Person.Person was changed in a way that would affect the definition of any views that are created on it. USE AdventureWorks2012 ĮXECUTE sp_refreshview N'Sales.vIndividualCustomer' ī. The following example refreshes the metadata for the view Sales.vIndividualCustomer. Requires ALTER permission on the view and REFERENCES permission on common language runtime (CLR) user-defined types and XML schema collections that are referenced by the view columns. Otherwise, the view might produce unexpected results when it is queried. If a view is not created with schemabinding, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view.
#Sql server recompile view code
Return Code ValuesĠ (success) or a nonzero number (failure) Remarks viewname can be a multipart identifier, but can only refer to views in the current database. Transact-SQL syntax conventions Syntax sp_refreshview 'viewname'Īrguments the name of the view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends. Updates the metadata for the specified non-schema-bound view. Summary of the downsidesĮach statement with option recompile consumes extra cpu and memory and doesn’t store the execution plan in cache, preventing performance tuners from seeing metrics like total execution count, or total worker time in dm_exec_query_stats.Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SET STATISTICS TIME ON Įvery time that statement runs, it will be compiled again, costing those 5 ms of CPU time. JOIN Users as U2 on Posts.LastEditorUserId = U2.Id JOIN Users as U on Posts.OwnerUserId = U.Id To see the cost of compilation, just use statistics time. Okay, but what about the cost of compilation? in Microsoft SQL Server enables you to change view columns. To prove that, I’ll right-click on the top SELECT INTO and view the properties. You don't actually have to recompile Oracle views invalidated when their underlying tables. exec 4Įach “query” in this example is a separate statement. Then, I’ll get the actual execution plan for post type 4. I’ll run the stored procedure with the value of 3, first. This stored procedure has OPTION(RECOMPILE) on only one statement INT) asįROM Posts where PostTypeId = PostTypeId, Tags, Body Except for this example, I can’t use the estimated execution plan because there’s a temp table. I’m going to re-use the stored procedure from this post on parameter sniffing and using the estimated execution plan. There’s also the fact that the hint applies strictly to the statement level, not the entire query. Using option recompile will use extra cpu and memory every time the statement compiles.
I have a few reasons why this hint is dangerous. In fact, the statement with option recompile won’t be stored in cach e. It also means that the statement itself won’t be vulnerable to parameter sniffing from other queries in cache. There’s some benefits, like something called “ constant folding.” To us, that just means that the execution plan might be better than a normal execution plan compiled for the current statement. SQL Server will compile an execution plan specifically for the statement that the query hint is on.
What happens when a statement has OPTION(RECOMPILE) I’ve used it before without fully understanding the impact. I wish I knew that when I started query tuning.
0 kommentar(er)
