January 29, 2010 at 8:37 am
I have a large application that uses a pessimistic locking scheme that we implemented with application locks. A client came to us with the complaint that adding memory to the server slowed the application down, which we can reproduce on various machines. I've isolated it to the calls to sp_getapplock().
Limiting the memory SQL Server uses, using /burnmemory and physically removing memory from the machine all have the same affect: less memory = faster. If I disable the locking by bypassing calls to sp_getapplock(), the performance increases with additional memory like I expect.
Anyone have an explanation?
February 2, 2010 at 7:24 am
not seen this proc used for this before, and having read up on it am still no wiser. It did seem to indicate it would only run single threaded which might indicate the locks serialise.
More memory slowing down - maybe you need to tell us what version of sql and what memory we're talking about and how the resources are being configured.
Generally extra memory increases the buffer cache so merely decreases physical io, x64 is a bit different in as much as the procedure cache can grow quite large.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 3, 2010 at 11:19 am
Thanks Colin
Slowdown happens on both x32 and x64 versions of SQL Server 2008. Memory is either total physical memory on the machine (remove chips), limiting the available memory using /burnmemory, or limiting the maximum memory that SQL Server can use via the Maximum server memory option on the server properties.
February 4, 2010 at 3:22 am
sorry I'm still no wiser - I'd log a call with microsoft at least you should eventually get an answer.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply