Well here is a small checklist. Due to the fact that I'm not on your machine I cannot take a look inside and give you some suggestions. But I'm pretty sure, that you'll find the root cause with the provided statements.
Wrong memory configuration
As you mentioned, you upgraded your SQL Server and run different instances on the same physical machine. One of the things which may be harmful is a restart or anything like that (e.g. Failover). Maybe your instance has reset their memory limits. This may explain your drastically slowdown. You can check if they still up with your documented max memories. You can get them running the following the command below:
EXEC sp_configure N'max server memory (MB)'
If you just want to see this as a quickshot. You can try to query the dm_os_sys_memory which has a nice column system_memory_state_desc which isn't very detailed but it says you the needed things too. It will display if your memory is low, which means there is a constant memory pressure on the system.
SELECT system_memory_state_desc
FROM sys.dm_os_sys_memory
Statistics are out of date
You might want to update your statistics for your database tables, as they may be too old.
EXEC sp_updatestats
In a situation like yours, it is wise to throw away your cache plan, as it might be useless due to the old statistics. If sp_updatestats shows many tables and many statistics which are updated, it may be a good idea to throw it away and let the SQL Server recreate it.
You can achieve this using this DBCC command:
DBCC FREEPROCCACHE
Fragmented indices
It might be, that your indices are too fragmented which cause a huge load on your I/O subsystem. Before you blindly execute the statement, you should check if your machine has a higher load on your I/O subsystem. This may be easily done by using perfmon for example. The READ UNCOMMITTED should be used as some indices may be written at the moment. You can try to run the following statement.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Get Index Fragmentation
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName
, ind.name AS IndexName, indexstats.index_type_desc AS IndexType
, indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 25
ORDER BY indexstats.avg_fragmentation_in_percent DESC
GO
Beware, it might run very long and on your system which is already under pressure it might kill it. Only run it, if your system may to be affected by it.
Each row which will be returned is an index which is fragmented and may need an REBUILD or REORGANIZE. This may improve your overall system performance too, but during the rebuild itself, it will slow down!
ARITHABORT problem
It may be an problem with ARITHABORT which tends to make problems on older databases (for some users). Maybe you try to enable/disable it.
Many other causes...
As mentioned in the first sentences, it's pretty hard to see where your problem is. It may be another error - maybe even a hardware fault. I've had a error on a switch to my SAN which killed my performance too for some years (before it was redundant).
But if nothing of the above tends to help, you can still provide the output of your dm_os_wait_stats which may help me to get the right view on your system.
SELECT TOP 30 *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
This will return the biggest waitcounter and may help to find a bottleneck or error. You can do this using the following query:
-- Show all active processes with detailed informations
SELECT pr.spid, pr.loginame, pr.status, pr.blocked, pr.waittime, pr.lastwaittype,pr.cpu, pr.physical_io, pr.memusage, pr.last_batch, db.name AS databaseName, obj.name AS objectName, txt.text AS currentQuery, pr.request_id
FROM sys.sysprocesses as pr
INNER JOIN sys.databases as db
ON pr.dbid = db.database_id
OUTER APPLY sys.dm_exec_sql_text(pr.sql_handle) as txt
LEFT JOIN sys.all_objects as obj
ON txt.objectid = obj.object_id
WHERE pr.status <> 'sleeping'
and pr.spid >= 50
Additionally it can be a process which produces locks or consume more than it should. But I think this isn't the best possibility. But anyway, you can take a look in your running processes. Maybe you see something which isn't correct.
