How do I get useful SQL Server database performance statistics? We currently have several shared SQL Server servers and instances in our organization. They are going with a different architecture where business units will get their own SQL Server instances. We have SQL Server 2008R2.

In the past, databases were put on servers in a more or less random fashion -- business use and disk space were the main drivers.

I've been tasked with coming up with a plan for allocating our 50+ databases across 3 servers (one instance per server). My criteria for divvying them up is as follows:


Business critical db's should be spread across the servers. i.e. avoid lumping a bunch of business critical db's all one server.  
For the remainder of the non-mission critical db's, consider:
a. Disk space usage
b. I/O usage -- memory, CPU, etc.  


1 and 2a are easy for me to figure out. I know how to get data file sizes. 

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. 

Frankly, I'm not 100% sure how to phrase this question! I think I basically want to know is, "How 'busy' is this db -- however you define 'busy'?" Or maybe, "How does this db impact the performance of the server?"

I mean, disk space is important -- but it could (in theory) have a ton of rows or binary objects in there that are not written very often, so it has little impact on the performance of the server. I think I would want to know how much impact a db within a date range.

Is there a way to get these kind of general stats? I'm not looking for expensive queries, detailed user stats, or query optimization here. I'm aware of the SSMS reports, but they seem to be more real-time. The end goal is to divide up our current databases into evenly balanced buckets, and I would think performance is one way to do that.

Thoughts? Wisdom? Any other ideas on how to divvy up the db's?

Thanks! Tom
You can gather data you need with Data Collector, which is a part of the SQL Server Management Studio (not in all SQL Server editions though), but it’s features are limited and it won’t show you any real-time data.

It seems you need an ad-hoc solution and, if that is the case, it’s better to use some 3rd party monitoring solutions to gather data and get the job done during free trial. All these tools are great and are offering fully functional free trials:

ApexSQL Monitor

Idera SQL Diagnostic Manager

Redgate SQL Monitor

SQL Sentry