I’d forgotten about this “issue” until I read on the ASP.NET forum someone else having the same problem. I can’t vouch for what happened to the that user but I can explain what I saw. Basically I had a very simple stored procedure that took an Id value in as a param and then happily used it with a query, something like (not the actual query for customer IP reasons):
The proc worked fine until the database data was scaled-up. Suddenly it performed very poorly and no amount of statistics updates would fix it. So I went through the usual steps of breaking the inner query out of the procedure and hard-coding the values:
Select Col1, Col2 from MyTable Where Col3=3
It worked great, so I started to add bits back until it went wrong again:
Worked great…
Create Proc MyProc(@Id int)
As
Declare @MyLocalId int
Set @MyLocalId = 3
Select Col1, Col2 from MyTable Where Col3=@MyLocalId
Create Proc MyProc(@Id int)
As
Declare @MyLocalId int
Set @MyLocalId = 3
Select Col1, Col2 from MyTable Where Col3=@MyLocalId
Worked great…starting to get concerned…
Create Proc MyProc(@Id int)
As
Declare @MyLocalId int
Set @MyLocalId = 3
Select Col1, Col2 from MyTable Where Col3=@Id
Create Proc MyProc(@Id int)
As
Declare @MyLocalId int
Set @MyLocalId = 3
Select Col1, Col2 from MyTable Where Col3=@Id
Poor again…hmm…
Create Proc MyProc(@Id int)
As
Declare @MyLocalId int
Set @MyLocalId = @Id
Select Col1, Col2 from MyTable Where Col3=@MyLocalId
Create Proc MyProc(@Id int)
As
Declare @MyLocalId int
Set @MyLocalId = @Id
Select Col1, Col2 from MyTable Where Col3=@MyLocalId
Worked great!!!
What just happened? All I’ve done is de-reference the param into a local variable and used that. It worked and since I was under a lot of pressure at the time I forgot all about it. However, since reading that at least one other person is seeing the same issue I’ve decided to dig a little deeper. My first clue came from TechNet that stated that by using a local variable the query wouldn’t use statistics, now I’m not sure about that but maybe that’s something do with it. The reason that is plausible is because at low data volumes the procedure worked fine but with different statistics it started to perform badly. Although uncommon it’s not impossible for the Query Optimizer to get confused and produce a poor plan. If you remove its ability to use statistics it falls back to a sort of worst-case algorithm. In this situation it maybe that the default worst-case plan is better that its optimized attempt, in SQL 2005 you could then force the “correct” plan. But it’s still a theory at the moment, but I’ve dug out a Kimberly Tripp webcast on how params work so hopefully that may shed some light on the matter. More posts soon, in the meantime if anyone else has any ideas then please post.
What just happened? All I’ve done is de-reference the param into a local variable and used that. It worked and since I was under a lot of pressure at the time I forgot all about it. However, since reading that at least one other person is seeing the same issue I’ve decided to dig a little deeper. My first clue came from TechNet that stated that by using a local variable the query wouldn’t use statistics, now I’m not sure about that but maybe that’s something do with it. The reason that is plausible is because at low data volumes the procedure worked fine but with different statistics it started to perform badly. Although uncommon it’s not impossible for the Query Optimizer to get confused and produce a poor plan. If you remove its ability to use statistics it falls back to a sort of worst-case algorithm. In this situation it maybe that the default worst-case plan is better that its optimized attempt, in SQL 2005 you could then force the “correct” plan. But it’s still a theory at the moment, but I’ve dug out a Kimberly Tripp webcast on how params work so hopefully that may shed some light on the matter. More posts soon, in the meantime if anyone else has any ideas then please post.
IT Bytes 