October 8, 2019 at 7:25 pm
Hi.
I have 22 databases with the exact same structure (1 per client). They range in size from 500 MB to 700 GB. Right now all 22 are on the same instance of SQL 2012. The resources are 170 GB memory and 12 processors. Environment is Hyper-V. All VMs are stored on SAN. All data is on SAN. Databases are accessed via a dedicated LUN. Logs are accessed via a dedicated LUN. VMs are on a separate LUN.
Would I get better performance to make 2 instances on the same machine, 2 machines with 1/2 the above resources or leave them all together?
Thanks,
Mike
October 8, 2019 at 8:37 pm
I think this falls under an "it depends" window. What problem are you trying to solve? Is a single customer complaining about performance? are multiple clients? are all clients? are you certain the database is the bottleneck?
Having a single SQL instance means fewer maintenance windows, but more customers you need to interact with to get a maintenance window in place. If you go to install a SQL update, for example, you are taking down ALL of your SQL databases to do that maintenance. Having 22 instances (for example) means you are only interrupting 1 client at a time to do your maintenance and you can work with the clients to get a good window for the maintenance. Trying to coordinate with 22 distinct bodies to get a single maintenance window sounds like a nightmare.
Now, there are ways around that downtime with failovers and such, but it is one thing to think of. From the opposite end of the window though, trying to allocate resources nicely for 22 SQL Instances so you are utilizing MOST of your memory and CPU's while not clobbering the SQL Instances memory/CPU requests. Or if you have SSIS or SSRS on the same machine, you have to be careful with the memory allocation or you can get out of memory errors. And if you get a 23rd customer, you now have to juggle the resource allocation to try to get them onto the system with enough resources to be satisfied with your work.
With your question about breaking it into 2 machines, that is a bigger can of worms. How much resources do you allocate to each machine? Split it evenly down the middle? This could lead to 1 machine being overworked and the other being underworked. You also now have 2 systems to manage everything on; SQL updates, 3rd party software updates, Windows updates. It gives you a bit more slack while doing the updates as you can install it on one of the 2 systems and let some of the end users be the first round of test users on the updates. You should be testing the updates on a test system for sure, but sometimes things get missed in testing. From a performance side, I don't think you will notice much difference UNLESS you have a few clients that run high CPU/memory queries; breaking those into 2 systems may improve performance. Now, if most of the queries are light on CPU and memory, breaking it into 2 systems will likely have less impact.
My advice would be to look at resources in use on the system and decide from there. If you have a "heavy hitter" database that is using up 50% of the memory and CPU (for example), breaking it out into its own instance will allow you to better allocate resources to it so that heavy use database isn't impacting the performance of the other databases.
Deviating from your question a little bit, there are tricks you can do inside SQL that will likely help more than breaking instances up. Depending on the operations being performed, adding or removing indexes may help, rewriting some queries if possible (remove cursors for example), etc. Looking at wait stats and perfmon is going to be a lot more helpful for investigating performance issues and determining what the ACTUAL performance problem is. That is my BIG piece of advice - determine what the performance bottleneck is. You may get a better performance improvement by moving the each of the data and log files to dedicated disk or faster disk. What I mean by this is each client gets its own dedidcated disk, if that isn't what you are already doing. OR, if you are using 7200 RPM disk for the log/data files (for example), putting them on SSD may give you better performance.
But there are a lot of tricks for improving performance and some may help some may not. Upgrading to SQL 2017 may help performance, or it may hurt performance. And we don't know what else is installed on those servers or how it is configured. For example, a misconfigured antivirus tool can cause decreased performance on the database if it is scanning the data and log files every time they change. But having no antivirus on a machine is just asking for your data to get lost/corrupted/destroyed.
All of the above is just my opinion.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 8, 2019 at 9:11 pm
Adding to the above - if any one customer is "misbehaving" you can (and probably should on this situation) use Resource Governor to limit the use of server resources.
But unless your server is permanently using over 80% CPU I would not split - but I would, if not done already, implement AG or WFC so it makes patching easier with minimal downtime to customers.
October 8, 2019 at 9:50 pm
Sorry - but what is AG or WFC?
October 8, 2019 at 11:09 pm
AG = Availability Group
WCF = Windows Failover Cluster
October 9, 2019 at 2:35 pm
I'm confused. You say 1 instance of SQL 2012, but mention VMs. Are there separate VMs for other things or is this related?
I agree with the above, but I also plan for the future. I like the idea of having two instances on separate machines because at some point I will outgrow xx instances (xx> 22) on a single machine. At that time, I don't want to be scrambling. I also find that at times I might have one database outgrow others in terms of workload and having a second machine means I can move a database to a new instance if needed. This also gives me some DR if a SQL instance dies for some reason.
This requires some planning and thought, and more importantly, some networking to allow clients to move between instances. You have data on a LUN, so re-pointing that is easy. What I might start to do now is separate clients to use different FQDNs for connecting to the single instance. At some point, you can then learn how to move some clients to a second instance.
October 10, 2019 at 6:51 pm
Personally I think 22 small to medium size databases is not a large number. 220 starts to be a large number, but it all depends on the resources on the box. Unless there is a compelling reason to split them up I would keep them all on the same instance.
If you have a mixture of production and development databases on the instance, then definitely plan on moving the development databases to a Developer Edition instance - you do not need to be paying for production licences for development work.
If you are concerned about compliance issues, especially GDPR, you already have a fail in still using SQL2012 - you need to be on latest available releases for GDPR which for today means SQL2017. Also, SQL2017 is faster, more reliable, and has more features than SQL2012, and SQL2019 improves on what SQL2017 gives. Also, if you are concerned about availability and resilience then plan on implementing Availability Groups, and as part of this plan on upgrading to SQL2019 to host it all (SQL2019 will be released by the time you get to implementation).
You may get some consultants almost fighting to the death to persuade you to use a separate instance for each database (we have had that), but seek other viewpoints before going down this route - it is hard for me to think of a scenario where this is the best solution. You may also get consultants trying to get you to move to Azure or AWS - this is a far better idea and is the future for most organisations. It needs a fair bit of planning and should be on your roadmap.
Hope this helps.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply