March 23, 2017 at 6:13 am
hi
we have a sql 2005 std instance on server 2008 Ent 32 bit
we had only 8gb of memory until recently on this server, but decided to add more (32gb in total now) to see if it helped some performance issues
we have AWE enabled and the sql db account has been configured to lock pages in memory, sql is configured to have 25gb of ram
but now i see "resource semaphore" waits appearing, which we where not getting before hand
anyone any ideas?
mal
March 23, 2017 at 7:42 am
dopydb - Thursday, March 23, 2017 6:13 AMhiwe have a sql 2005 std instance on server 2008 Ent 32 bit
we had only 8gb of memory until recently on this server, but decided to add more (32gb in total now) to see if it helped some performance issues
we have AWE enabled and the sql db account has been configured to lock pages in memory, sql is configured to have 25gb of rambut now i see "resource semaphore" waits appearing, which we where not getting before hand
anyone any ideas?
mal
What might help is to check sys.dm_exec_query_memory_grants to see what queries are using up what memory grants.
There is a pretty good blog about this wait type and the process for memory grants on SQL 2005 in this article:
Understanding SQL server memory grant
The end of the article has a couple of good queries to track down some of the memory hungry queries.
Sue
March 23, 2017 at 7:48 am
hi Sue
thanks, i will have a look, but is it not strange that these have suddenly started appearing just after we add memory?
mal
March 23, 2017 at 7:56 am
dopydb - Thursday, March 23, 2017 7:48 AMhi SScthanks, i will have a look, but is it not strange that these have suddenly started appearing just after we add memory?
mal
Yeah that's what I thought at first. Maybe some code changes. Another thing to check would be stats. Issues with those can sometimes suddenly appear. On the application side, code paths can sometimes change as well and throw things off. Or maybe some change in data in one or more tales - could be needing indexes.
Sometimes you can figure it out when you find the queries eating up the grants.
Sue
March 23, 2017 at 8:21 am
ok thanks sue
going to keep a close eye over next few days
March 23, 2017 at 11:50 am
While it's not in SQL, do you have Physical Address Extension enabled in the OS?
https://msdn.microsoft.com/en-us/library/windows/desktop/aa366796(v=vs.85).aspx
It could be (if it's not enabled) that you're now starving the OS of RAM, leading to the waits you're now seeing. You weren't getting them before because presumably either you had the max memory for SQL set lower, or because you didn't have lock pages enabled (which, if this is the right track, means SQL is now locking memory, the OS is starving and forcing SQL to give up RAM, rinse and repeat.)
March 24, 2017 at 4:27 am
hi Jasona
yea PAE was already configured as we had 8gb ram
now we haev 32 gb and sql is setup for max 25
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply