Sunday, September 14, 2008

SQL Server Views and Performance

Someone asked me recently how do views affect performance in SQL Server, since they don't get a compiled execution plan like a stored procedure?  In investigating this, I was at first surprised to find that the person asking was absolutely correct, there is no compiled plan for a view in the dmv's:

select usecounts,cacheobjtype,objtype,query.text,executionplan.query_plan 
from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle) as query
cross apply sys.dm_exec_query_plan(plan_handle) as executionplan
where text not like '%sys%'and cacheobjtype ='compiled plan'



If you run the above, you will find that Views get a Parse Tree instead.  So what does this mean exactly in the context of performance and using a view?  The answer is not really all that simple.  The Parse Tree defines the expansion of the view definition for execution time.  While the actual view doesn't have a compiled plan, the queries that use the view do.  You can test this by clearing the procedure cache:




DBCC FREEPROCCACHE



and then running a query, twice with different parameter values, with a view in your database:




SELECT * 
FROM [dbo].[SampleView]
WHERE OrderID = 10248

SELECT *
FROM [dbo].[SampleView]
WHERE OrderID = 10250




If you look at the cache now, you should see that this query was parameterized by the SQL Server, and stored in the plan cache for reuse.  This means that your index tuning for a system that heavily uses Views that join multiple tables, must be based, not on the view definition itself but instead on the code that utilizes the view.

No comments:

Post a Comment