This week, I got a chance to do some performance tuning and optimization (PTO). This is the favorite part of my job, but sadly not too prevalent (we typically identify a problematic query/routine and leave the rest up to the customer). Over the course of my career, I hope to specialize in performance—making PTO the bulk of or even the entirety of my work. Anyway…
I was assigned a work order for resolving timeouts of a custom monitoring query for replication jobs on one of our customer’s instances. The instance in question has a lot of SQL Server Agent jobs (just under two-hundred), so the [msdb].[dbo].[sysjobhistory] table is pretty big. I used two PTO approaches, index tuning and refactoring the query (when used together, the performance improvement can be amazing).
While I was revising the query, I discovered a bug. The usage of the DateDiff() function caused results to be returned that were more than an hour prior to the execution of the query. I contacted the “Assigned System Engineer” (or ASE—basically our employee that is a technical liaison to the customer) and requested permission to fix the bug in my new implementation. It is always a good idea to get approval to fix bugs when refactoring code. The calling application may break if the routine’s behavior changes, even though the original results are incorrect.
The ASE was fine with the change, but also ran it past the guys on the Monitoring Systems team, including the engineer who had written the original query. I didn’t expect this, and was immediately concerned about the potential for an affront to a co-worker that I haven’t even met. Pointing out a flaw in somebody else’s code needs to be done delicately, and I hadn’t taken any time to choose my words carefully. I expected the ASE to simply rubber-stamp my request.
Fortunately, the engineer on the monitoring team (who wrote the query) turns out to be a really good guy. Instead of bristling at my claim, he sought to understand it… his curiosity trumped his pride. A fantastic dialogue developed in which the engineer and I exchanged numerous emails, all in the spirit of learning. He even found a mistake in my explanation of the bug (I was misattributing the problematic functionality of the DateDiff() function to integer rounding when it actually had to do with the concept of boundaries). Then, knowing engineer’s attitude, I offered to explain the entire set of optimizations for the work order. It took me an extra day to complete the work order, but he really appreciated the extra information and I enjoyed putting it together.
We’ve now exchanged many emails and I’ve found a kindred spirit. When I meet a person with a thirst for learning and a passion for technology, I’m happy to invest time and energy into sharing what I know. I enjoy SQL Server immensely; learning about it, using it, and teaching it. Teaching also has a wonderful way of deepening one’s own knowledge of a subject too.
NOTE: This work order applied to a SQL Server 2000 instance, so the links herein refer to that version.
