While there are things like INSERT DELAYED in SQL and Microsoft have the Resource Governor these are not automatic. Developers have to use INSERT DELAYED and DBAs have to configure the Resource Governor.
I'd like to see a SQL query scheduler that could be configured up front to give automatically priority to short running queries and reduce the priority of long running queries. This might be better done at the transaction level. The main configuration would be the cut off point for short vs. long; medium might be in there too. This configuration would be greatly helped by some transaction count vs. run time/IOs etc. statistics.
Short running transactions should be given a high priority and long running ones a lower priority.
All queries would start off as high priority but as time passed their priority would reduce. Read only transactions (or at least ones that are read only so far) could be pushed down way the priority. As with all schedulers care would need to be taken to ensure that long running queries are allowed to continue to completion and that the whole database does not lock-up in huge deadlocks.
This would allow interactive use (typified by small, short lived transactions) co-exist with reporting and MIS use (typified by large, long lived read only transactions).
I'm sure a real life implementation would throw up some complications but that's where the implementation fun lies!
I'd like to see a SQL query scheduler that could be configured up front to give automatically priority to short running queries and reduce the priority of long running queries. This might be better done at the transaction level. The main configuration would be the cut off point for short vs. long; medium might be in there too. This configuration would be greatly helped by some transaction count vs. run time/IOs etc. statistics.
Short running transactions should be given a high priority and long running ones a lower priority.
All queries would start off as high priority but as time passed their priority would reduce. Read only transactions (or at least ones that are read only so far) could be pushed down way the priority. As with all schedulers care would need to be taken to ensure that long running queries are allowed to continue to completion and that the whole database does not lock-up in huge deadlocks.
This would allow interactive use (typified by small, short lived transactions) co-exist with reporting and MIS use (typified by large, long lived read only transactions).
I'm sure a real life implementation would throw up some complications but that's where the implementation fun lies!
Comments
Post a Comment