Choosing a Limit Type

After you determine the users and applications to limit, chose the resource limit type.

This table describes the function and scope of each limit type and indicates the tools that help determine whether a particular query might benefit from this type of limit. You may want to create more than one type of limit for a given user and application.

Limit Type

Use for Queries That

Measuring Resource Usage

Scope

Enforced During

io_cost

Require many logical and physical reads.

Use set showplan on before running the query, to display its estimated I/O cost; use set statistics io on to observe the actual I/O cost.

Query

Preexecution or execution

row_count

Return large result sets.

Use the @@rowcount global variable to help develop appropriate limits for row count.

Query

Execution

elapsed_time

Take a long time to complete, either because of their own complexity or because of external factors such as server load or waiting for a lock.

Use set statistics time on before running the query, to display elapsed time in milliseconds.

Query batch or transaction

Execution

tempdb_space

Use all space in tempdb when creating work or temporary tables.

Number of pages used in tempdb per session.

Query batch or transaction

Execution

idle_time

Are inactive.

Time, in seconds, during which the connection is inactive.

Individual processes

Preexecution

The spt_limit_types system table stores information about each limit type.

Related concepts
Identify Users and Limits