Cache bindings

Sorts for create index take place in the cache to which the table is bound. If the table is not bound to a cache, but the database is, then cache is used. If there is no explicit cache binding, the default data cache is used. Worktable sorts use the cache to which tempdb is bound, or the default data cache.

To configure the number of sort buffers and large I/O for a particular sort, always check the cache bindings. You can see the binding for a table with sp_help. To see all of the cache bindings on a server, use sp_helpcache. Once you have determined the cache binding for a table, use sp_cacheconfig check the space in the 2K and 16K pools in the cache.