Yukon SP performance issue

Yukon SP performance issue

• FundamentalDiscord

  In general, static values in a query give the query optimizer more information to use when generating a query plan.  Since SQL Server keeps distribution histograms for columns, it can determine if a particular value is more frequent than others.  This can allow the optimizer to find a plan that is particularly efficient for a specific value.
  
  As a simple example, if you run a query like:
  SELECT * FROM Table WHERE col = @p
  
  Depending on the indexes created on the table, a number of execution plans could be considered.  We could scan the whole table or perhaps, as an alternative, we couldseek into a secondary index for rows that qualify the predicate, then retrieve the remaining columns from the base table.  The relative advantage of these two plans is based on how many values the predicate would return, the width of the base table (which implies how many rows fit on a page and thus how many I/Os we may need to do to retrieve the data for the query), how much memory and how many CPUs exist, etc.
  
  If the optimizer does not know the intended or common value for @p when the query is compiled, it will take a guess about what an "average" execution would return.  This may not work well, in practice, if the distribution of values in the table is far from uniform.  Note that the new SQL Server 2005 product will have a feature to allow you to hint which parameter would be the "common case" when compiling such a query.
  
  The example I've shown here is a simple "scalar" comparison where a single column in a table is used.  Things are roughly the same when the concept is applied to "table parameters".  SQL Server 2000's T-SQL table variables do not have enough statistical information to properly determine the distribution of column values when making choices between different plans.  As a result, a common-case estimate is used which may or may not be appropriate for your query.  If the query is cheap enough, it may not matter enough to make a difference.  However, if your query is noticably slower, it can matter tremendously.
  
  In general, care must be used when using parameter to make sure that enough information is given to the query optimizer to generate plans that operate efficiently for your scearnio.
  
  An example snippet of T-SQL is useful to help others narrow down which exact problem you are seeing.  If you can post more information about your specific example, we can perhaps help you more specifically.  In general, not all uses of parameters, table variables, or Table-Valued Functions result in poorly performing plans.  However, it can happen.
  
  I hope this helps explain why it might matter to your plan performance.
  
  Conor Cunningham
  SQL Server Query Optimization Development Lead
  

• Dave Parker

  Thanks for the suggestion Clifford. A few questions before you drop me though...
  
  What is a 'repro' Do you mean a reproduceable example It turns out that just about any example will work: use of variables appears to dramatically reduce performance relative to static values. I recognise some rationale for this but not to account for 16s vs 0.1s.
  
  Are you aware of that kind of performance difference in previous versions of SQL Server
  
  Thanks,
  
  Rod O.
  
  

• Decoy830

  In general, using local variables in a query condition causes the optimizer to guess selectivity rather than use accurate estimates. This can cause poor plan selection. Using literals instead of variables will give you better estimates and thus possibly a better query plan. Try to use parameters instead of local varables in the query. If that still doesn't work, consider using dynamic SQL so that the actual literals appear in the query.
  
  Eric
  
  

• mysternem0

  It is difficult to say without more information.   Perhaps you should create a repro, collect the query plan, and log a bug on Betaplace.   I think you are more likely to get a timely and accurate response that way.
  
  Regards,
  Clifford Dibble

  
  
  

• Michael Ragsdale

  Hey all,
  
  One correction. The code is in a table-valued function and not a stored procedured.
  
  Rod O.
  
  
• Yukon SP performance issue