August 28, 2002 at 4:39 pm
As a general question to the group, have any of you had experiences with, for lack of a better term, "quirky" behavior in the estimated execution plans provided by the query analyzer?
To be more specific and perhaps more technical, I came across a situation recently while tuning a long-running query in a stored procedure where the estimated execution plan would change based on the actual value of a hard-coded parameter in the WHERE clause. The query was fairly complex and involved a number of joins and the column referenced by the parameter was indexed and produced a highly efficient execution plan utilizing indexes almost exclusively except when set to a particular value (ex. WHERE EmployeeID=0). When hard coded to be equal to 0, the execution plan changed to a nasty series of cascading hash joins and table scans and the execution time would more than double. Now it would seem odd to me for the plan to change merely based on the value of a parameter.
I ended up solving the problem by witholding the value of the parameter from the execution plan. I did this by simply declaring a variable (ie. DECLARE @EmployeeID int) and setting the variable equal to 0 and then having the clause read like so: WHERE EmployeeID=@EmployeeID. When given this, the execution plan for the procedure went back to the nice set of indexes and a much more reasonable execution time. The value of the parameter is exactly the same but the execution plan changes. The more efficient execution plan is discarded based on a submitted value but, when forced to use the original plan for the value in question, the more efficient plan works just fine for said value.
Now, for the most part, I have found the execution plans specified by the server to be excellent but I wonder if there are other such unusual behaviors that others have found in their query tuning adventures.
August 28, 2002 at 5:23 pm
If you have an index on the EmployeeID it may have had the highest density of unique values making it a stronger candidate for index use, use sp_statistics to look at this. I am not sure how you ran your other queries or if it was just setting it to 0 that set it off. One thing to do thou is periodically run sp_updatestats especially if a large table. Also use DBCC UPDATEUSAGE to care for internal sysindexes problems. It could be a simple maintainence problem if you don't already handle this. Finally, if you want to post some details of the table along with the execution plan you want and the one you got we can take a look to see if anything specific sticks out to us.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply