This week started off in Nashville at the in.sight user conference and expo. My team prepared 6 SQL Server presentations that went off great. I couldn’t be prouder of my crew. Back at home the rest of the team did a great job holding down the fort while shorthanded. 2 of my presentations were extremely technical in nature covering minimum maintenance on a SQL Server and index sargability. Upon returning home the strangest coincidences occurred.
On my first day back to work I received a case where a customer had gotten corruption in their database. By the time the case made its way to me at around 4pm ET, the corruption was so bad and had been going for so long that our desperate efforts to avoid data loss kept us working until 4 the next morning. The issue had actually started alerting in the error log a full 20 hours before I ever got on a call and 32 hours before our resolution. I can’t help but wonder what might have been had that person seen my presentation from 2 days earlier about how good maintenance can help spot early warning signs of corruption. I suspect we would have been able to recover much more quickly and easily than we did and would have gotten a few more hours of sleep that night.
Fast forward to today and I get a frantic call from a developer rolling out a new piece of code at a customer site. It’s running poorly, but it’s not obvious why. He tells me that if he walks back into the dispatch office without a fix they’ll kill him. It took me about 10 minutes, but I noticed a join predicate comparing a CHAR(18) column to an integer column. This CHAR(18) column is indexed, but that index it is not being used by the query engine. The data type difference made that argument non-searchable. One additional CAST function later and this query had dropped from 18,000,000 reads to 6,000. I just double checked my script as presented to the class in attendance and found this exact join predicate as an example where sargability can be easily lost. The script included the CAST function as a resolution.
These coincidences are just that. But they serve to remind me that what we’re trying to teach at these events is truly relevant and topical. I’ll hope to see many of you at SQL Server Groups, SQL Saturdays, and, for my customers, eventually at in.sight 2018 in Houston so that hopefully you can avoid being a part of my next bad-news blog!
Great Site. Very much enjoyed reading.