To calculate the amount of virtual shared memory that the database server might need for sorting, estimate the maximum number of sorts that might occur concurrently and multiply that number by the average number of rows and the average row size.
For example, if you estimate that 30 sorts could occur concurrently, the average row size is 200 bytes, and the average number of rows in a table is 400, you can estimate the amount of shared memory that the database server needs for sorting as follows:
30 sorts * 200 bytes * 400 rows = 2,400,000 bytes
You can use the DS_NONPDQ_QUERY_MEM configuration parameter to configure sort memory for all queries except those queries that have a PDQ priority of 0 (zero). The minimum and default value of DS_NONPDQ_QUERY_MEM is 128 kilobytes. The maximum supported value is 25 percent of DS_TOTAL_MEMORY. For more information, see More Memory for Queries with Hash Joins, Aggregates, and Other Memory-Intensive Elements.
If the PDQ priority is greater than 0, the maximum amount of shared memory that the database server allocates for a sort is controlled by the memory grant manager (MGM). The MGM uses the settings of PDQ priority and the following configuration parameters to determine how much memory to grant for the sort:
For more information about allocating memory for parallel processing, see Allocating Resources for Parallel Database Queries.
Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]