Long running queries are generally in the domain of reports and/or OLAP (Online Analytical Processing).
In an OLTP (Online Transaction Processing) environment, long report execution times are generally considered to be a violation of best practices, since they tend to naturally conflict with the requirements of an OLTP environment (instantaneous data capture). OLTP queries tend to execute very quickly to give fast response times. Reporting and OLAP queries tend to take much longer to execute because they examine large quantities of data to produce results.
Unfortunately, many database server and application environments are set up to operate in this fashion, where OLTP exists simultaneously with reporting and sometimes even OLAP. The primary point of contention is RAM. OLTP needs database components in RAM to facilitate quick response times. Reporting and OLAP queries read records from disk into RAM and overlay the data components needed by the OLTP environment for optimum performance.
If the reporting and/or OLAP requirements are minimal, the conflict between the 2 environments is minimal. When the reporting and/or OLAP requirements are intense, OLTP tends to suffer, sometimes to the point of lost transactions.
I’ve worked in environments where 3rd party application software prevented any rectification of this conflict at the server level. That is, there was no way to separate the OLTP environment, and the reporting and/or OLAP environment, onto different servers. So, I had to invent work-around solutions to prevent the OLTP environment from suffering.
This is a good solution if the server can accept more RAM but it still may not be an adequate solution depending upon the demands being placed on the server and the sizes of the databases.
I’ve written proprietary SQL Agent jobs that:
Large tables tend to aggravate the natural conflict between OLTP and reporting and/or OLAP activities. There are at least two solutions to this issue that involve a reduction of the size of the underlying tables causing the conflict. They can be accomplished via:
A corporate policy can be implemented that can be enforced using SQL code that restricts the execution of certain long executing report and/or OLAP queries to times of day or night when server activities and stress are at a minimum. If the queries must be executed daily, for instance, the queries can be automated to execute in the middle of the night and the resulting output can be saved for examination and use the following day.
These are not the only solutions, just some that I’ve implemented. Each solution has to be tailored to a company’s business requirements.