We may get this error message when there is high workload on the server. And servers are experiencing high memory pressure.
In this error there are some additional symptoms also.
1. When connecting to server will get the error message as "Login Failed".
2. Will get disconnected from server.
3. CPU usage will be very high.
4. if running "select * from sysprocesses" SPIDs will have a waittype of 0x40 or 0x0040 and a last_waittype of RESOURCE_SEMAPHORE.
5. The System Monitor object SQLServer:Memory Manager displays a non-zero value for Memory Grants Pending.
6. SQL Profiler displays the event "Execution Warnings" that includes the "Wait For Memory" or the "Wait For Memory Timeout" text.
Reasons for this error is memory intensive queries are getting qued and are not getting resources before timeout period. And after timout period and getting timeout. By default query wait period is -1 by setting non-negative number you can improve the query wait time.
Other reasons for this errors are not properly optimized queries, memory allocation for sql server is too small.
Solutions for this error include the following.
1. Optimize the performance of queries using sql profiler.
2. Distribution statistics should be uptodate.
3. Watch the system monitor trace to see the memory usage of sql server.
In this error there are some additional symptoms also.
1. When connecting to server will get the error message as "Login Failed".
2. Will get disconnected from server.
3. CPU usage will be very high.
4. if running "select * from sysprocesses" SPIDs will have a waittype of 0x40 or 0x0040 and a last_waittype of RESOURCE_SEMAPHORE.
5. The System Monitor object SQLServer:Memory Manager displays a non-zero value for Memory Grants Pending.
6. SQL Profiler displays the event "Execution Warnings" that includes the "Wait For Memory" or the "Wait For Memory Timeout" text.
Reasons for this error is memory intensive queries are getting qued and are not getting resources before timeout period. And after timout period and getting timeout. By default query wait period is -1 by setting non-negative number you can improve the query wait time.
Other reasons for this errors are not properly optimized queries, memory allocation for sql server is too small.
Solutions for this error include the following.
1. Optimize the performance of queries using sql profiler.
2. Distribution statistics should be uptodate.
3. Watch the system monitor trace to see the memory usage of sql server.
No comments:
Post a Comment