How do I get useful SQL Server database performance statistics?

Question

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:

1. Business critical db's should be spread across the servers. i.e. avoid lumping a bunch of business critical db's all one server.
2. 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

Answer 1

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.

Answer 2

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