Microsoft® SQL Server® 2012 Bible

502

Part V: Enterprise Data Management

set by the query governor, then SQL Server does not execute the query. By default, the

query governor cost limit option is set to 0. This value enables all queries to execute, no

matter how long they take.

The query governor cost limit option does not abort queries with an estimated duration of less than the limit but a

longer actual duration.

In Management Studio, the query governor cost limit confi guration option can be

set by typing the limit in the Use Query Governor to Prevent Long-Running Queries box on

the Server Properties Connections tab (refer to Figure 19-9).

The following code sets the query governor cost limit to 300 seconds for the entire

server:

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'query governor cost limit', 300;

RECONFIGURE;

In code, the query governor can also be changed for the current connection. The following

code overrides the currently confi gured query governor cost limit value for the cur-

rent connection and sets it to 15 seconds:

SET QUERY_GOVERNOR_COST_LIMIT 15;

Use the query governor cost limit option to stop long-running queries before they start and thereby help

prevent system resources from being consumed by these long-running queries.

Remote Access

The remote access option enables running local stored procedures from remote servers

or remote stored procedures from the local server. By default, the remote access option

is enabled.

The remote access option applies only to servers added using sp_addserverand is included for backward

compatibility.

Using this feature is not recommended because it will be removed in the next version of Microsoft SQL Server. Use

the sp_addlinkedserver feature instead.

To disallow remote access, uncheck the Allow Remote Connections to This Server check

box in Management Studio on the Server Properties Connections tab (refer to Figure 19-9)

or set the remote access option to 0 in code:

c19.indd 502c19.indd 502 7/30/2012 5:43:02 PM7/30/2012 5:43:02 PM

http://www.it-ebooks.info