Performance Tuning SQL Server – non technical solution
Finding a resolution a Non-Sargable Query – Its not always about your SQL Server technical astuteness
Being passionate and committed to solving SQL Server performance issues when a resource hungry SQL query within the database performance monitoring tool was glaringly obvious, I was eager to delve straight in.
The t-sql embedded within the application code was a non-sargeable query.
WHERE customerid LIKE %customerid%
- It was reading 10’s of GB’s of data
- Taking on average 15 minutes to return results
- Ran at least 20 times a day
Next Step
That was an easy analysis. I took off like a shot to discuss with the application development team. Eager to schedule time at the next stand-up for allocation within forthcoming sprint. But nobody wanted to touch it!
Same old replies from Product Owners and developers alike:
- “the customer service web interface is a legacy app”
- “not my code, don’t know how it works, was written before I joined the company”
But the system was used by company employees 24/7 and there was no sight of the new system.
Historically the systems’ performance was labelled a database problem. Not only by the whole of Engineering department but throughout the whole company. From Accounts to Compliance, Marketing to outsourced Cleaners
Everyone wanted to work on the latest technology. Keen to develop new functionality however large or small to other services within the tech stack. In general, nobody had an appetite to re-architect the problem child. Let alone try to assess and get to grips with the current code. Which would then have their name’s associated to amendments in the corresponding GitHub repository.
Persistence and Change in Strategy
Well I told myself
- IF I CAN’T FIX IT, I MUST FIX IT!
So it was time to be pragmatic
- TIME TO CHANGE MY TACTICS AND FIND ANOTHER WAY
Breakthrough
Time to schedule an informal meeting with the Customer Services Team Manager. Sit down at their desk, listen, observe and ask questions.
After users experiencing several years of pain and blame without action from anyone within technology there was a simple solution.
where customerid = 'customerid'
- Users never searched for part of a customerid
- They cut and pasted the customerid field in its entirety every time from a prior search that brought up customer details and this specific piece of information.
The fix that was going to delight the users and managers
- increase employee productivity
- change the business customer’s experience dramatically
- reduce 150 man hours/month of waiting on results.
This had me swaggering across the office like John Wayne with piles under my armpits (Bad joke alert)
Armed with this information, the Product Owners all wanted to assist now. They were fighting between themselves to take ownership. Wanting to assign a developer to make the small code change mid-sprint.
Developers were now ready to offer their services to test and deploy into Production as quickly as possible.
Can do Attitude, Courage and gaining Clarity
Having worked at large financial institutions supporting/developing critical profit making systems, as a database expert, you know that the blame for any sudden issues to production initially, more often than not, comes heading to the database owners.
DBAs are usually the very risk averse. Thus, first to investigate their platform when outages and degradation occur. Subsequently if the issue isn’t database related often we have enough information to assist other teams in finding and resolving the root cause.
Job satisfaction and reward, like life, is about giving
Take control, have a strategy and always give more to others than you ever expect to receive
Be grateful and have grace for what you have learned and perhaps failed at in the past that enables you to grow and utilise your experience to add value to others…
