A customer called with the following situation:
OE10.1C on Windows 2008 Server. They run a client server application. They also have some data reporting tool that an end user can work to write SQL queries to the database. For now the name is not important because I do not know it :-) In fact, they may run more than one tool. The tool(s) runs native on the the db server.
Users being users, they seem to create poorly formed and inefficient queries. Like most tools, there is little to no optimization and they allow the user to cripple a perfectly good machine with a horrible query.
I am starting to research how to manage (find, kill, alter state, alter priority, restrict) any such ill-formed queries.
Ideally, I need to find a way to identify a query and either remove it or knock it down so as not to bring the production machine to it's knees.
Thoughts or shared experiences on the topic?
Thanks,
Bob Brennan
Hi Bob,
You can enable SQL statement tracing on the server side and extract problematic queries that run when problems occur. You can even set it to record query plans of running statements that you suspect are causing trouble.
Here's how you can do that:
knowledgebase.progress.com/.../P119161
Hope it helps
Marek
Hi Bob,
You can enable SQL statement tracing on the server side and extract problematic queries that run when problems occur. You can even set it to record query plans of running statements that you suspect are causing trouble.
Here's how you can do that:
knowledgebase.progress.com/.../P119161
Hope it helps
Marek
other suggestions that might help in part :
- 10.1C is getting old. upgrade.
- consider additional indexes. the ones you have were probably chosen to meet the needs of the 4GL applicaiton and not the needs of ad-hoc queries.
- are your table and index statistics reasonably current ?
Depending on the query tool, it may be possible to lock it down so it can only run canned queries. If you can meet with the users and create a reasonable set of canned queries that meet their business needs (and are optimized for performance), then it is possible they will no longer have the desire or the need to run ad hoc. Or maybe I'm just an optimist. :)
You're an optimist ...
Thanks Marek. Good info on SQL tracing. That covers half my question. It helps discover the offenders What do do with a running query that is currently bogging down a live server. Still need to figure out how to deal with these.
Bob
Well another view on 'dealing' with this is of course to take the SQL reporting users out of the production sandbox and let them connect to a copy. OER Target databases are widely used for this at various customer sites.