Estimating the amount of procedure cache used by create index

create index sorts data within the index key. This sort may require one or more in-memory sorts and one or more disk-based sorts. Adaptive Server loads the data create index sorts into the data cache associated with the table upon which it is creating an index. The number of data cache buffers the sort uses is limited by the value of number of sort buffers. If all keys being sorted fit into the value for number of sort buffers, Adaptive Server peforms a single, in-memory sort operation. If the keys being sorted do not fit into the sort buffers, Adaptive Server must write the results of the in-memory sort to disk so Adaptive Server can load the next set of index keys into the sort buffers to be sorted.

In addition to the sort buffers allocated from the data cache, this sort operation also requires about 66 bytes of metadata from the procedure cache. The formula for the amount of procedure cache used, assuming all of the sort buffers are used, is:

Image consists of two forumulas: Procedure cache size = number of sort buffers times rows per page times 85 bytes.

Example 1

In this example,

As a result, all 500 2K buffers are used to hold the data being sorted, and the procedure cache uses 2,111 2K buffers:

(131 X 500 X66) / 2048 = 2,111 2K buffers

Example 2

In this example,

As a result, all 5,000 2K buffers are used to hold the data being sorted, and the procedure cache uses 39, 639 2K buffers:

(246 X 5,000 X 66) / 2048 = 39,639 2K buffers

Example 3

In this example:

As a result, all 5000 2K buffers are used to hold the data being sorted:

(39 X 1,000 X 66) / 2048 = 1,257 2K buffers

But the data cache has 200 2K buffers left over for overhead, so the procedure cache uses 1057 2K buffers.