0

I've one server with a database who stress the server and is blocking other db. Developer has optimized the query, but there we need more power. The SQL server is a VM in a vCloud Director tenant, we think two solution:

  • Two Instance on same server: we add a second instance on same VM. We double the system resource, in second instance we move only the stressed DB, limit CPU e RAM usage, so when the DB is in stress, the first instance have dedicated resource and there's no blocking query, when the second instance is down, the first instance has more resource... as another vantage I dont have to pay another SQL server license...
  • Two server: New dedicated server dedicated to stressed DB... as disadvantage I have to pay another SQL license and the resource are only dedicated to the single DB even when is not under stress...

At this time we think the two instance solution is the right way, we are wrong? There are disadvantage that we have not consider? What you think? Thanks in advance for response...

Stefano

4
  • 1
    You are missing a lot of detailes such as Edition that is very important (for example if you have Express edition you can add as much RAM as you want but it still will use only 1Gb per instance, and you should be more precise when saying "one database is blockig other db", do you know the exat meaning of "blocking"? If you have X locks due to long running transactions involving 2 dbs you cannot resolve this by adding resources, so what do you mean by saying "blocking"?
    – sepupic
    Oct 23, 2023 at 14:54
  • 1
    "We double the system resource" If you are doubling the CPU cores, you're still going to need to buy more licences. What are you including in your "resources" that you aim to double?
    – Thom A
    Oct 23, 2023 at 14:54
  • Run the script from this page: sqlskills.com/blogs/paul/… and add them to your question so we'll see your real problem
    – sepupic
    Oct 23, 2023 at 14:56
  • Thanks for your response, the sql versione is the standard aquired via MS spla agreement where the cost is per vm\month. The block occurring to the machine is not caused by deadlocks, but from elevated request to a single DB. If I block request to this DB the vm start responding good. The final resource will be 24 core and 64 Gb Ram. Surely there is optimization to do on single database, but at this time we cant modify our software, so the necessity to have more resource... Oct 27, 2023 at 16:19

1 Answer 1

0

There are disadvantage that we have not consider?

  • Harder to tune if CPU or IO was taken by other instance.

  • Both instances are connected and must be down during server reboot.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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