|Last updated: 06 September 1998
Query filter to catch "Queries from Hell"
This is derived from a
tool I once wrote to intercept end-user-written queries
which might be unacceptably heavy. Those end-users, while
technically not familiar of any SQL or RDBMS concepts,
were allowed to type in free SQL queries as they liked,
which could contain anything.
This type of query, a.k.a. "Queries from Hell",
often tend to include Cartesian Products, huge joins not
using any indexes and other things you might not be happy
with as a DBA, because they would put too heavy a load on
Basically, this tool
examines the query without executing it, and tries to
figure out if the query is likely to be very heavy, or
does other things you don't want them to do. If so, the
tool generates a message; based on this, you can give
some gentle explanation to the user why you don't accept
his query and possibly suggest some improvements.
Should the query look harmless, you can then execute it
and pass the results back to the user.
Another possible application is to use it as part of some
sanity checks on newly developed or modified SQL code
which is kept in a repository. When a new version of a
module is checked into the repository, you could run an
automatic check using this filter, to see if there is
anything in the code which shouldn't be there, for
example forcing of indexes. This way, the filter could
help enforcing SQL coding standards.
Anyway, while this
query filter is not perfect, it works surprisingly well.
Give it a try...
It's a Bourne Shell
script, which was successfully tested on Digital Unix and
Solaris with SQL Server 11.0. For technical reasons, it
didn't run on ASE 11.5, and I never found the time to
update to make it work with 11.9 or later versions (you
could yourself to make it work in combination with ASE's
"resource limits" feature, though).
Note that this isn't a
full-featured tool, but rather something that could serve
as inspiration how to tackle cases like this. Download
it from here & turn it into something that works