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.