General troubleshooting steps in case of memory errors :
The following list outlines general steps that will help you troubleshoot memory errors.
Verify if the server is operating under external memory pressure. If external pressure is present, try resolving it first, and then see if the problem/errors still exist.
Start collecting performance monitor counters for SQL Server: Buffer Manager, SQL Server: Memory Manager.
Verify the memory configuration parameters (sp_configure), min memory per query, min/max server memory, awe enabled, and the Lock Pages in Memory privilege. Watch for unusual settings. Correct them as necessary. Account for increased memory requirements for SQL Server 2005.
Check for any nondefault sp_configure parameters that might indirectly affect the server.
Check for internal memory pressures.
Observe DBCC MEMORYSTATUS output and the way it changes when you see memory error messages.
Check the workload (number of concurrent sessions, currently executing queries).
Memory errors :
701 - There is insufficient system memory to run this query.
Causes
This is very generic out-of-memory error for the server. It indicates a failed memory allocation. It can be due to a variety of reasons, including hitting memory limits on the current workload. With increased memory requirements for SQL Server 2005 and certain configuration settings (such as the max server memory option) users are more likely to see this error as compared to SQL Server 2000. Usually the transaction that failed is not the cause of this error.
Troubleshooting
Regardless of whether the error is consistent and repeatable (same state) or random (appears at random times with different states), you will need to investigate server memory distribution during the time you see this error. When this error is present, it is possible that the diagnostic queries will fail. Start investigation from external assessment.
Possible solutions include: Remove external memory pressure. Increase the max server memory setting. Free caches by using one of the following commands: DBCC FREESYSTEMCACHE, DBCC FREESESSIONCACHE, or DBCC FREEPROCCACHE. If the problem reappears, reduce workload.
802 - There is insufficient memory available in the buffer pool.
Causes
This error does not necessarily indicate an out-of-memory condition. It might indicate that the buffer pool memory is used by someone else. In SQL Server 2005, this error should be relatively rare.
Troubleshooting
Use the general troubleshooting steps and recommendations outlined for the 701 error.
8628 - A time out occurred while waiting to optimize the query. Rerun the query.
Causes
This error indicates that a query compilation process failed because it was unable to obtain the amount of memory required to complete the process. As a query undergoes through the compilation process, which includes parsing, algebraization, and optimization, its memory requirements may increase. Thus the query will compete for memory resources with other queries. If the query exceeds a predefined timeout (which increases as the memory consumption for the query increases) while waiting for resources, this error is returned. The most likely reason for this is the presence of a number of large query compilations on the server.
Troubleshooting
1. Follow general troubleshooting steps to see if the server memory consumption is affected in general.
Check the workload. Verify the amounts of memory consumed by different components.
Check the output of DBCC MEMORYSTATUS for the number of waiters at each gateway (this information will tell you if there are other queries running that consume significant amounts of memory).
Small Gateway Value
------------------------------ --------------------
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
(6 row(s) affected)
Medium Gateway Value
------------------------------ --------------------
Configured Units 2
Available Units 2
Acquires 0
Waiters 0
Threshold Factor 12
(5 row(s) affected)
Big Gateway Value
------------------------------ --------------------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Reduce workload if possible.
8645 - A time out occurred while waiting for memory resources to execute the query. Rerun the query.
Causes
This error indicates that many concurrent memory intensive queries are being executed on the server. Queries that use sorts (ORDER BY) and joins may consume significant amount of memory during execution. Query memory requirements are significantly increased if there is a high degree of parallelism enabled or if a query operates on a partitioned table with non-aligned indexes. A query that cannot get the memory resources it requires within the predefined timeout (by default, the timeout is 25 times the estimated query cost or the sp_configure ‘query wait’ amount if set) receives this error. Usually, the query that receives the error is not the one that is consuming the memory.
Troubleshooting
Follow general steps to assess server memory condition.
Identify problematic queries: verify if there is a significant number of queries that operate on partitioned tables, check if they use non-aligned indexes, check if there are many queries involving joins and/or sorts.
Check the sp_configure parameters degree of parallelism and min memory per query. Try reducing the degree of parallelism and verify if min memory per query is not set to a high value. If it is set to a high value, even small queries will acquire the specified amount of memory.
To find out if queries are waiting on RESOURCE_SEMAPHORE
8651 - Could not perform the requested operation because the minimum query memory is not available. Decrease the configured value for the 'min memory per query' server configuration option.
Causes
Causes in part are similar to the 8645 error; it may also be an indication of general memory low condition on the server. A min memory per query option setting that is too high may also generate this error.
Troubleshooting
1. Follow general memory error troubleshooting steps.
Verify the sp_configure min memory per query option setting. :
No comments:
Post a Comment