We have a SQL Server 2008 installation on a machine with 24+GB of RAM and 16 cores. Plus fast drives, etc. A large machine by our standards.
Anyway, that machine is 100% devoted to SQL Server and there are TWO production databases on it. Each DB is from two different vendors.
This has been running fine for 2 years.
Now, within the last 2 WEEKS, Vendor B is saying that the SQL Server can't handle their 8 GB database in performance. Nothing has changed on our end in months. Our DB server hasn't been touched.
Vendor A is working just fine.
So it sounds like Vendor A is taking more resources and slowing down Vendor B. But I don't suspect Vendor A is to blame because I am familiar with the their DB and its users.
How can I find out how much resources each DB is using? How I can say Vendor A gets 50% and Vendor B gets 50% of the server?
Best Answer
One way to do this would be through Resource Governor. You can set up the appropriate Resource Pools and Workload Groups for each vendor/database. Then you would create a classifier function (basically just a UDF that's registered in Resource Governor as the classifier function) to determine which Workload Group the incoming request would belong to.
To make this easy you could create Windows Groups (one for each vendor/db) and base your classifier function off of this, and set the appropriate workload. These workloads can be configured according to your specifications above.