We have a SQL Server with about 15 databases and about 60 + users accessing any of these databases. Each database is designed to serve different data set (meaning - we have multiple roles in each database). one user can access as many as all database (1 or 2 or any number upto 15). Users typically run the workflows which includes access to multiple databases usually 3-4 databases at a time. During peak time multiple users run similar workflows accessing many databases.
Out of 15 databases, we have 5 databases with heavy load, for rest of them it is nominal load.
Recently we started experiencing high CPU and memory issues on this server.
In most of the times, single user takes most of the resources on the server (which runs work flow on any 1 of these databases - not accessing multiple databases) and other users suffers as they are not able to execute queries.
Please suggest ways to resolve these issues - we tried resource governor but not able to handle this single user case.
Let me know if you need any other information about the system.