Long time overdue.

My apologies for taking such a long time to post this.  Had a little spill about 3 weeks back and it put me in the hospital.  I guess the gardening and horsing around with my son finally took a toll on my back.  I am finally able to get online and will start blogging soon (once I get back into the swing of things).  Also, being that I am still in the hospital they seem to have twitter blocked (go figure).  I am actively working on getting back to it and back to the community, which is why you haven't seen me out there for the last few weeks.  I so do miss it.  Hope to 'see' you all there soon.

I am honored to be giving a virtual presentation for PASS.

Thanks to Thomas LaRock (twitter) for introducing me to Jeremiah Peschka (twitter), who took the time to review my presentation and give it the stamp of approval.  I will be giving my virtual presentation on SSIS thread load balancing October 13th at 1pm EST.  http://appdev.sqlpass.org/ has all the information.

Comments Welcome.

I made a change in the configuration of my web page to allow anonymous comments.  Writing in a vacuum doesn't help anyone.

Indexes and Convert_Implicit

When you are designing your where clause to access data from a given set of tables it would benefit you to pay careful attention to the data types of the columns you will be using.  SQL Server tries to help out by implicitly converting between data types which can cause 'hidden' optimizer issues.

A common example is the implicit conversion between character and integer datatypes.  Listed below is a snippet of the query I was asked to tune.

 You will notice the number 45 in purple.  The graphical query plan produced this:

 What first drew my attention was that 90% of the time was being taken up by a very small part of the overall where clause.  Digging deeper I saw this:

 

 Highlighted in purple was the culprit.  A quick look at the column dbo.stcsmf11.hlvno showed it was a char(2), yet in the where clause I was joining it as a number stcsmf11.hlvno=45.  I changed the where clause to be:

The subtle change in the where clause (making the number (45) into a character ('45')), yields a dramatic improvement.  The time spent in that specific section of code goes from 90% to 2%:

The detailed picture shows two important pieces of information.  First, we are doing an Index Seek vs. an Index Scan and secondly we have an additional join now being used.  The join you see was always there, but due to the implicit conversion, SQL Server was not able to use the other qualifier.  The additional seek predicate is 00779081.hlval.

 It is very important to remember to be as specific as possible in your where clauses and keep a close eye on data types.

Procedures, Parameters, and the Optimizer

The stored procedure is a very powerful tool and if you follow a few rules, it will perform very fast and efficiently.

Rule 1: Do not change parameters that are SARGS.

Rule2: If you must break Rule 1, split your procedures.

Let's create a procedure to use in our examples:

A little pubishing industry background.  ISBN10 was a 10 character identifier used for all books.  The industry started running out of available ISBNS, so a new ISBN13 was introduced.  There is a formula to convert between ISBN10 & ISBN13 and many legacy applications still rely upon ISBN10.  Now let's dig into RULE1.

There are two input parameters for this procedure:@ISBN and @discount, but only 1 of them is a SARG.  The statement below breaks the first rule by modifying the parameter that is a SARG: @ISBN.  It does this in the following sql:

SELECT @ISBN=

   dbo.udf_CnvISBN13_to_ISBN10(@ISBN)

Why does this matter?  The optimizer must first calculate all of the query plans prior to executing the procedure itself, so the above sql statement happens AFTER the plans have been determined.  If I pass in the ISBN= '9780486438511', the optimizer will look to use that value to join to Book.ISBN.  The optimizer chooses the query plan, runs and then wham!  You are now looking for ISBN='0486438511' and the optimizer most likely picked a wrong plan, which will lead to poor performance.

You notice that I have mentioned nothing about the modification of @discount.  That's because it is not used as a SARG and the optimizer isn't going to use it in determining the query plans.

If due to certain restrictions the application is passing in an ISBN3 and you must convert it to an ISBN10, you use RULE 2

As you can see from the above image, I have broken the stored procedure into two different procedures.  The first procedure merely does the conversion of the ISBN and override of the discount before passing it on to the 'inner' procedure.  When the inner procedure is executed, the optimzer will have the correct information and will grab the most efficient query plan, based upon the data passed in.