If SQL Server is running slow and operations are throwing errors due to lack of memory, it is necessary to look into memory issue. If SQL Server is restarted all the cache memory is automatically cleaned up. In production server it is not possible to restart the server. In this scenario following three commands can be very useful.
When executed following three commands will free up memory for SQL Server by cleaning up its cache.
DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
Reference : Pinal Dave (https://blog.sqlauthority.com)
31 Comments. Leave new
You didn’t mention that there could be a performance hit associated with clearing the system cache and procedure cache, as all requests after issuing DBCC FREESYSTEMCACHE will cause a plan recompilation as well as FREEPROCCACHE.
So people should be aware that executing these commands could cause a temporary decrease in performance.
If things are running slow and errors are occurring due to lack of memory on a production server – these commands are only short term band-aids and the real root of the problem needs to be found, analyzed, and fixed in order to have a well performing Production server.
Be VERY VERY careful when you run these commands in production environment – and make sure you know what you are doing.
Hi,
So when should we use these commands or when we should not? Will you explain me?
Thanks
Shailesh
is by executing these statements (written below) whatever our data is in sql will be removed if not then wht’s the query to remove all the date in once exection?
DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
Hi Pinal,
One of the interviewer asked me about the database refresh.as per my knowledge i thought of backup and restore.
is iam correct.
As you said earlier article we can refresh the database by below dbcc commands,is iam right?
The following commands clean only the plan cache:
DBCC FREESYSTEMCACHE(All | pool_name)
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FLUSHPROCINDB( db_id )
To clear the data buffer use the following command:
DBCC DROPCLEANBUFFERS
Hello Sir,
I have an issue with a server memory. SQL server is allocated 32 Gb out of 40 GB memory and sql server is using all the memory which is allocated to it.
The memory usage is gradually increasing to the max value that is allocated to SQL Server.
I have tried all ways but still no luck:
1. Killed all the open transactions.
2. Cleared the system cache.
3. Cleared session cache
4. Cleared procedure cache.
5. Checked for blocked transactions and killed them.
6. Ran DBCC opentran and found no open transactions.
The active connections to the server are as follows:
DatabaseName NoOfConnections LoginName
ReportServer 3 NT AUTHORITY\NETWORK SERVICE
master 21 sa
OLTP_QA_TEST 4 sa
KRApplication 1 sa
KYApplication 4 sa
DWH_QA_FINAL 1 sa
OLTP_QA_FINAL 2 sa
The Buffer info is as follows:
bpool_commit_target bpool_committed
I restrted the SQL server service and then memory went down but gradually it started increasing to theax value within one hour..
Can you please help me out in resolving this issue please.
Hey Suman,
only solution is..
just go to memory settings and set max value 1024MB and click apply
thn again go to memory settings set 32GB.. now see the sql server memory utilization.. u got it.. rit..
hey Srinath.. but I have hundreds of SP’s. This will hit on the performance then….
Hi Suman,
I have the same problem, can you share the solution?
Best Regards
Mo
yes. i have the same problem. SQL server memory keep on increases. i have 20 GB of RAM. at in 1 Hr SQL memory reaches 20 GB. server goes dead slow. still no clue.
Hi Friend,
I have the same issue earlier, we found that SQL server wont release the unused memory once it complete the transactions or backups and this is a know issue. And in our case we found that we have application services run through out the clock and sending the data in xml’s to vendor server which is out of box so here they are occupying my RAM memory and never coming down, these services hit the DB to query in the open orders so obviously its SQL memory caching issue. so we tunned the services in background now the system is stand and calm.
So please check the any such services or applications which are connecting or using DB to query in continually.
And one point i need to add here, if you are using Windows server 2008 R2 have issue with the cache memory release please find the below
Patch released;
http://www.microsoft.com/en-us/download/details.aspx?id=9258
And there is also a SP1 released for the same
Good luck.
Hi all,
Right now me also facing same problem. i already reboot the sql services 2 times but it could not resolved.
Hi friends,
I have sql 2008 R264 bit and 20 GB RAM but 19.7% ram is used by sqlservr.exe so any body know how to freez SQL 2008 R2 64 bit. Don’t tel me use AWE to allocate memory because it is not support on 64 bit
Hi
I have an issue with a server memory. SQL server is allocated 31 Gb out of 32 GB memory and sql server is using all the memory which is allocated to it.
The memory usage is gradually increasing to the max value that is allocated to SQL Server.
Any suggestions?
Hello sir
I am using Sql Server 2008 and want to write a procedure which can update the table as i fire it.
Suppose there is 100 data so at the time of updation the 100 data will be sequenced by datetime(this is one of the column data type) and when the another 5 data has been inserted and after then i fire that procedure it only sequenced the rest of 5 data no need to touch the data of 100 what i should have to do??
Dear All,
We are using a DB view in our project. This is the view which will be triggered many times in different reports. Initially data is coming fast but gradually the performance is getting down.
Can some one help us to improve the performance,
Thanks in advance.
Eswar
Hi There,
Basically you won’t be able to release physical memory of the server by running DBCC FREESESSIONCACHE,DBCC FREEPROCCACHE
In order to release the memory try to run following two queries:
1.
EXEC sys.sp_configure N’show advanced options’, N’1′ RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N’max server memory (MB)’, N’8000′
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N’show advanced options’, N’0′ RECONFIGURE WITH OVERRIDE
GO
Then run following:
2.
EXEC sys.sp_configure N’show advanced options’, N’1′ RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N’max server memory (MB)’, N’2000′
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N’show advanced options’, N’0′ RECONFIGURE WITH OVERRIDE
GO
hi pinal dave
one production server is there in that server total ram size is 32 gb.on that server one database occupy 31gb.what abt remaining databases. how to solve the performance issue
i am following frequantly your blog
Can we use the following commands in Production environment?
The following commands clean only the plan cache:
DBCC FREESYSTEMCACHE(All | pool_name)
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FLUSHPROCINDB( db_id )
To clear the data buffer use the following command:
DBCC DROPCLEANBUFFERS
Some body please give me the way to clear production buffer pool.
Regards,
Swami.
Dear Pinal Sir,
i am facing this error while synchronizing database from principle to mirror server.
“Database mirroring connection error 4 ‘10055(An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full.)’ for ‘TCP://xyz:5022’.” Please help me in this regards.
Thanks
Hi friends,
I have sql 2012 R1 64 bit and 12 GB RAM but 11.8 GB RAM is used by sqlservr.exe so any body know how to freez/Resolve the performance issue , as this is a higher priority and a challenging task to me in my company, where our client is very much disappointed with server performance. Please sujjest me with the solution to improve the performance and to make fast access to application we provide.
Note: client is not ready to increase the RAM size as per his concern there will be hardly 20-30 user’s to DATABASE(only 1 db).
try to set max memory used for sql server at 8 GB and minimum at 4 GB and then restart the server.
What will happen if I do execute the following commands in Live Server while memory usage reaches to ‘MAX MEMORY’ set.
Nothing would go wrong. SQL knows how to manage cache.
In My Production server My RAM memory shows 73 GB is used. In Proccess sql is taking around 66 GB. Does it always takes 66 gb, how to free memory when it is not using.