Long Running Queries

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.

Some work-around solutions:

Add more RAM to the server.

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.

Limit the elapsed time that reporting and/or OLAP queries can execute.

I’ve written proprietary SQL Agent jobs that:

  • Track and maintain a record of long running queries. The data obtained can be very useful when it is necessary to determine the source of a long running query and why it is taking so long to execute.
  • Will kill any process that exceeds a client’s predetermined threshold for query execution time maximums. This action effectively terminates the query and all executing threads in support of it, as is the case in parallel thread processing executions. It is a drastic action that prematurely terminates a report or OLAP operation. But when the most important criterion is the preservation of the OLTP environment, it may be the best alternative, since a report or OLAP execution can be re-executed, but an OLTP execution often cannot be re-executed, resulting in a lost transaction.

Reduce the size of the database tables that contain the data for reporting and/or OLAP queries.

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:

  • Data archiving of old data when implemented in conjunction with an appropriate corporate data retention policy.
  • Data trimming of old data when implemented in conjunction with an appropriate corporate data retention policy.

Restrict the time of day or night during which long executing queries can be executed.

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.

Contact me to schedule a free consultation on performance tuning your SQL Server.