SQL Server Performance

Views v Stored Procedures

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by Tricky, Feb 23, 2009.

  1. Tricky New Member

    Hello,
    We are in the process of familiarising ourselves with SQL 2008 having spent a number of years using 2000. My previous understanding was that amongst other considerations, you may choose a stored procedure over a view as stored procedures are more efficient, due to caching execution plans etc. Is this still the case in 2008?
    Thanks in advance for your advice.
  2. FrankKalis Moderator

    [quote user="Tricky"]
    We are in the process of familiarising ourselves with SQL 2008 having spent a number of years using 2000. My previous understanding was that amongst other considerations, you may choose a stored procedure over a view as stored procedures are more efficient, due to caching execution plans etc. Is this still the case in 2008?
    [/quote]
    First of all I think you really can not compare procedures with views just in terms of caching an execution plan. SQL Server is getting better and better at ad-hoc querying with every version. So this argument alone may not hold true any longer. but there are other, far more important reasons, to favour a procedure over a view
  3. Adriaan New Member

    It sounds like perhaps you want the view to pre-filter data, or to return calculated values on the results. As a rule-of-the-thumb, you should avoid doing that in views, at the risk of poor performance. (Queries against such a view may require the view to return the complete results first.)
  4. Atesim New Member

    Hi Tricky,
    There are many aspects to stored procedures which make them very useful and performance enhancing. However, the same is also true with views. Personally, I consider them distinct- but I also think it really depends on what you're doing with them or need them to do. If you could provide a little more feedback perhaps we can tell you a little more.

    Generally, the two are used for different purposes though their use can emulate (or be confused with?) the other. For example, you can use a stored procedure to return a result set. If all the procedure is doing is executing a query that performs joins and filters data, then a view does the same thing. You can even index views! (provided you bind the schema). SQL server will now even use indexes defined on these views if they can be used to satisfy the needs of queries for tables/views which don't have the appropriate indexes applied to them. Naturally, there are performance implications and pros/cons for everything so knowing more about what you need/want to do can help us provide you with more than general conjecture and opinion :D
    Cheers!
  5. Tricky New Member

    Hi Atesim et al,I have used sp's for a long time so am relatively familiar in some respects with the power of SP's. To clarify my "query", I am really wondering specifically about any performance benefits of one over the other, under simple scenarios where each could otherwise achieve the same functionality. Take a simple select query that you wanted to have stored in the db example. I have long been under the impression that SP's were able to use cached execution plans in ways that views couldn't and were often favoured for this reason.
  6. satya Moderator

    I would say it all depends on how frequently that query is executed, the number of rows on underlying table. For the generic purpose it is ok to swith over from SP and execute the query as an adhoc process, in any case there is always an advantage for SP to execute in optimized way for the DB engine architecture.
  7. Expansion New Member

    An important point may be, if the query produces alternating big and small resultsets. If it is true that a stored procedure keeps more to the compiled execution plan than a view does, it might favor the use of views or adhoc queries.
    Apart from tips about avoiding parameter sniffing, I don't read much about this subject. It is also difficult to test.
    To get an idea, you could perform tests in the following sequence:
    - clear the buffers and proccache (dbcc dropcleanbuffers; dbcc freeproccache;)
    - perform the query so it gives a big resultset
    - repeat it, but with criteria that produce a small resultset
    - clear the buffers and proccache (dbcc dropcleanbuffers; dbcc freeproccache;)
    - perform the query so it gives a small resultset
    - repeat it, but with criteria that produce a big number of rows

    You need to do the test both for a stored procedure and a view that produce the same results.
    I myself didn't try it like that (so far).
  8. satya Moderator

    Good thoughts, but only addition is never attempt to DROP CLEANBUFFERS on production system causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance..
    Also you can use CHECKPOINT to produce a cold buffer cache. This forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.
  9. ghemant Moderator

    In addition to what Satya says we can now remove specific plan from the cache using dbcc freeproccache ('plan_handle')
  10. satya Moderator

    Hemant
    I believe it would help if we add that how to find the plan_handle, see below:
    SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject, omo.memory_object_address, pages_allocated_count, type, page_size_in_bytes FROM sys.dm_exec_cached_plans AS ecp JOIN sys.dm_os_memory_objects AS omo ON ecp.memory_object_address = omo.memory_object_address OR ecp.memory_object_address = omo.parent_addressWHERE cacheobjtype = 'Compiled Plan';GO

Share This Page