0

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.

5
  • 3
    SQL Server operates a sensible "If there are available memory resources then I'll use them" policy by default.
    – Alex K.
    Oct 8 '15 at 13:10
  • 2
    You 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
    Oct 8 '15 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
    Oct 8 '15 at 13:16
  • If this is the wrong forum for this question, please advise where I should post, thanks.
    – Savage
    Oct 8 '15 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
    Oct 8 '15 at 17:28
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.