Summary information |
|
|---|---|
Default value |
200 |
Range of values |
0–2147483647 |
Status |
Dynamic |
Display level |
Comprehensive |
Required role |
System administrator |
Configuration groups |
Memory Use, SQL Server Administration |
number of aux scan descriptors sets the number of auxiliary scan descriptors available in a pool shared by all users on a server.
Each user connection and each worker process has 48 scan descriptors exclusively allocated to it. Of these, 16 are reserved for user tables, 12 are reserved for worktables, and 20 are reserved for system tables (with 4 of these set aside for rollback conditions). A descriptor is needed for each table referenced, directly or indirectly, by a query. For user tables, a table reference includes:
All tables referenced in the from clause of the query
All tables referenced in a view named in the query (the view itself is not counted)
All tables referenced in a subquery
All tables that need to be checked for referential integrity (these are used only for inserts, updates, and deletes)
A table created with select...into
All worktables created for the query
If a table is referenced more than once (for example, in a self-join, in more than one view, or in more than one subquery) the table is counted each time. If the query includes a union, each select statement in the union query is a separate scan. If a query runs in parallel, the coordinating process and each worker process needs a scan descriptor for each table reference.
When the number of user tables referenced by a query scan exceeds 16, or the number of worktables exceeds 12, scan descriptors from the shared pool are allocated. Data-only-locked tables also require a system table descriptor for each data-only-locked table accessed with a table scan (but not those accessed with an index scan). If more than 16 data-only-locked tables are scanned using table scans in a query, auxiliary scan descriptors are allocated for them.
If a scan needs auxiliary scan descriptors after it has used its allotted number, and there are no descriptors available in the shared pool, Adaptive Server displays an error message and rolls back the user transaction.
If none of your queries need additional scan descriptors, you may still want to leave number of aux scan descriptors set to the default value in case your system requirements grow. Set it to 0 only if you are sure that users on your system will never run queries on more than 16 tables and that your tables will always have few or no referential integrity constraints. See “Monitoring scan descriptor usage”.
If your queries need more scan descriptors, use one of these methods to remedy the problem:
Rewrite the query, or break it into steps using temporary tables. For data-only-locked tables, consider adding indexes if there are many table scans.
Redesign the table’s schema so that it uses fewer scan descriptors, if it uses a large number of referential integrity constraints. You can find how many scan descriptors a query would use by enabling set showplan, noexec on before running the query.
Increase the number of aux scan descriptors setting.
The following sections describe how to use sp_monitorconfig to monitor the current and high-water-mark usage to avoid running out of descriptors, and how to estimate the number of scan descriptors you need.
sp_monitorconfig reports the number of unused (free) scan descriptors, the number of auxiliary scan descriptors currently being used, the percentage that is active, and the maximum number of scan descriptors used since the server was last started. Run it periodically, at peak periods, to monitor scan descriptor use.
This example shows scan descriptor use with 500 descriptors configured:
sp_monitorconfig "aux scan descriptors"
Usage information at date and time: Apr 22 2002 2:49PM. Name Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name ------------------------ ---------- ---------- ------- ----------- ---------- -------------------- number of aux 260 240 48.00 427 0 NULL
Only 240 auxiliary scan descriptors are being used, leaving 260 free. However, the maximum number of scan descriptors used at any one time since the last time Adaptive Server was started is 427, leaving about 20 percent for growth in use and exceptionally heavy use periods. “Re-used” does not apply to scan descriptors.
To get an estimate of scan descriptor use:
Determine the number of table references for any query that references more than 16 user tables, or for those that have a large number of referential constraints, by running the query with set showplan and set noexec enabled. If auxiliary scan descriptors are required, showplan reports the number needed:
Auxiliary scan descriptors required: 17
The reported number includes all auxiliary scan descriptors that are required for the query, including those for all worker processes. If your queries involve only referential constraints, you can also use sp_helpconstraint, which displays a count of the number of referential constraints per table.
For each query that uses auxiliary scan descriptors, estimate the number of users who would run the query simultaneously and multiply. If 10 users are expected to run a query that requires 8 auxiliary descriptors, a total of 80 will be needed at any one time.
Add the per-query results to calculate the number of needed auxiliary scan descriptors.
