Sql-server – How to increase performance in Cross Database operations

In order to avoid overloading my server, the requests are queued and handled one at a time.

That's the problem right there. You are not avoiding but causing overloading this way. Single row INSERT / UPDATE is dramatically more expensive than doing the same en bloc. Each statement has to be planned and executed separately. Depending on missing details there may be transaction overhead and even connection overhead, too.

Try to run multi-row INSERT / UPDATE commands instead. Or use COPY, or one of the fastest options would be to define a foreign table and fetch all data from a remote DB directly in a single SQL command (if that's at all possible).

Or depending on details, some other replication method may fit your needs.

This is how you update multiple rows at once:

• Update multiple rows in a table from another table when condition exists

And here is how to do that from a CSV file:

• How to update selected rows with values from a CSV file in Postgres?

My question is with that last bit - 2b. I do not know how relevant this is, what specific stats to look at, or how to get them.

If you are tight with budget for a good third party tool - Performance Advisor (note: We use do not use 3rd party tools - only SQL Server DMVs/XEvents, etc homegrown over the years), you can look at sys.dm_os_buffer_descriptors DMV. Remember that you have to have a job that dumps the data from the dmv to a physical table, since the DMV data get reset.

From my answer :

--Find out the database that consumes highest memory in buffer pool 
SELECT COUNT(*) AS cached_pages_count , 
    ( COUNT(*) * 8.0 ) / 1024 AS MB , 
    CASE database_id 
      WHEN 32767 THEN 'ResourceDb' 
      ELSE DB_NAME(database_id) 
    END AS Database_name 
FROM sys.dm_os_buffer_descriptors 
GROUP BY database_id

if you want something like CPU usage, Memory usage, Network usage and Disk space usage overtime then use Performance Data Collector.

How 'busy' is this db -- however you define 'busy'?"

Busy - I would define as

• how much space the database uses in buffer pool ?
• How much IO it is generating ?
• CPU used by databases.
• Is the database involved in read (reporting) or more write (OLTP) activity ?

Its very important to do a Baselining of your sql server with SQL Server Dynamic Management Views

Once you have proper data collected, you can easily figure out what databases to group together.