CODEWITHSUNDEEP
sql-server
OlduwanSteve
OlduwanSteve

Reputation: 1295

Why would reducing "max server memory" in SQL Server improve my application performance?

I have an intermittent problem with one area of my application where it starts running slow. The application is ASP.Net+ with SQL Server. We discovered by chance that making any change to the "max server memory" in SQL server, up or down, will immediately stop the problem.

The problem area is quite dynamic, and being updated simultaneously by many threads, so my current best guess to explain this is some kind of deadlocking problem in the DB. Unfortunately it happens very rarely, only on a live system and when it does happen we have to fix it quickly. As there is not a lot of opportunity for 'digging' I want to narrow my search and this oddity seems like a clue.

I have googled this, and looked in "Books Online" so I know that "max server memory" is not a setting that requires a restart. What I can't find is any detailed description of what happens when RECONFIGURE is called 'under the hood'.

My hope is that somebody here knows, or can point me at a more detailed resource. Just to be clear, I'm not asking for general advice so please try to stay specific. Thanks :)

Update:

Calling DBCC FREEPROCCACHE does not have any effect on the problem, while bumping the memory continues to fix the symptoms immediately.

Upvotes: 1

Views: 1344

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294317

You have a performance problem and you should approach it by investigating the performance, not by guessing. Follow a performance troubleshooting methodology like Waits and Queues to identify the bottleneck, then take appropriate actions depending on the identified issue.

As to your speculations about 'internal deadlocks' they are ... creative. A more realistic explanation would be that by messing with server settings you're tickling the clock hands which evict your bad plan.

Upvotes: 2

Related Questions