I have a very busy database with lots of reads and writes. I want to create another database on the same server, but I am wondering if that busy database will slow down the performance of the second one.
I'm using SQL Server.
In general I want to understand if a blocking query will be per database or per server instance.
Will a busy database affect others? In general, yes. Within a single instance all databases share buffer pool, worker threads, plan cache and other system resource. There is a single TempDB for the instance to which all queries write (or spill) when needed.
Installing multiple instances on one machine may not prevent this. Likely they will still share CPU cores, network cards and (perhaps) disk spindles. A very busy DB on one instance could adversely affect a DB on another instance that shares these types of resources. Putting the instances in VMs does not avoid this conflict.
It is possible to configure each instances to have dedicated CPU, memory, disk, network cards etc. Then there will be little or no impact of one on the other. By then you have, I would argue, two independent machines residing inside a single case.
So your question about a blocking query comes down to system configuration. If a query in one DB blocks, and it does so while holding lots of resource - say an exceedingly large temp table or very many rows in an open transaction - and that resource is shared with another DB - then another query in a separate DB may not be able to acquire the resource (disk, memory etc) it requires and will fail.
The shared and exclusive locks taken in the normal course of DML, that are active in one DB will not affect what reads and writes can occur in the other DB (in the absence of cross-database queries).
