April 7, 2005 at 9:03 am
Wanted your thoughts on this: My counterpart SQL Server DBA is tuning a poor performing SP. The SP is called and is passed a fixed set of variables (12) which may or not be populated w/ any combination (eg. Vars 1,3,5,7 are populated on invocation #1, Vars 2,4,6,8 are populated on invocation #2, etc.)
The DBA added an auto-recompile command forcing the recompilation of this SP prior to each and every execution. My take on this is that SQL Server cannot dynamically interpret "which" variables are being populated therefore SQL Server cannot optimize it's access path based on the varying populated variables. SQL Server's optimizer will interrogate the variable names (not the variable's contents) and determine access paths based on predefined indexes available to satisfy the query. Recompiling will do nothing unless the data volumes (and associated index volumes) expand and reduce substantially.
Am I off base here? (technical replies and links are greatly appreciated)
(my rationale is based on my 15 yrs experience as a DB2 DBA. Once having a similar performance issue w/ an online application allowing a user to enter any combination of 16 variable screen values including Last Name, First Name, City, State, ZIP, Country, etc -- Our solution was to have the application interrogate the values entered - and brach to 1 of 8 different precomiled SQL querys -- eg. If only the ZIP was entered, the application would invoke Query # 7 - which used Zip in the predicate and was bound to the Zip index)
April 7, 2005 at 3:11 pm
SQL Server does perform "parameter sniffing" whereby the values of the parameters are passed to the query optimizer to generate the execution plan. Without the recompile parameter, the stored procedure execution plan created based on the first set of parameter values is used for all subsequent executions. As this may be sub-optimal for a different set of parameter values, specifying with recompile is a common technique.
There is very little documented reqarding "parameter sniffing"
SQL = Scarcely Qualifies as a Language
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply