Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Our database architecture allows multiple 'customers' to exist in the same database, yet we split them into mulitple database for administrative reasons [patching, backup, etc.]

Question 1

What would the performance implications be if we consolidated the customers into a single DB?

Question 2

We current have multiple customer in each DB, but we may have 10 in each and say 5 database; so if we consolidated the DBs we'd just have one DB with 50 customers; would that make much difference to performance?

share|improve this question
    
Where you currently have 10 customers in each of 5 databases, how many physical servers and SQL instances are there? –  Mark Storey-Smith Aug 26 '11 at 12:43
    
These are fictitious figures we actually don't have customers, well, we have one customer, broken down into different regions and sub regions but the software can work with them split across DBs or in a single DB we have many physical boxes running multiple DBs. –  Dog Ears Aug 26 '11 at 13:07
1  
This is one of those big "it depends" situations. Get a baseline for how hard the resources are getting hit by using perfmon, and through some calculations and testing you can get a pretty good idea before pushing this out to production. –  Thomas Stringer Aug 26 '11 at 14:41
    
@Surfer513 - what calculations and testing? Could you expand in an actual answer? thsi is a pretty basic question "Big v Multiple Small" - yet I can't seem to find anything specific on the inter-web. –  Dog Ears Aug 26 '11 at 14:59
    
How big are the databases? How many concurrent users of the system are there? –  Stuart Blackler Aug 26 '11 at 15:27

4 Answers 4

up vote 7 down vote accepted

As others have indicated in the comments, it's difficult to form an answer to this question without an understanding of the application. It depends, it really really does.

The nature of the question (and answer) also changes on the basis of the physical environments i.e. multiple databases on a single server or spread across several? Is a customer “typical” or do some consume a disproportionate percentage of server resources? In 3 years, will there be 50 customers or 50000?

That said, let’s have a crack at it.

What would the performance implications be if we consolidated the customers into a single DB?

Potential gains

  • Reduction in plan cache memory. If you have 5 of the same database you have 5 copies of every execution plan.
  • Improved buffer pool utilisation. Similar to above, any common data that you have in each database exists in the buffer pool for each database.
  • Improved cpu/memory utilisation. Consolidating multiple servers to a single server removes the overhead of the operating system on each host.
  • Possibly improved IO utilisation. Combining the smaller arrays allocated to each server might result in improved overall throughput by having higher capacity to deal with peaks.

Potential risks

  • $$$. Multiple low spec servers are typically cheaper than an all mighty powerhouse. Spreading the load across utility servers can be cheaper.
  • Expansion flexibility. When you have all customers on that all mighty powerhouse server and it runs out of steam, upgrading is difficult and complex.
  • Locking/blocking/deadlocks. Any deficiencies in the database and application design are likely to be magnified in a single host environment.

From your comments, it sounds like this is early days for your software and company. So, I’d be looking for ways to maximise flexibility and minimise capital expenditure.

  • Install an expandable iSCSI array. With separate storage, you can expand both raw GB capacity and IOPS in isolation from the servers.
  • Make your customers data portable. Might be difficult to retrofit but if you can migrate a customer’s data from one system to another, you can move them between systems to better balance workloads.
  • Tier your customers. Allocate each customer to a tier depending on their usage, perhaps 3 tiers initially. Analyse typical usage patterns for these tiers of customers and allocate them to server resources accordingly. It may be that you can accommodate 200 tier 3 customers on a single box, or 50 tier 2, or 10 tier 1.

Maybe virtualisation would be a better fit. Maybe cloud would work. Maybe hybrid cloud. Honestly, it really does depend. Call in an expert to help, they could save you a fortune.

share|improve this answer
    
Also, the more databases you have on a given server, the longer it takes for that server to come online after a restart, failure, etc as SQL Server has to do any pending rollbacks / redos and other checks before it onlines each database. –  Eric Humphrey - lotsahelp Dec 7 '11 at 18:39

One of the biggest drawbacks with lots of smaller (and smaller obviously being a relative size) databases, is log management. Log files are written to sequentially, not randomly. With one large database, you can dedicate your log file to a single disk array, giving maximum sequential performance. If you have 50 databases on that same array, the data will effectively be random IO, causing way worse performance.

The same goes for data files to some extent, but since they're written to asynchronously by the checkpoint process, it's less of an issue since it'll mostly be smaller sequential writes.

We're running a similar architecture and while I really enjoy the benefits in patchability and running different versions, I'd definitely go for a completely multi-tenant solution if I were to do it over.

share|improve this answer

What would the performance implications be if we consolidated the customers into a single DB?

Only you can answer this question. Not knowing your environment or your server configurations this would be difficult to predict. There are tools out there that can be used to stress test your current configurations to see what the limit is they can handle.

You should setup a script to gather the performance counters over a period of time so you have a good baseline of your systems. You will be able to use this to find your peak times of traffic for each one. You can do a quick Google search for "SQL Server performance counters" that can point you in the right direction. I am also sure stackexchange has many post on it as well.

EDIT This would be a good link to start with for the performance counters.

share|improve this answer
    
Maybe I need to re-word the question - I'm after a general answer, giving guidance on what is the considered best approach and an overview of the reasons why. Your answer seems a bit of a 'It depends' type answer, which although probably accurate, doesn't really help me :( –  Dog Ears Aug 28 '11 at 9:59

I know this question asks specifically about performance implications, and there are good answers here already, but I think it would be remiss to not mention a topic that hasn't been broached yet when talking about multi-tenant solutions: security.

When you hold data for multiple customers in a single database, you have to be extremely careful when it comes to security, not only in the database itself, but in the application(s) as well. Generally speaking, one customer should never be able to see another customer's data. I don't know your business, but this is usually the case.

When architecting an application, creating security boundaries is desirable, so that if when your team happens to make a small mistake, there's already a built-in fallback to prevent Bad Things from happening. A database is one type of security boundary.

This separation (a business requirement, which may or may not exist) needs to be balanced with your plan to manage the data, performance, etc., though security requirements can (and should) trump everything else if it's deemed important enough by the business.

Yes, it sounds like there's an echo in here, but it really does depend on your exact situation to make the best decision. All we can do is give you ideas to help you reach that conclusion.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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