Today’s day in SQL, volume I

Ah… another day spent fighting the SQL Server 2005 Query Optimizer. I truly have no idea why this sucker shipped (after 5 years of development, no less) with this insane amount of regression, and random regression at that. Don’t get me wrong, I dearly love SQL Server 2005, overly fat and slow front-end tools and all. But come on, you really have to hold this puppy by the hand sometimes.

Too vague? Okay, an example.

Create Procedure SelectSomething
   @Parameter1 Integer
As
Begin

   Select
     …
   From
     dbo.SomeTable As SomeTable
     Inner Join [A Random Bunch Of Other Tables]
       …
     Inner Join dbo.LastTable As LastTable
       …
   Where
     (LastTable.SomeField = @Parameter1)

End

In some cases, 2005 decides to fully join all tables (sometimes even lazy-spooling the entire shebang) before applying the condition. Yes, there are indexes on the table. Yes, the statistics are up to date. And don’t tell me it can’t be done, because 2005 does get it right sometimes and 2000 got it right almost all of the time. And sometimes it makes the right decision for ages, then decides to change its mind on the plan. So what is the fix? It cannot be as simple as

Create Procedure SelectSomething
   @Parameter1 Integer
As
Begin

   Select
     …
   From
     dbo.LastTable As LastTable
     Inner Join [A Random Bunch Of Other Tables]
       …
     dbo.SomeTable As SomeTable
       …
   Where
     (LastTable.SomeField = @Parameter1)

End

Can it? Yes it can, it is, and this fixes this category of problems all of the time. Now that is just silly. It’s absolute monkey-work to fix these things, and I feel I shouldn’t have to.

Still, those are the easy ones. Many, many, many stored procedures we write here have the form

Create Procedure SelectSomething
   @OptionalParameter1 Integer = Null,
   @OptionalParameter2 Integer = Null,
   …
As
Begin

   Select
     …
   From
     dbo.SomeTable As SomeTable
     Inner Join dbo.SomeOtherTable as SomeOtherTable
       On …
   Where
     ((@OptionalParameter1 Is Null) Or (SomeTable.SomeField = @OptionalParameter1))
     And ((@OptionalParameter2 Is Null) Or (SomeOtherTable.SomeField = @OptionalParameter2))

End

With these, the behavior is just plain random. Sometimes 2005 sniffs parameters, sometimes it does not. Sometimes simply recreating the procedure can fix errant behavior. Most of the time it does not. Sometimes With Recompile helps, sometimes spoofing the parameters like thus fixes it:

   … yadda yadda …

   Declare @X1 Integer, @X2 Integer

   Set @X1 = @OptionalParameter1
   Set @X2 = @OptionalParameter2

   Select
     …
   From
     dbo.SomeTable As SomeTable
     Inner Join dbo.SomeOtherTable as SomeOtherTable
       On …
   Where
     ((@OptionalParameter1 Is Null) Or (SomeTable.SomeField = @OptionalParameter1))
     And ((@OptionalParameter2 Is Null) Or (SomeOtherTable.SomeField = @OptionalParameter2))

(Hence, of course, my contention that sometimes there is sniffing, and sometimes there is not).

Still, after the bag of standard tricks, there are some procedures like the above that do the wrong thing some of the time, and others that do the wrong thing all of the time. Especially the ones with UDFs in the Where or Select clause, which is of course much more understandable. What to do here? See, re-ordering cannot rescue you here (especially since there are specimens with 5 optional parameters and up to 5 joins’ worth distance between each selector table).

Sometimes it helps to simply dumb things down, because there seems to be an internal threshold of query complexity beyond which the optimizer simply seems to give up and start joining in whatever order it seems to find most amusing and inefficient (yes, I am a little bitter about this), and do some more hand-holding:

   … yadda yadda …

   Declare @T Table(ID As Integer)

   Insert Into @T(ID)
   Select
     ID
   From
     dbo.SomeTable As SomeTable
     Inner Join dbo.SomeOtherTable as SomeOtherTable
       On …
   Where
     ((@OptionalParameter1 Is Null) Or (SomeTable.SomeField = @OptionalParameter1))
     And ((@OptionalParameter2 Is Null) Or (SomeOtherTable.SomeField = @OptionalParameter2))

   Select
     …
   From
     @T
     Inner Join [All the non-selector tables]
       …

But sometimes you truly have to hand-manufacture the danged pacifier:

   … yadda yadda …

   Declare @T Table(ID As Integer)

   If (@OptionalParameter1 Is Null) And (@OptionalParameter2 Is Not Null)
     Begin

       Insert Into @T(ID)
       Select
         ID
       From
         dbo.SomeTable As SomeTable
         Inner Join dbo.SomeOtherTable as SomeOtherTable
           On …
       Where
         (SomeOtherTable.SomeField = @OptionalParameter2))

     End

   Else [ And so on and so forth for every combination ]

   Select
     …
   From
     @T
     Inner Join [All the non-selector tables]
       …

Imagine the fun with 5 optional parameters.

On one hand, it is delightful to be a hero to one’s peers, bringing query times down from 8 seconds to .15 (not to mention the number of reads for said query from 1,000,000 to 8,000). But it drives me nuts having to periodically check to see which query has now started to misbehave, to the point where it is taking 40% of our four-core Opteron with 8GB RAM’s time… simply because the optimizer has decided that a threshold has been reached and it is now smarter to fully spool out 12 tables of 50,000+ records each rather than applying the correct SARG first and reducing the entire set to a single record.

On the other hand, I’d much rather be spending my time on refactoring error handling into Try … Except, or converting external polling applications to SQLCLR classes, or, well… other things.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: