In my installation of SQL Server 2012 on my development machine (Win8.1), the process "SQL Server Windows NT - 64 Bit" is running continuously, using up disk resources and filling up my memory over time, even though I'm doing nothing. If I reboot the computer or restart the service called SQL Server (MSSQLSERVER), then the memory allocation is reset and immediately begins to accumulate again.
How can I identify the operation that SQL Server is running to cause this usage? I don't recall changing any operations or configuration.
Edit: The problem is not just that memory is filled, but also that it continually uses half my disk operation speed (the "Disk" column in Task Manager Processes is at 50-75%). There appears to be an ongoing operation that perhaps has a memory leak. In other words, I am doing no activity and sitting watching the Task Manager and the memory is going up steadily.
-
3SQL Server operates a sensible "If there are available memory resources then I'll use them" policy by default.Alex K.– Alex K.2015年10月08日 13:10:59 +00:00Commented Oct 8, 2015 at 13:10
-
2You need to change settings at installation time - namely the "max server memory" that you allow SQL Server to use. If you don't specify anything, it'll use up all your memory, if it can.....marc_s– marc_s2015年10月08日 13:11:34 +00:00Commented Oct 8, 2015 at 13:11
-
Please see the edit, there is a disk operation happening in the background, and I don't know what it is. It's affecting my CPU.Savage– Savage2015年10月08日 13:16:49 +00:00Commented Oct 8, 2015 at 13:16
-
If this is the wrong forum for this question, please advise where I should post, thanks.Savage– Savage2015年10月08日 13:17:45 +00:00Commented Oct 8, 2015 at 13:17
-
Not really. SQL Server only acquires memory from the OS if there are active workloads that need more than what it currently has. SQL Server does not acquire memory just because it is available. Even if you set the min memory setting, it will only acquire if needed. Key difference here is it won't release the memory below min once acquired.SQLmojoe– SQLmojoe2015年10月08日 17:28:51 +00:00Commented Oct 8, 2015 at 17:28
1 Answer 1
SQL Server does not use up memory without a workload running. I.E. an instance of SQL Server will consume the bare minimum it requires to keep the engine alive, listen for connections and handle background tasks (e.g. resource management). That's usually less than 100MB. If you have a min memory setting then it'll acquire that minimum IF there's demand because of workload and then sit there; won't release beyond the minimum even after the workload is done.
If your SQL Server process (sqlservr.exe in task manager or process monitor) is consuming lots of memory, something is going on. Check what's going on in the server using SELECT * FROM sys.dm_exec_sessions and look at the login_name column to see who's connected. That's a start to identify potential unknown workloads. From there, the other columns will provide information on how much resources it's using. It could be a real user, machine or even some old SQL Agent job gone wild.