About a month ago I blogged about an issue I had with a customer where a search in one area of our application returned less data than expected, and a search in a different area of the application returned the proper amount of data. I was able to work around the issue by dropping a unique, nonclustered index from one of the tables, and I wrote the post hoping to find a true solution.
One reader, Colleen, left this comment:
I wonder if this is an example of “when an unused index isn’t an unused index.” Rob Farley has a great chapter in SQL Server MVP Deep Dives about this. Indexes that don’t actually appear in the query plan are still being used by the optimizer, and dropping them affects the plan. A merge join needs to sort the data first, and since you have the query on that column, perhaps the optimizer is using the index? Doesn’t really explain why you only get 2 rows, but it might explain why dropping the seemingly unused index affects the plan.
This suggestion was worth pursuing, and I went and read Chapter 40 of the SQL Server MVP Deep Dives book, titled “When is an unused index not an unused index?”, and it seemed like it might be the problem. Then I asked Rob Farley ( blog | @rob_farley ) about it on Twitter and he agreed it sounded like my issue. Rob then took the time to chat online with me (how I LOVE the internet) and explained in depth what could be occurring.
In the meantime, I had requested a copy of the database and the customer finally agreed to send it. I don’t like to request customer data, and we don’t do it often, but sometimes we simply cannot recreate an issue. We have confidentiality agreements in place and there are numerous compliance rules that we follow; for example, the data can only exist on a specific server to which authorized individuals have access. I finally got the backup and had it restored to a SQL 2000 instance. I connected, recreated the index that I had dropped as a workaround, ran the query and…all the rows came back. I couldn’t recreate it. Rats.
I checked the version of SQL Server, it was 8.0.2282. I checked the customer record, they were running 8.0.2055. I asked the customer to apply the appropriate hotfixes and patches to get up to 2055, and last week I dialed in to recreate the index in their environment and test again. Lo and behold, all the rows appeared in the search. Apparently it was a bug, and not Rob’s example of an unused index really being used. I was kind of hoping it was what Rob described, but in the end I’m glad it’s resolved.
Thanks to Colleen for pointing out Rob’s chapter, and to Rob for all the education. I feel very fortunate that I am able to interact with SQL Server MVPs and experts so easily through Twitter. It has made such a difference in my professional development, and has helped with a few customer issues as well. As I have said many times, I am so grateful for the SQL Server community!
