Inside SQL Server: Stored Procedure Plans

Rating:

(0)

Kalen Delaney

SQL Server Magazine

InstantDoc ID #6113

Improve performance through caching

In SQL Server 7.0, reusing compiled plans for stored procedures can make a dramatic difference in overall system performance. (For more information on plan reuse, see "SQL Server 7.0 Plan Caching," September 1999.) The greater complexity of the Query Optimizer saves parse-and-compile time (which includes query optimization). But reuse of compiled plans isn't always the most important reason for using stored procedures. With earlier SQL Server releases, the savings aren't great, and SQL Server 7.0 includes other methods of saving and reusing query plans. So why should you use stored procedures?

Sto...

...This article is for paid Professional Members only.

Already a Professional Member? Please log in now:

Professional Membership

Monthly

Annual

VIP Membership

Monthly

Annual

Compare Member Benefits

Add a Comment

Greetings,

When I try:

SELECT sql, cacheobjtype , objtype, object_name(objid) nam, uid, refcounts, usecounts
FROM master..syscacheobjects
WHERE objtype = 'Proc'
ORDER BY usecounts DESC

I see different values in sql and nam cols in single row.
Why? What is right one?

martin rakhmanoff 11/13/2001 3:11:40 AM

This article uncovers only a part of the problems related to SP recompiling. I experience actually trigger recompile at each run on an insert statement (insert trigger). The keep plan option is not an option for insert statement, the IF EXISTS (select*.. doesn't allow to specify keep plan either).

The low thresold recompilation for a table as set by the query processor for an empty table seems to me inadequate, because changing the access plan is only meaningful if it is of value to change the access plan. It is crazy to recompile a SP or a trigger only for 5 insert, will it really improve the performance for these few rows ?

I think this is a design problem of the query processor.

Maurice Pelchat 6/5/2000 9:12:12 AM

You must log on before posting a comment.

Are you a new visitor? Register Here