To enable resource limits, use:
sp_configure "allow resource limits", 1
A value of 1 enables resource limits; a value of 0 disables them. allow resource limits is static, so you must restart the server to reset the changes.
allow resource limits signals the server to allocate internal memory for time ranges, resource limits, and internal server alarms. It also internally assigns applicable ranges and limits to login sessions.
Setting allow resource limits to 1 also changes the output of showplan and statistics i/o:
showplan displays the optimizer’s cost estimate for the entire query as a unitless number. This cost estimate is dependent on the table statistics (number and distribution of values) and the size of the appropriate buffer pools. It is independent of such factors as the state of the buffer pools and the number of active users. See Chapter 2, “Using showplan,” in the Performance and Tuning Series: Query Processing and Abstract Plans.
statistics i/o includes the actual total I/O cost of a statement according to the optimizer’s costing formula. This value represents the sum of the number of logical I/Os multiplied by the cost of a logical I/O and the number of physical I/Os multiplied by the cost of a physical I/O.
