I am fairly new to MS SQL Server and am curious as to what each instance of SQL Server Windows NT does. I currently have 4 of them running, and they are consuming most of my test server's resources. A few days ago my cpu would idle around 0~5% usage even with the 4 instances of SQL Server Windows NT running, but now they've gone wild and are using up almost all of my cpu and ram. (See image below)
I am only running 1 database as far as I know, and was curious what would happen – or if I could, end the task on 2 or 3 of these.
I also tried restarting my database, but they all came right back.
Best Answer
When you install SQL Server it installs some shared components and also some components that are specific to the instance itself. In its simplest form, an instance can be likened to a virtual machine or a container running on a host server. You can have multiple instances but each one requires it's own resources and has it's own limitations in terms of how many databases may be created. Check out the Microsoft docs to understand more about instances and shared components.
Something to be aware of especially if you are new to SQL Server or come from an Oracle background is that you do not need to install more than one instance to have more than one database. Each instance of SQL Server can have up to 32767 databases on it. More information on limits can be found on Microsoft Docs.
A common scenario for having multiple instances is when you need to develop against different versions of SQL Server. For example, on my one of my development workstations I have 2008, 2008R2, 2012 and 2014 installed. However, I normally would not have them all active at one time.
From your question above it sounds like you don't have many databases so unless you need to isolate a development environment (such as having two separate 2014 instances e.g. one DEV and one QA) or support multiple versions of SQL Server, I would actually recommend uninstalling the instances that you don't require - not only to free up CPU and RAM but also to free up any disk space that was used. Be sure to back up anything you may need first. To uninstall SQL Server follow these instructions.
Alternatively you can disable the service's so that they do not automatically start when you boot your machine. The instructions on how to do this can be found here. The only side-effect to stopping the service is that you will no longer be able to connect to the instance. But you could easily restart it when you do need it.
If you do require all instances to be running at the same time, then I would suggest setting a limit to how much RAM and CPU resources are allocated to each instance. I suggest looking at the documentation on how to set the max memory and processor affinity. However, as a new user to SQL Server I would recommend one of the solutions I proposed earlier over changing server properties until you understand them better.