Home > SQL Server > Override server-wide query governor cost limit in the current session – SET QUERY_GOVERNOR_COST_LIMIT – SQL Server

Override server-wide query governor cost limit in the current session – SET QUERY_GOVERNOR_COST_LIMIT – SQL Server


Query governor cost is used to set the maximum time period for queries to execute in SQL Server. This limit varies between different SQL queries and it decided by query execution plan with hardware configuration. This cost value usually refers to query estimated elapsed time and it is measured in seconds.

If a SQL query execution time exceeds the maximum limit given in the query governor cost than server throws an error.

We an obtain the query governor cost of the SQL Server using sp_configure.

pic1The default value is 0. It means query can run indefinitely. We can modify this value at server level using sp_configure statement. But, we can also modify query governor cost limit value for the current session only using SET statement.

SET QUERY_GOVERNOR_COST_LIMIT

QUERY_GOVERNOR_COST_LIMIT set statement is used to override the query governor cost limit value for the current session.

Usage

SET QUERY_GOVERNOR_COST_LIMIT value

value must be between 0 and 2147483647 (int) or numeric(38). If we specify a negative value then SQL server automatically change the value to 0 and if we give decimal value then it coverts the value to the next maximum whole number.

Example

pic2

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: