After you determine the users and applications to limit, you have a choice of three different types of resource limits.
Table 6-1 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”.
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 |
Pre-execution 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.
