2

I'd like to understand how tuning works on instances that are living inside the same machine/VM.

I couldn't find a straight answer to the question if properties of each separate instance have some impact on hardware specs of the machine. Here's what I mean: take a server with 8 cores and 24GB RAM, for example, running 2 instances. If I set the max server memory to 10GB for each instance, Cost Threshold for Parallelism to 50 (to enable/set better multithread) and Max Degree of Parallelism to 3 cores, does that mean that both instances will use

  • 3+3=6 cores so 2 cores for the OS?
  • 10+10=20GB so +4Gb for the VM?
  • Cost Threshold for Parallelism so be set to 25+25 ?

I haven't found anywhere this kind of info and it would be nice to know this for better customization of instances I currently run, and for future reference as well.

3

We have multiple servers running multiple instances where I work. The assumption is, that using one piece of hardware for multiple instances, will work pretty good for smaller databases that are used once or twice a day.

This also allows us to run individual instances with a dedicated IP address for certain departments/councils/customers.

The big advantage is the licensing as Microsoft SQL Server permits you to install multiple SQL Server instances using the same one license. This depends on the Edition (Standard / Enterprise) and the CAL or licensing agreement you have signed with your reseller.

Prior to SQL Server 2000 (8.x), only one instance of SQL Server could be installed on a computer. SQL Server listened for incoming requests on port 1433, assigned to SQL Server by the official Internet Assigned Numbers Authority (IANA). Only one instance of SQL Server can use a port, so when SQL Server 2000 (8.x) introduced support for multiple instances of SQL Server, SQL Server Resolution Protocol (SSRP) was developed to listen on UDP port 1434. This listener service responded to client requests with the names of the installed instances, and the ports or named pipes used by the instance. To resolve limitations of the SSRP system, SQL Server 2005 (9.x) introduced the SQL Server Browser service as a replacement for SSRP.

Reference: SQL Server Browser Service (Microsoft | SQL Docs)

However, the SQL Server instances are all running on one Windows Server and the Windows Server distributes the "consolidated" resources of the underlying "hardware" (physical or virtual), be they:

  • Disk I/O or SAN I/O
  • RAM
  • CPU
  • etc...

Solutions

You can configure the instances to run on certain CPUs with the Resource Governor.

You can configure the SQL Server instances to use only a certain amount of RAM and if there is enough RAM, then each instance will normally use different memory spaces. However, in a virtual environment e.g. VMware the host system can decide to assign the same RAM as other executables.

What doesn't work

Setting max degree of parallelism (MAX_DOP) to a value of 3 will have no effect, as this is the setting for parallel execution of e.g. index scanning/seeking. A SQL Server instance will run on all available CPU cores, regardless of the MAX_DOP setting.

cost threshold for parallelism (CTFP) settings will only have an effect on the level at which the Query Optimizer might decide to use parallel processing for queries.

Personal Recommendations

Based on my current experience with +1000 databases running on +400 instances on +100 servers, I would say that there are cases where it can work.

But in other cases with large databases and a large amount of users querying lots of data the whole day, you can reach a limitation in the available performance for a given instance, which will force you to install a single instance for a single database. This would be the opposite of what you are trying to achieve.

You will have to build up knowledge of your databases, the way they are queried and be able to determine when a database is performing well or not. Visiting Performance Tuning workshops can assist you in achieving these goals.

That's the best advice I can give you to your general question.

Additional Considerations Based on Comment

Well I wouldn't put two "small" database in separate instances, unless you require some sort of real separation, because users or programs are using sa to login to the database or because you require some form of network separation.

Security / Legal / Audit

There may be legal / audit requirements where you have to separate the instances, just to ensure that one person can't "accidently" access a different database.

2
  • Thank you a lot for the long informative answer of yours. The Databases are around 12-15Gb each year so they are considered small (not even medium) and the users accessing them (of course it matters the queries they are running) are 2 accountants for one instance (ERP based one) and 17-22 for the other instance for the CRM. I dont know if in my case would be better to hang the 2nd database in the same instance instead, but I am not a dba, I just setup Linux hypervisors, VMs AD...etc . Not my field but then again I am one guy for all IT jobs. – ieronymous Jun 9 at 17:45
  • responded to comment in answer. – John K. N. Jun 10 at 6:59
2

Yes that's true you can configure your two SQL Server instances to share resources that way, but keep in mind they'll potentially be sharing any other resources they're bound by as well, such as I/O, disk space, or network bandwidth.

It's not normally recommended to install multiple instances on the same server (though it is possible to manage), since SQL Server is designed in such a way that it is meant to consume all the resources available to it, so it can perform most optimally internally.

3
  • Especially since you should be using Virtualization, giving each instance it’s own VM is usually preferable. – David Browne - Microsoft Jun 8 at 16:29
  • Thank you for the info. It seems it would be a good idea to inform you first about the condition in which I found the company, in order to feel sympathy and agree why even what I am trying to do is considered to be an improvement. So it is an old Dell Serv Poweredge with 4 cores and 24 gb ram (DDR2 era) upon which exists the roles of AD/DNS/DHCP/3 SQL instances/ File server So, having separated all the above in 3 licensed VMs on the new workstation/server, at least for me is considered an upgrade :) Also cost is a big issue for the company. – ieronymous Jun 9 at 17:32
  • @ieronymous Honestly, I'm glad to hear you at least split the 3 types of servers, because the risk of all of that on one (plus the general issues) is probably more expensive in the long run than the licensing lol. In any case, the licensing on SQL Server can be by CPU core, and therefore shouldn't cost you any more if each instance needed 2 cores vs using a 6 core server for all 3 instances on the same machine (since you have to split the resources one way or another). But the licensing for the VM / OS is a different story that I couldn't advise much on. – J.D. Jun 10 at 1:01

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.