Desperately Seeking Seeks

This month’s TSQL Tuesday topic (follow Twitter hashtag #TSQL2sDay) is hosted by Michael J. Swart ( blog | Twitter ) and the topic is, “What are your thoughts on Database Indexes?”

One of the most powerful features of indexes is that they can provide a performance benefit without requiring a change to the underlying code. This is incredibly important in my world, as I work for a software company and when customers encounter performance issues, I cannot change code in an attempt to tune queries; but I can add indexes.

We had a customer issue in July where a customer found a particular query that took seven seconds to execute. The query was something like this: 

{code}SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE RTRIM(LastName) LIKE ‘Smith%’;{/code}
And the Execution Plan looked like this: 

 (Note: I’m using the AdventureWorks database for this example, even though a RTRIM would likely never be used against LastName in the Person.Contact table because it’s a nvarchar data type. In my issue with the customer, the column was a char.)

I can hear the sighs now, many of you know what’s coming; but let’s step through trying to tune this with indexing.

First, if we check the indexes on the table, we see there is no index that leads with LastName: 

Let’s add an index on LastName: 

{code}CREATE NONCLUSTERED INDEX IX_LastName ON Person.Contact(LastName);{/code}

Now let’s see what the Execution Plan looks like when we re-run our original query:

It’s better. We now have an Index Scan instead of Full Table Scan, and since the index is much narrower than the table, the IO should be much lower. But I want a seek on this query. The index I created isn’t a covering index, and I can see the Key Lookup to get the FirstName and EmailAddress from the Clustered Index.  I know that querying only on LastName, or creating an index to cover all three columns will alleviate the Key Lookup, but it won’t affect the Index Scan.  We can prove it by only selecting LastName (again, I could also create a covering index to test, but this is easier):

Still no seek, and the Key Lookup is gone as expected. What else can I modify? Yep…the function. Let’s remove the RTRIM and see what we get:

Fabulous, now I have the Index Seek I was so desperately seeking. I could drop the index I created and create a covering one to support the original three-column select, but that doesn’t solve the fact that I still have a function in the query. And as I stated earlier, I cannot modify the code. So what can I do?

What I haven’t told you is that this customer is running our software on Oracle. Don’t worry, I still love SQL Server best, but our application supports both platforms. In Oracle, I can create a Function Based Index to support this type of query. The syntax in Oracle , for our example, is: 

{code}CREATE INDEX IX_LASTNAME_FUNCTION ON PERSON.CONTACT(RTRIM(LastName));{/code} 

 When we added this index to the customer database, the query time for the original query became sub-second.

But what if the customer had been running SQL Server? Does SQL Server allow a Function Based Index? It does not – you will find no reference in the CREATE INDEX documentation. The closest thing is a computed column in the table, using that function, with an index on that computed column. Let’s see how the computed column would work.

First, alter the table: 

{code}ALTER TABLE Person.Contact ADD LastName_Computed AS RTRIM(LastName);{/code} 

 Second, create an index to support the query: 

{code}CREATE NONCLUSTERED INDEX IX_LastName_Computed ON Person.Contact(LastName_Computed);{/code} 

 Now, at this point, I cannot just re-run my original query and expect it to use the new index on LastName_Computed, I have to change the column I am selecting and querying in my query: 

{code}SELECT LastName_Computed FROM Person.Contact WHERE LastName_Computed LIKE ‘Smith%’;{/code} 

 If I do this, I get an index seek on LastName_Computed. But I’m back to square one because again, in my situation I actually don’t have the ability to change code. I can change the schema, so adding the column and the index would be ok, but the code would need to be altered to select from this column.

I would bet that when many of you saw this query, your first question was, “Why is there a RTRIM in the where clause?” It’s a good question, and one that I took to development. I’m still working to sort out why we have the RTRIM in there, but the good news is that it is only in the code for Oracle and we can add the index as a work-around for now.

And for the record, the rule-follower in me believes the plural of index should be written as indices, but because I never see it or hear it used anywhere, I always use indexes.

Clean up code: 

{code}DROP INDEX Person.Contact.IX_Lastname;
DROP INDEX Person.Contact.IX_LastName_Computed;
ALTER TABLE Person.Contact DROP COLUMN LastName_Computed;{/code} 

*Update: Follow up and additional information can be found in Seek and ye shall find.