March 11, 2009 at 7:58 am
Sql2k5 Standard edition, SP2
A time out occurred while waiting for memory resources to execute the query. Rerun the query.
They seem to be intermittent, and I haven't much of a clue what to start looking for.
In PerfMon, I get some Memory Grants Outstanding in SqlServer:Memory Manager counter
The following code snippet returns 10-15 records when the timeouts are happening:
select *
from sys.dm_exec_query_memory_grants mg
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle)--join sys.dm_exec_query_plan qp on qp.plan_handle = mg.plan_handle
where is_next_candidate is not null
At the last run of this, 2 stored procedure calls appear in those records more than once, and another one in there is sp_sqlagent_get_perf_counters. I am not sure if the 2 SPs are specifically at fault or whether they are in there due to being frequently called by an application.
Task Manager shows 3 sqlserver.exe processes (one of them is a Sql2000 instance on the same server). The other 2 I believe are the Sql2k5 instance, one running under the SYSTEM user, and one running under Tranbkup user, which is the account our Sql Agent runs under, and it seems to have a high memory reading. I've never noticed this extra process in the list when investigating other problems.
Proflier is throwing up a lotof Execution Warnings with this sort of thing in (although in XML format/brackets rather than square brackets):
[Grant]
[SPID]144[/SPID]
[BatchId]0[/BatchId]
[SQLHandle]0x03002A00A5D26650570BBF00DC9A00000100000000000000[/SQLHandle]
[PlanHandle]0x05002A00A5D26650B8610F18000000000000000000000000[/PlanHandle]
[MemoryRequested]164[/MemoryRequested]
[MemoryGranted]0[/MemoryGranted]
[MemoryUsed]0[/MemoryUsed]
[DOP]0[/DOP]
[TimeOutInMS]25000[/TimeOutInMS]
[MemoryRequestTime]2009-03-11 13:51:23.05[/MemoryRequestTime]
[MemoryGrantTime][/MemoryGrantTime]
[/Grant]
also, there's a fair bit of activity in Profiler from the Tranbkup user, which is something I've not noticed before either
The 2 SPs are most likely not optimsed, but nonetheless have been chugging away with no problems upto today
I'm not a DBA and feel very lost - any pointers as to what to look at next would be very much appreciated
I am of course more than happy to provide any further info
Thanks
March 11, 2009 at 8:15 am
Do you know how much RAM you have on the server, What is the Pagefile Size?
Also are there SPs that are running quite often with SET NOCOUNT ON option missing?
I had the same problem one week back. I had to restart my SQL Server. I also used the oppurtunity to increase the RAM on my server.
This is definetly a lack of memory issue. Check your SQL Error Log. It will give more details regarding the error.
-Roy
March 11, 2009 at 8:23 am
Thanks Roy
Roy Ernest (3/11/2009)
Do you know how much RAM you have on the server, What is the Pagefile Size?
2GB RAM on the server - I'm not sure where to check the pagefile size?
Also are there SPs that are running quite often with SET NOCOUNT ON option missing?
Most likely!
I had the same problem one week back. I had to restart my SQL Server. I also used the oppurtunity to increase the RAM on my server.
This is definetly a lack of memory issue. Check your SQL Error Log. It will give more details regarding the error.
Management studio has just frozen and I can't look at anything! The same timeout error occurred when I tried to expand the treeview of database names
March 11, 2009 at 8:52 am
Pagefile you can see it on the server itself. That is set up by the people who install the OS.
I have a feeling that your option are very limited.
1. Restart the server
2. Give a bigger page file
3. Add SET NOCOUNT ON on all Sps
4. Add Additional RAM.
This is just my 2 cents. Maybe someone else can give you more advice regarding this.
-Roy
March 11, 2009 at 8:58 am
Thanks for your time Roy.
The service is being restarted now.
A colleague dropped a database earlier this morning (without any problems as far as he could tell), but for some reason there is a DROP DATABASE appearing in the sp_who2 results. it is RUNNABLE and seems to have been active a long time - and has been using up a fair amount of memory.
I can't think why a DROP DB command would cause all this though!
March 11, 2009 at 9:55 am
Right - the server had to be rebooted in the end, as shutting down the service didnt run cleanly.
We're up and running normally now. All I can see in the Sql Server log to do with the SPID that dropped the database was:
SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
that's all there was in fact, until the timeout errors started appearing
I did a spot of googling about this, but am none the wiser
March 11, 2009 at 10:10 am
I have never seen these errors before. But it is talking about the Cacheplan which is usually in the memory.
-Roy
March 11, 2009 at 11:09 am
Dropping a database flushes the caches for that database - the information can no longer be used. Those messages are normal.
March 11, 2009 at 11:11 am
Matt, when you drop database it is supposed to flush them. But the error log shows that it is not able to do it. Or atleast that is what I understood from the error log.
-Roy
March 11, 2009 at 11:14 am
2GB memory is not enough for most production SQL server enviornments. Those memory flush means you have memory contention issue on your server. When SQL Server starts it will take memory as it think it needs it.
But if the OS complains it will release it by flushing the buffer pools. Few things you should do right away...
1) Change the Min/Max memory settings of SQL Server. By default min is set to 0 and max is set to 2147483647. This should be changed to 512 and 1536.
2) Check your Server Memory Available, Page Life Exp. and Page hit ratio, Page faults, etc memory counters to see how your server is sitting.
Most likely you need up the memory on the server.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 11, 2009 at 11:22 am
matt stockham (3/11/2009)
Dropping a database flushes the caches for that database - the information can no longer be used. Those messages are normal.
Thanks.
So there is one plan cache per database then? I don't know why I thought there was one plan cache for all the DBs on an instance.
It's just that the SPID that dropped the database was the one that seemed to be hogging all the memory.
My colleague who performed the DROP said it seemed to take rather a long time to complete executing. Yet according to sp_who2 it was still running (CPU time and memory were increasing)
The log holds only this info:
09:02 - various log backups
09:29 - the 3 cachestore flush statements mentioned above
11:35 - timeout errors
March 11, 2009 at 11:28 am
Thanks for your response Mohit
Mohit (3/11/2009)
2GB memory is not enough for most production SQL server enviornments.
Our databases are not at all large - 10000 rows in the bigger tables, maybe
Those memory flush means you have memory contention issue on your server. When SQL Server starts it will take memory as it think it needs it.
But if the OS complains it will release it by flushing the buffer pools. Few things you should do right away...
1) Change the Min/Max memory settings of SQL Server. By default min is set to 0 and max is set to 2147483647. This should be changed to 512 and 1536.
Thanks, it was indeed set to the default
2) Check your Server Memory Available, Page Life Exp. and Page hit ratio, Page faults, etc memory counters to see how your server is sitting.
I will monitor these tomorrow, however the server has been working fine (despite it's suboptimal set-up) up until now.
I am at a loss as to what set off all the memory contention
March 11, 2009 at 11:37 am
Roy : I was just referring to these errors -
SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
They are just warning messages to indicate that flushing has occurred.
mazzz (3/11/2009)
matt stockham (3/11/2009)
Dropping a database flushes the caches for that database - the information can no longer be used. Those messages are normal.Thanks.
So there is one plan cache per database then? I don't know why I thought there was one plan cache for all the DBs on an instance.
My comment was poorly worded in that respect - what I meant to say was that data pertaining to that database is flushed from the caches. You are correct that there is only one plan cache per instance.
March 11, 2009 at 11:40 am
March 11, 2009 at 11:44 am
And it was my mistake that I did not look at the log properly and see that it was just the warning. I think I read what my mind wanted it to read. Sorry Matt.
-Roy
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply