0

I have inherited a Network and Server environment recently. I have SQL Server 18 on one server, at the moment reports are very slow to be created, I went to the server instance and noticed that the memory has maxxed out, my logic is telling me to clear the SQL cache, however I am not sure where to go next (I am not an SQL expert) Details below,

SQL Server Management Studio 15.0.18384.0 SQL Server Management Objects (SMO) 16.100.46367.54 Microsoft Analysis Services Client Tools 15.0.19535.0 Microsoft Data Access Components (MDAC) 10.0.17763.1 Microsoft MSXML 3.0 6.0 Microsoft .NET Framework 4.0.30319.42000 Operating System 10.0.17763

Memory Info Link below enter image description here

Salahuddin Ahmed
  • 4,854
  • 4
  • 14
  • 35
StuinDresden
  • 21
  • 1
  • 2
  • Your version of SSMS is irrelevant - and that is the version information you posted. How exactly did you determine that memory is "maxxed out" and what "logic" did you use to determine that some "cache" needs to be cleared. – SMor Jun 02 '21 at 12:13
  • Clearing SQL Server's buffer pool or plan cache is typically pointless. The engine manages this memory automatically; clearing it out just means the next big query that requires a lot of memory will claim it right back again. If memory is an issue, messing around with the buffer pool is not the solution. Note that SQL Server, unlike some engines, does *not* cache query results: only data that would otherwise be read from disk. As a result, clearing the buffer pool usually results in nothing but a temporary slowdown and no reduction in memory use or boost in performance. – Jeroen Mostert Jun 03 '21 at 11:58

1 Answers1

-1

Fortunately, SQL Server provides us with the undocumented stored procedures to do the job.

To clear the cache, run the set of following commands as below:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
Dharman
  • 30,962
  • 25
  • 85
  • 135
Salahuddin Ahmed
  • 4,854
  • 4
  • 14
  • 35