As a sysadmin at this manufacturing plant, this pilot fish is aware of the problems that persistently plague a database that sits on a server that another admin is responsible for.
“About once a quarter, users would start complaining about how slow the database was,” says fish. “The database sysadmin would be questioned extensively by management and given hell for the problems on his systems.
“He would run diagnostics and pull his hair out for a few days. Then eventually things would go back to normal.”
This goes on for years, until the other sysadmin moves on — and fish becomes responsible for the database server.
Fortunately, his ex-colleague did manage to convince management to buy a comprehensive performance monitoring suite, though that never led him to pinpoint the slowdown.
Now it’s fish’s problem.
“As I was monitoring what was going on, I noticed that every two hours the disk I/O went through the roof,” fish says. “And each day it seems to be getting a little worse. It’s not hard to narrow down the culprit: There’s only one job scheduled to run every two hours on the hour.”
So fish digs out a database manual, teaches himself a little SQL, and soon narrows the problem down to one query — and a few tests confirm his suspicions.
What’s happening is that every two hours, the job adds data to a table. But it verifies the data in the most expensive way possible: For each entry that’s supposed to be in the table, the query reads the entire table and then checks the entry.
Fish figures that, when performance gets bad enough, one of the database admins manually cleans up the table and things improve.
“I presented my findings to the database admin group, including a rewrite of the query to eliminate the full table scan,” says fish.
“The answer? ‘We can’t do that because we would have to hire a consultant to evaluate the change.’
“I found out later that they had hired a consultant two years earlier and made the exact change I requested at another site. I’m not sure why the company has ‘database administrators’ on staff if they can’t even rewrite a simple query.”