In planning a resource limit, consider:
When to impose the limit (times of day and days of the week)
Which users and applications to monitor
What type of limit to impose:
I/O cost (estimated or actual) for queries that may require large numbers of logical and physical reads
Row count for queries that may return large result sets
Elapsed time for queries that may take a long time to complete either because of their own complexity or because of external factors such as server load
Whether to apply a limit to individual queries or to specify a broader scope (query batch or transaction)
The maximum amount of idle time for users who start a connection but leave it idle for a long time, potentially using system resources such as locks.
Whether to enforce the I/O cost limits prior to or during execution
What action to take when the limit is exceeded (issue a warning, abort the query batch or transaction, or kill the session)
After completing the planning, use:
sp_add_time_range to create a named time range to specify times for imposing the limit.
sp_add_resource_limit to create new resource limits.
sp_help_resource_limit to obtain information about existing resource limits.
sp_modify_time_range and sp_modify_resource_limit to modify time ranges and resource limits, respectively.
sp_drop_time_range and sp_drop_resource_limit to drop time ranges and resource limits, respectively.
