June 16, 2009 at 9:15 am
Looking for some guidance from the gurus here. In my company we have right now one production SQL Server 2005 cluster. That cluster hosts some forty databases. The databases range in size from tiny (a few MB) to one at 200GB, one at 250GB, and one at nearly 1TB having about 300M rows in its largest table. The larger databases are used primarily for reporting: load them up once a day from another data source and run reports off of them the rest of the time (some form of BI refactoring is in our future, but no $$$ are available right now for it) The remaining, much smaller databases, are primarily used for backing stores for OLTP applications.
The owners of the smaller databases often complain that queries on the bigger reporting databases are crowding them out, performance-wise. They often feel the slowdowns associated with one or more expensive queries run against tables containing 100M-300M rows. No matter how well indexed your tables and how well performance tuned your queries, when you're crunching a lot of data or returning a lot of rows (i.e., tempdb and SQL engine cycles), this happens.
Conversely, the owners of the larger databases complain that they're hampered in their ability to load and query their data because of all the de facto dependencies created by not being able to negatively impact the smaller fish.
So, my question is, is this an unavoidable reality and having everything remain on one server for "ease of maintenance" is the best solution; or is this a situation screaming for a second cluster where we put the larger reporting databases and leave the smaller OLTP databases where they are? I'm trying to make the case for the latter course. It would help greatly if I could reference white papers, articles, books, or other cogent arguments that describe best (or at least good) practices for when and by what criteria to separate out databases.
Thanks in advance for any advice offered.
June 16, 2009 at 9:20 am
From my experience it is better to have OLTP databases on a faster RAID and OLAP databases on a bigger disk with more redundancy. It seems you are trying to serve two different purposes with the one server for the sake of maintenance. If you have the means to split them off I would.
June 16, 2009 at 9:26 am
This is where your expertise rules in:
No human running reporting will want to pull 100M rows and he will not be able to go through all the 100 M rows in even 6 months.
So ask them to use where clauses to restrict datasets to minimum.
this will help reduce performance impact.
Increase server memory:(if possible)
I hope they are not using all the 300 Mil Rows. Do you have a Data retain policy or something: move the unsed or unwanted records to another Database and/or pull it off the Server.
Try Multiple Filegroups and multiple data Files for the bigger database if you dont have them already.
Manage your tempdb, ( the millions of rows if they are sorted ot have indexes etc, there should be a huge impact on the tempdb as well)
Better IO=buy new disk system/SAN.
Maninder
www.dbanation.com
June 16, 2009 at 9:57 am
There is a way to allocate specific CPUS to an application under SQL Server 2005 as explained on Slava Oks's blog at http://blogs.msdn.com/slavao/archive/2005/08/18/453354.aspx.
This week one of our customers had an interesting problem. The customer wanted to partition single SQL server instance based on the load. Customer’s application is heterogynous. It consists of TPCH type queries and data loading applications. The customer has a system, which is NUMA, with 2 nodes and 4 CPU per Node. The customer wanted to give the loading application two CPUs and the rest of CPUs to the queries. Is it possible to achieve it?
With SQL Server 2008, this is much easier to impliment with the resource governer capabilities.
SQL = Scarcely Qualifies as a Language
June 16, 2009 at 10:02 am
Try seperating the reporting database on a seperate server if you can afford to have one. Also since it's being built from OLTP databases, it need not reside on a cluster(as it can be re-built in case of disaster).
Also are you loading FULL data into reporting database on a daily basis? Why not plan something which is incremental in nature?
June 16, 2009 at 10:06 am
Thank you for all the quick replies. All good thoughts and I agree with everything everyone's said. The problem for me is that, while this is really a conceptually simple problem with a fairly simple solution (you don't put 40 databases, with widely disparate sizes, loads and business functions on one server, no matter how well you can segment resources); the solution is not quite so readily apparent to those I'm trying to convince. What I'm looking for are some types of whitepapers, books or the like to help buttress my argument that it's time to look at physical segmentation (or of course, to disabuse me of thinking that's what's needed if I'm wrong about it).
June 16, 2009 at 10:30 am
cant find exact details but may be you can club details from the technet sites and present it to users.
http://technet.microsoft.com/en-us/library/cc966418.aspx
There are few more technical papers there which might help.
June 16, 2009 at 10:36 am
Pradeep: In answer to your question, the data loads we do daily are incremental, not full.
June 16, 2009 at 10:40 am
Thanks Pradeep. Trying to cobble together some of those articles and whitepapers on my own is what I've been doing to date, with some marginal success. It seems as if this would be a very good topic for better treatment in a book or other venue.
In any event, I hadn't seen that particular whitepaper. I'll add it to my 'arsenal' for when the justification meetings and battles begin. 🙂
Thanks again!
Steve
June 16, 2009 at 11:30 am
How many nodes are in your cluster? It sounds like you have only one active node. Have you considered creating a new instance on the same cluster that could run on one of the currently passive nodes? This could buy you breathing room without the cost of any additional servers.
June 16, 2009 at 1:32 pm
Recommend benchmarking your performance, showing your decision makers how you can improve performance, and increase customer satisfaction by improving availability.
Slow reporting and other system processes leads to inefficient work - if you can demontsrate how much time is lost due to the latency issues this may help.
You may also want to track the number of complaints and time spent on resolving/researching them. This will also add value to your request 🙂
AND - if you do the research on the new box - getting a good price, basically doing all of the legwork for the decision-maker, it's removes this from their plate and may increase the likelihood that they will give approval.
June 16, 2009 at 1:54 pm
dakman (6/16/2009)
How many nodes are in your cluster? It sounds like you have only one active node. Have you considered creating a new instance on the same cluster that could run on one of the currently passive nodes? This could buy you breathing room without the cost of any additional servers.
Wow, I hadn't really thought of this. Thanks for the idea. Sounds intriguing. Do you know of any documentation that discusses doing this?
June 16, 2009 at 1:57 pm
Sandra Skaar (6/16/2009)
Recommend benchmarking your performance, showing your decision makers how you can improve performance, and increase customer satisfaction by improving availability.Slow reporting and other system processes leads to inefficient work - if you can demontsrate how much time is lost due to the latency issues this may help.
You may also want to track the number of complaints and time spent on resolving/researching them. This will also add value to your request 🙂
AND - if you do the research on the new box - getting a good price, basically doing all of the legwork for the decision-maker, it's removes this from their plate and may increase the likelihood that they will give approval.
Sound practical (and politically savvy) advice from someone who sounds as if she's been there. 😛
Thanks Sandra!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply