SQLTeam.com | Weblogs | Forums

Reducing SQL Server memory takes a LONG time

sql2008

#1

I have a SQL Server instance with 128GB of RAM that doesn't have a memory cap on it. Well, it had a cap but it was at 128000 so it wasn't doing much good.

We've had issues reducing memory before so I was going to go slow. I reduced that from 128000 to 126000. That took 45 minutes. The server was fine during that time. I continued to work but it caused me a fair amount of grief.

I've had it run quickly and I've had it take a while. Anyone had anything similar?

I'm SQL Server 2008 Enterprise x64 Enterprise w/128GB RAM and 4 cores on a VM. Nothing is oversubscribed.


#2

I've just always reduced it to the amount I wanted and then "walked away", never even paid attention to how long it takes. On rare occasions where I wanted it to happen fast due to a competing SQL instance on the same server, I restarted the service.


#3

I have had to reduce the MAX MEMORY setting only couple of times in my entire life. In my limited experience/research I did then, SQL Server releasing memory is the result of a combination of the OS asking for memory to be released and SQL Server having memory to release. In other words, if OS needs memory and asks SQL Server to release memory, it would not if it does not have free memory. Similarly, if it has free memory (even if you have reduced the max memory setting), it takes its own sweet time releasing the memory if OS doesn't ask for it.

If by "fair amount of grief" you meant that OS needed memory, but SQL wouldn't give it, then the only reason that I can think of is that SQL needed the memory for some process/job it was running.


#4

In the case where SQL isn't releasing it because it thinks it needs it and the OS wants it, I'd probably free the procedure cache and drop the clean buffers on a database. I think that would make SQL release it faster, right?