Post
Ask Question

Learn to Avoid Query Governor Cost Limit Error in SQL Server

Castle Image

If you have ever encountered the error message

 "The query has been canceled because the estimated cost of this query (X) exceeds the configured threshold of X-1. Contact the system administrator."

You may wonder what a SQL Server query implies and how to correct it when executing it. I'll address the root of this problem and its fix in this article.

What is the cost limit of the query governor?

An SQL Server setting called the query governor cost limit sets a maximum duration for a query to execute. The duration, expressed in seconds, is predicated on approximating the query cost for a particular hardware setup. This parameter is meant to keep persistent inquiries from using excessive resources and interfering with the operation of other queries and applications.

Since the query governor cost limit has a default value of 3000, any query predicted to take more than 3000 seconds—or 50 minutes—will be aborted. The system administrator can change this value by using the stored method sp_configure.

I'm getting the error; why?

You may receive an error notice if your query has a high anticipated cost and is too complex or inefficient. Numerous variables, including the quantity and size of the tables involved, the indexes and statistics at hand, the join and aggregation techniques employed, the filters and sorting strategies employed, and the SQL Server version and edition, all affect how much a query is expected to cost.

For instance, running a query that includes scanning a sizable table devoid of indexes or filters could result in a high projected cost. Similarly, running a query that uses a function or subquery that is called for every row in the return set could result in a highly high estimated cost.

How do I correct the error?

Increasing the cost limit of the query governor or optimizing your query are the two probable solutions to the problem.

Raise the cost limit of the query governor: The SET QUERY_GOVERNOR_COST_LIMIT command can be used to raise the query governor cost limit for the active connection. For instance, you may execute the following line before executing the query if the projected cost of the query is 14.000:

SET QUERY_GOVERNOR_COST_LIMIT 15000

Here is an example of how to utilize it:

using Microsoft.Data.SqlClient;
using System.Data;

var connectionString = "Server=YourServerName;Database=YourDatabaseName;User=YourUserName;Password=YourPassword;";

// Create a SqlConnection and SqlCommand
using SqlConnection connection = new SqlConnection(connectionString);

connection.Open();

// Set the QUERY_GOVERNOR_COST_LIMIT before executing the query
var setQueryGovernorCostLimit = "SET QUERY_GOVERNOR_COST_LIMIT 15000;";
using (SqlCommand setQueryGovernorCommand = new SqlCommand(setQueryGovernorCostLimit, connection))
{
    setQueryGovernorCommand.ExecuteNonQuery();
}

// Complex SELECT query
var selectQuery = @"
    SELECT
        Orders.OrderID,
        Customers.CustomerName,
        Cars.CarModel,
        Colors.ColorName,
        Engines.EngineType,
        Wheels.WheelType,
        Interiors.InteriorType,
        Accessories.AccessoryName,
        Features.FeatureName,
        Options.OptionName,
        -- Add more fields related to the customized automobiles here
    FROM
        Orders
        INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
        INNER JOIN Cars ON Orders.CarID = Cars.CarID
        INNER JOIN Colors ON Orders.ColorID = Colors.ColorID
        INNER JOIN Engines ON Orders.EngineID = Engines.EngineID
        INNER JOIN Wheels ON Orders.WheelID = Wheels.WheelID
        INNER JOIN Interiors ON Orders.InteriorID = Interiors.InteriorID
        LEFT JOIN OrderAccessories ON Orders.OrderID = OrderAccessories.OrderID
        LEFT JOIN Accessories ON OrderAccessories.AccessoryID = Accessories.AccessoryID
        LEFT JOIN CarFeatures ON Cars.CarID = CarFeatures.CarID
        LEFT JOIN Features ON CarFeatures.FeatureID = Features.FeatureID
        LEFT JOIN CarOptions ON Cars.CarID = CarOptions.CarID
        LEFT JOIN Options ON CarOptions.OptionID = Options.OptionID
    WHERE
        -- Your filter conditions for customizations or specific orders here
";

 
using SqlCommand command = new SqlCommand(selectQuery, connection);

using SqlDataAdapter adapter = new SqlDataAdapter(command);

DataTable dataTable = new DataTable();
adapter.Fill(dataTable);

This will prevent the cancellation of your query. This method is not advised because it can impact the performance of other queries and apps. Additionally, this statement needs to be executed for each connection that requires the query to be done.

  • Optimize your query: Reducing the anticipated cost of your query optimization is a preferable course of action. The following methods can help you enhance the efficiency of your queries.
  • Use indexes: By reducing the amount of scanning and sorting necessary, indexes can help SQL Server locate the data more quickly. The columns used in your query's join, filter, and order by clauses can all have indexes created on them. The Database Engine Tuning Advisor can also recommend the best indexes for your query.
  • Use statistics:  SQL Server can estimate the number of rows and value distribution in tables with statistics. This can assist the SQL Server in selecting the query's optimal execution strategy. The UPDATE STATISTICS statement and the sp_updatestats stored procedure can be used to update the statistics in your tables.
  • Simplify your query: You can make your query simpler by eliminating any extraneous tables, joins, filters, columns, or sorting. Your query can be rewritten to take advantage of more effective operators, like EXISTS rather than IN, or it can be divided into shorter searches using temporary tables or common table expressions (CTEs).
  • Use the latest SQL Server version and edition: Use the most recent edition and version of SQL Server: These may offer improved performance and optimization features over previous iterations. For instance, the projected cost of your query for SQL Server 2022 Web Edition can be less than that of SQL Server 2019 Web Edition. The @@VERSION function allows you to verify your database's SQL Server version and edition.

Conclusion

A common issue in SQL Server that arises when your query is over the configured threshold and has a high estimated cost is the query governor cost limit error. Increasing the query governor cost limit or query optimization are two ways to resolve this problem. Nonetheless, query optimization is best because it can enhance query performance and efficiency while averting possible resource problems.


Similar Articles