Release Total Server Memory in SQL 2008
-
Can anyone provide me the contents on how to release/reset Total Server memory in SQL Server 2008 without restarting SQL Services?
I am using DBCC FREEPROCCACHE, DBCC FREESESSIONCACHE. But its not working here.
Regards
sqldba4u
-
lower the max memory and then raise it again
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
sqldba4u (9/6/2012)
Can anyone provide me the contents on how to release/reset Total Server memory in SQL Server 2008 without restarting SQL Services?Can I ask why? If you are looking to test a query with a 'cold start', this is the normal routine:
-- Clear plans cache
DBCC FREEPROCCACHE;
-- Flush all dirty buffer pool pages to disk
CHECKPOINT;
-- Remove clean buffer pool pages
DBCC DROPCLEANBUFFERS;
-
There is a requirement to reset SQL Server memory without restarting SQL Services. And the below commands are not doing the needful.
-
CHECKPOINT is a per database operation so you may need to run for multiple databases being attempting to free cache
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
sqldba4u (9/6/2012)
There is a requirement to reset SQL Server memory without restarting SQL Services.Why?
-
sqldba4u (9/6/2012)
There is a requirement to reset SQL Server memory without restarting SQL Services. And the below commands are not doing the needful.Why? That's going to make things run slower until SQL reallocates all its memory, repopulates the data cache and recompiles all the plans it had cached.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Things are even much slower. So just wanted to know how we can release memory without restarting SQL.
-
Releasing memory won't make things fast. It's more likely to make things slow.
SQL uses a lot of memory, that is how it works. It does not run slowly when it has lots of memory, it does not have a memory leak. It uses memory for data so that it doesn't have to go to the (slow) disks. It uses memory for query plans so that it does not have to spend time regenerating those query plans.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
sqldba4u (9/6/2012)
Things are even much slower. So just wanted to know how we can release memory without restarting SQL.That's what we've been trying to tell you.
SQL Server is designed to use all memory available,and puts a lot of stuff in caches so that it can serve the data for subsequent requests faster.
if you don't set the maximum limits in SQL, the SQL server will use all available memory, which can starve the operatng system for memory.
if you need more memory for processes outside of SQL, set the max memory. it's a good practice to leave a gig or more for the OS, depending on how much total memory you have.
otherwise, doing things like clearing cache just makes SQL rebuild all the stuff that was serving data quickly.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Freeing the buffers and cache will make EVERYTHING in SQL Server run slower.
What exactly is running slow? Is it sql commands or OS or both?
How much memory is on the server?
How much is allocated to SQL Server?
Is there any paging going on with this server? This is a good indication that the server is starved for memory. If there is paging, then everything on that server is coming to a crawl.
Is the server slow during certain times of the day? If so, what is running during those times?
SQL Server itself is a memory hog. Everything requires memory, with so much memory allocated to users, locks, processes, buffers, etc.
I think that you are going to find that there are a combination of items which need to be identified and resolved when it comes to memory.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan -
sjimmo (9/6/2012)
Freeing the buffers and cache will make EVERYTHING in SQL Server run slower.Not quite everything. When you checkpoint a database and then run DBCC DROPCLEANBUFFERS it will only flush the buffer pool of buffers for the database in which you ran checkpoint since those buffers have been flushed to disk. Other databases on the server will likely still remain in cache.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
which is why I didn't say checkpoint;-)
Checkpoints are database specific and not server wide. I think that this case is going to turn out to be not enough memory and/or memory configuration
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan -
Max and Min memory have been setup and few GBs are left for OS. Clearing up cache is not releasing server memory.
-
sqldba4u (9/7/2012)
Max and Min memory have been setup and few GBs are left for OS. Clearing up cache is not releasing server memory.What do you mean? What are you hoping to see?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply