2

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.

enter image description here

  • You can connect to all of them and see what databases are running inside the instances. If you don't see anything important you can stop the service (or delete the instance). – eckes Apr 24 '17 at 2:53
3

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.

| improve this answer | |
  • 1
    Thank you for all the info! Come to find out, I had a few instances running I must have accidentally created when I was first installing SQL Server. I was able to disable them, and my cpu and ram usage are much better, thanks again! – Void Apr 24 '17 at 5:12
0

To shutdown a instance of SQL Server you should follow the MS docs found here:

To stop a named instance of SQL Server:

On the Start menu,

point to All Programs,

point to Microsoft SQL Server (version),

point to Configuration Tools, and then click SQL Server Configuration Manager.

In SQL Server Configuration Manager, expand Services, and then click SQL Server.

In the results pane, right-click the named instance of SQL Server, and then click Stop.

A red box on the icon next to the server name and on the toolbar indicates that the server stopped successfully.

Click OK.

| improve this answer | |
  • Thanks for your reply, I was able to fix my issue using this method. – Void Apr 24 '17 at 5:14
0

For CPU consumption try these resources:

For memory usage read these resources. It can be due your 'max server memory (MB)' setting even there are no activity in the server.

| improve this answer | |

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.