February 16, 2009 at 12:52 pm
Hi all,
I´m in a big challenge at my job. I´m working in a retail company that has 220 stores and about 10K employees.
Before I came, this the company has purchased and installed SAP/R2 with several modules like Banking, FI,CO,SD. We have 5 application server and 1 clustered SQL Server 2005 running on a Dell R900 with 64GB Ram. Our storage and our weakness point is a EMC CX3 40. The performance is ok but the trouble is that our database is growing like a crazy. The initial installation started with about 700 GB but after 9 months the database reached 3,3 Tera Bytes. This is generating a high cost for the company because we had to buy several disks magazines.
The SAP does many things by itself like creating index and creating the tables so it´s hard to restrict it´s behavior or create new filegroups or partitioned tables.
My major concerns are:
Is that correct a SAP database grows so fast and become so big? Anybody has seen this scenario?
Is it possible that somebody have made some mistake at the time to configure the storage? I mean did not aligned the disks, the pages are not filling the correct space or something like that. How can I check this kind of detail in SQL Server storage engine?
Is it possible that SQL Server is consuming extra space because a miss configuration like spreading a page in several allocation units?
I´m looking for a way to dig in the storage, investigating some configuration mistake but I haven’t had much success.
I´ll be glad to receive any suggestions that help me to investigate this issue!!!!
Thanks in advance
Eduardo Pin
February 16, 2009 at 10:09 pm
The thing you could have done is set a very low fill factor and there would be lots of free space on pages, but as you changed data or added it would go in the pages as it fits.
Are you sure it's the data files growing and not the logs? Are you running log backups?
If the files are growing that fast, perhaps it's just a lot of data and transactions being added.
February 17, 2009 at 6:04 am
Hi Jones, transaction log backups are done every 5 minutes, sometimes they have 1,5GB, usually they have 300MB. The log files are limited to 50 GB and believe this size is ok for a 3,3 TB SAP Database.
Also I have a remote log shipping for this database. The bad thing is when I lost the log shipping and I need to recover a full backup that takes 12 hours to complete.
I´ll try to set a small fill factor....
Thanks...
February 17, 2009 at 7:10 am
That seems like a massive amount of growth in a 9 month period to me, but it really depends on what's being stored.
If it's down to the level of every transaction of each retail store and the data is quite denormalised, then it's possible that the size is representative.
Things like Fill Factor could make a different, but not likely to be to very large orders of magnitude.
Go through the documentation from the implementation. Was predicted database growth covered in this?
I would start by identifying the top 20 largest tables. There are plenty of scripts available for this on the site, or there's a built report in Management studio for disk usage by table. Look at the row counts, what kind of data is stored, how much space do the indexes take up and validate whether it's all sensible.
If not, go to the vendor (I'd assume with a SAP implementation the size yours you'd have support) and see whether they have any comments on the tables in question. SAP implementations tend to be heavily customised and have all sorts of data feeds in, any one of which could be wrong/duplicating data/pulling in unneccasary data.
Also definitely check on the space used in the database as opposed to the datafile sizes.
February 17, 2009 at 2:03 pm
Steve asked you to check if the fill factor is a small value like 40 or 50 % if so please make it 80 or 90% that means all the leaf level pages will be 90 % filled there by eliminating half empty pages and cutting down on the amount of space consumed.
Dont have a very low fill factor unless needed
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
February 17, 2009 at 2:36 pm
But be sure that you know if you need a low fill factor. This is the percentage of space on the pages that are filled. If you are really growing that fast, and potentially have lots of changes, then you might want a low fill factor to prevent excessive page splitting.
As far as logs go, 300MB every 5 minutes if a lot. A huge amount. I could see the size changing dramatically if there were index rebuilds, but 300Mb every 5 minutes is a lot.
Honestly if you're not sure why/what is causing growth, I'd really recommend that you contract with someone to spend a few hours looking things over. It sounds as though you might be a little out of your element with this. My business partner at End to End training, Brian Knight, has extensive experience with systems and has been a SQL Server MVP for years. He has a consulting business called Pragmatic Works (http://www.pragmaticworks.com/) that I'd recommend you call (or someone else) and find out what's happening.
February 18, 2009 at 3:31 am
You need to talk to SAP. They will have LOTS of experience on how big the database should be with your workload, and how to tune it. SAP databases can grow very large, depending on what your company is doing within SAP. When you purchased SAP, there should have been a process of diligence where your company told SAP what they planned to do and SAP said what resources wee needed. If your company is doing different things to the plan, the SAP capacity plan will need to be changed.
There are some generic things a non-SAP expert can say (e.g. look at your fill factor, look at your table and index rebuild process, etc), but SAP can give application-specific advice.
In particular, SAP can tell you how to archive old data from their application, which could save a shedload of space if this is relevant to your situation.
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
February 18, 2009 at 2:35 pm
Hi,
1)The fill factor is already set to 90.
2)The growth predicted has reached months ago…
I´m think that there are something related to duplicated data, however SAP has about 50K tables and it´s hard to search for duplicate information.
I reported the 20 biggest tables to SAP team and the biggest one is about to reach one billion of lines. I cant take any conclusion about duplicated data because SAP tables is almost impossible to understand , looks like they talk another language "SAP language". I think the business team and basis team can check if data is duplicated.
I´m trying to concentrate my efforts on the infra-structure analyzing the disks or some wrong configuration that can lead to a unexpected grow…
I asked to basis team to investigate some data duplication, I´ll wait for their answer.
Thanks…
Eduardo Pin
February 18, 2009 at 3:34 pm
I worked on one of the pharmaceutical organization, they also had a similar issue. The data growth was massive. Eventually we have to create datawarehouse solution to put a hold on OLTP environment. We were currenlty keeping 2months worth of data in OLTP and moveing the older one to federated data warehouse. Still the OLTP size was more than 1 TB.
This is not related to your performance issue, but you may think of adding data warehouse solution here.
February 19, 2009 at 1:18 pm
I STRONGLY recommend that you get some professional help, sooner rather than later. From your questions and comments I just don't see you having the necessary skills and training to handle what has been thrown your way. That is not a personal attack btw. Unless you are a certificated pilot I bet you can't fly an airplane either. 🙂 A professional engagement will help the company get a handle on what is going on and how to react to existing conditions as well as set up proactive improvements to keep future trouble at bay. You get some mentoring to help you be a better admin in the future too. Win-Win.
Oh, and the CX's can be REALLY problematic, even if you don't mess up other things like raid types, raid stripe sizes, queue depths, cache ratios, sector alignment, cluster sizes, etc, etc. Drives 0-4 on Enclosure 0 have a variety of system functions that are on a private partition across them. 33GB per drive lost, and if you include those drives on other raid groups you lose that amount on all of them too. And those 5 drives can have a DRASTIC performance degredation under a number of situations.
Drop me a PM if you like and I can recommend some help. Note that I am NOT looking for work for myself in case you were wondering about that. I am (VERY fortunately for me!!) wayy too busy to help out - at least in the near term. It certainly would be a gig I would love to engage in, however!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 3, 2011 at 12:50 pm
I have SAP (Basis) experience and just passed 70-442 exam.
I have not seen this type of growth, here are things YOU can check. One user created a LOG table (just flat file logs) with an improper index (well, Primary key) and it grew out of control - so if you see a USER table with LOG in it's name a flag should go up. As a matter of fact review ALL user created tables.
There is an OSS note for SAP tables you should watch and maintain. One table, BALMP – I found out this also was a (SAP) log table and had 13 years of data! Went to the functional team and found out they only needed 6 months worth of data! I saved 80 Gig in the database. Remember this is SAP, even though I deleted the data I had to REORG the table and indexes before the space was reclaimed by the database.
You need an archiving solution. IXOS may be the best for R/2 or R/3, not sure anymore. I like the way you can use (SQL Server) table partitioning for arching but SAP is tricky the way they store data so I’m not sure if you can use this as a strategy. Is your functional team aware of this problem?
Bob Jessie
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply