Resource limits are a mechanism to prevent database queries from consuming excessive time and resources. Below are examples of how to set query execution time limits in MySQL and SQL Server.

MySQL's MAX_EXECUTION_TIME

In MySQL 5.7.8 and later versions, the MAX_EXECUTION_TIME hint can be used to limit the execution time of an SQL query. This hint instructs the MySQL server to allow a query to run for only a specific number of milliseconds. If the query execution exceeds this limit, the server will terminate the query.

Example:

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM my_table;

In the example above, the maximum execution time is set to 1000 milliseconds (1 second). If the query runs longer than this time, it will be interrupted.

SQL Server's QUERY_GOVERNOR_COST_LIMIT

SQL Server uses an estimated query cost to decide whether a query should be executed. The QUERY_GOVERNOR_COST_LIMIT is a configuration value that represents the maximum cost a query can have before being allowed to execute. If the estimated cost of a query exceeds this value, SQL Server will not execute the query.

Example:

-- Set the query governor cost limit to 300
SET QUERY_GOVERNOR_COST_LIMIT 300;
GO

-- Run a query
SELECT * FROM my_table;
GO

In the example above, the QUERY_GOVERNOR_COST_LIMIT is set to 300. This means that any query with an estimated cost higher than 300 will not be executed. The limit remains in effect until the session ends or the value is changed.

Notes:

• MySQL's MAX_EXECUTION_TIME limit only applies to SELECT statements.

• SQL Server's QUERY_GOVERNOR_COST_LIMIT applies to all queries within the session until it is reset or the session ends.

These limits should not be set arbitrarily. They should be based on system performance and business needs. Setting them too low might cause legitimate queries to be interrupted, while setting them too high might fail to prevent excessive consumption of system resources.

When setting resource limits, other related configurations and limitations should also be considered. For example, MySQL’s innodb_lock_wait_timeout controls the maximum time a transaction waits for a lock, while SQL Server’s MAXDOP (Maximum Degree of Parallelism) limits the maximum number of processors a query can use.

In practical applications, resource limits can also be set through database management tools or at the application level, so that even if the database doesn't terminate a query, the application can still interrupt long-running queries as needed.