Q&A

Q Why does the server need to keep multiple plans in memory for a single procedure?

A Each execution of a procedure needs a private execution space. That’s where session information (temporary table names, local variable values, cursor information, and global variables) is stored, and it’s where the procedure actually runs. In order to allow multiple users to execute the same procedure concurrently, SQL Server needs to load multiple versions of the procedure.

Q Do I need to worry about exactly matching the data types of parameters to the data types of referenced columns?

A Absolutely. When you are planning to pass a stored proc parameter to a WHERE clause, you should make certain that the parameter matches in both data type and length. (Don’t worry about nullability: all parameters and variables allow null values.) Take the time to look up the data type using the Enterprise Manager or sp_help. If you fail to pass the proper data type, SQL Server may be forced to guess about optimization, and that’s never a good thing.

Q I thought that variables in the WHERE clause created an optimization problem. How do I avoid variables in my queries within stored procedures? And what is the point of using procedures if I can’t use variables??

A On Day 15, we said that a query containing a local variable in the WHERE clause would use the magic or guessing method of optimization, rather than referring to the statistics page. For example, in this batch, SQL Server does not know the value of the variable at optimization time, so it cannot look up its value and determine the selectivity of the WHERE clause:

declare @start datetime, @end datetime
select @start = “1/1/97”, @end = “12/31/97”
select pubdate, price
from titles
where  pubdate  between  @start  and  @end  --  optimizer  must
guess  with local vars

If you perform the same query in a procedure where the start and end dates are parameters, SQL Server knows the values of the parameters at optimization time, so it substitutes them in the query to develop an optimization plan:

    (@start datetime, @end datetime)
 as
 select pubdate, price
 from titles
 where pubdate between @start and @end -- optimizer can look up
parameter vals
return

Procedures solve the optimization problem by knowing the parameter values at optimization time and using them to determine the selectivity of the WHERE clause.

Q How do I guarantee the performance of an update procedure?

A Updates are tricky. The problem is that you don’t want to update all columns in the procedure if only one column is changing. Consider this example:

create proc pr_x_update
(@id int, @col1 int, @col2 int)
as
update x
 set    col1 = @col1,
        col2 = @col2
 where  id = @id
 return

The procedure works fine, but may perform horribly. The problem concerns the style of update. Certain changes to a row cause SQL Server to perform a complete overhaul of the row (a direct or deferred update), whereas other changes only cause a modification of the changed column values (a direct update in place). The difference in performance can be dramatic.
SQL Server decides what form of update to use at optimization time from your syntax and from what it knows about the table. If the UPDATE statement in the stored procedure modifies every column (even if the value hasn’t changed), update performance may suffer. There are lots of workarounds, including coding every possible variety of UPDATE statement in your procedure and running the one that corresponds with the modified variables. A more modest approach would be to modify sets of related data, especially in tables with many columns.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you’ve learned. Try to understand the quiz and exercise answers before continuing on to the next day’s lesson. Answers are provided in the appendix.

Quiz

1.  When could a stored procedure cause serious performance problems? Can you give an example?

2.  When is on-the-fly execution useful?

Exercises

1.  This procedure is taking forever to execute. Can you fix it?

create proc dollar_sales_by_state
    (@start_date smalldatetime, @end_date smalldatetime)
 as
 select t.title_id, qty, price, stor_id
   into #temp
   from titles t, sales s
  where ord_date between @start_date and @end_date
    and t.title_id = s.title_id
 select state, sum(qty * price)
   from stores s, #temp t
  where s.stor_id *= t.stor_id
 group by state
 return 0

2.  You need to grant select permission for every table and view in the pubs database to the user joseph, but the script needs to be self-modifying so that tomorrow it will add permissions for new tables and views. How would you write it?