3

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

  • 3
    If you don't already have a monitoring solution, I would very highly advise you looking into something like Spotlight or SQL Monitor or Database Performance Analyzer (formerly known as Ignite). You will get real time and historic stats for SQL Server and Windows Server in addition to all the warnings and alerts that can be configured. – Steve Mangiameli Feb 22 '16 at 17:33
  • 6
    i'd vote for SQL Sentry's PerformanceAdvisor (note: i'm a customer, not an employee) – swasheck Feb 22 '16 at 18:25
  • You could look at the plan cache. Minimally, if you run sp_blitzcache (or similar) you can rank queries by cost. If you see a lot of high cost and/or high usage queries aimed at a particular database, you could surmise that that database has high usage requirements. – Peter Feb 22 '16 at 20:06
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

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.

  • Thanks. I was hoping I was missing some easy magic inside of SSMS, but sounds like that's not the case. I will need either a job that dumps DMV stats into a table so I can do my own parsing, or I'll need to pay for a monitoring tool. Is that right? We actually already have RiverBed AppInternals. I know it does some MSSQL monitoring; perhaps I can dig into that and see if it reveals anything. Thanks! – ironfist Feb 22 '16 at 21:28
  • @ironfist I dont have any experience with RiverBed AppInternals, so I cannot say if that will help or not. – Kin Shah Feb 22 '16 at 22:49
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.