Chapter 1: Limiting Access to Server Resources

Choosing a limit type

After you determine the users and applications to limit, you have a choice of three different types of resource limits.

Table 1-2 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. In some cases, it may be appropriate to create more than one type of limit for a given user and application. For more information on limit types, see “Understanding limit types”.

**Table 1-2: Resource limit types**
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

The spt_limit_types system table stores information about each limit type.

View this book as PDF