SAP Note

Header Data

Symptom

Since SQL Server 7.0, SQL Server has had a feature known as Proportional File Filling which means that as data gets inserted into a database it gets proportionately allocated across the empty space in the database data files within one file group. All data gets inserted roughly in the ratio of free space in each of the database files.  When the free space in all of the data files is about the same amount, the fill rate as well as the I/O rate occurs evenly across all files of the file group.  This is the ideal for SAP databases and the goal that should be aimed for. When the database gets full and it needs to grow, however, only one of the database data files will be automatically grown.  This can create a problem. One data file will expand and have a very high amount of free space compared with the other data files of the database that did not automatically grow.   The consequence is that the proportional file filling algorithm will now place most of incoming data into this expanded data file (since it has more empty space in it).  As a result, an unexpected I/O bottleneck can be created.  It is also possible that this situation could be repeated through additional growing events such that only a small subset of the data files are automatically grown. This behavior exists in all versions of SQL Server that are currently supported by SAP: SQL Server 2000, 2005, and now 2008.   The very viable workaround and recomended action to this is to manually grow all data files in the database by the same amount before the autogrowth algorithm kicks in.  And leaving the autogrowth mechanism as a failsafe operation when the manual file management tasks are not performed. Manually growing the database files allows the DBAs/SAs to choose the time when this can be achieved during the least impactful time. Otherwise, sometimes timeouts can be encountered in shortdumps or the following messages might be seen inside of SQL Server's ERRORLOG: Timeout occured while waiting for latch: class 'FGCB_ADD_REMOVE' id...

Other Terms

Reason and Prerequisites

Solution

With SQL Server 2008 a trace flag has been implemented so that ALL database files will be grown, as they are configured to grow, whenever an autogrowth event takes place.  For legacy reasons, this trace flag is not enabled for databases as a default setting. The database porting group of SAP for Microsoft SQL Server recommends that you enable this SQL Server Trace flag on all SQL Server 2008 servers where SAP is installed. You should do this by setting trace flag 1117 on the database server: On the server where SQL Server 2008 has been installed. Start SQL Server Configuration Manager Choose the "SQL Server Services" in the left pane and then Right click on the SQL Server instance of interest Choose Properties In the Properties dialog box select the "Advanced" tab Drop down the list box to the right of "Startup Parameters" Add a semicolon at the end of all those parameters and then the trace flag:  ;-T1117 Take note that there are two cautions to consider with this.   First, if you configure the database datafiles to grow by differing percentage growth rates, the files will become disproportionality grown over time. Again, as mentioned earlier: with this trace flag enabled ALL data files will be autogrown as they are configured to be autogrown in case of autogrowth event is encountered.  Imagine if one data file is set to autogrow by 20% and all the others by 10%.  At each autogrow event, that one database data file configured to grow by 20% will grow more quickly than the others.  After 8 growth events, the 20% grown data file will be twice the size of the other data files.  Secondly, with this trace flag in place, since all data files will be grown, space can be consumed more quickly than anticipated.  For instance if you have 20 data files and enable this trace flag; should an autogrow event take place then ALL 20 data files would grow as they are configured.

    1238993 - Proportional File Auto-Growth with SQL Server 2008  

Version   4     Validity: 02-23-2012 - active   Language   English

Released On 02-23-2012 17:05:49

Release Status Released for Customer

Component BC-DB-MSS Microsoft SQL Server

BW-SYS-DB-MSS BW Microsoft SQL Server

Priority Recommendations / Additional Info

Category Installation information

Other Components

You can check to make sure that the database files are configured to grow by the same amount, before you enable this trace flag by executing this query in each database on the server where growth might occur: SELECT growth, is_percent_growth, * FROM sys.database_files If necessary, you can change how the database datafiles are set to be grown either through the GUI or via the ALTER DATABASE command: ALTER DATABASE <DBName. MODIFY FILE   (      NAME = <DB FILE NAME>,      GROWTH = ??    ) Consult SQL Server Books Online for more information on syntax specifics. REMEMBER - This is still a failsafe operational process.  It is still best to manually grow your files, so that performance is not negatively affected by autogrowth occurring during some unexpected high load period.  Increase your data files during non-peak load times or during maintenance down periods and you will increase your users happiness.

Validity

This document is not restricted to a software component or software component version

References

This document refers to:

SAP Notes

This document is referenced by:

SAP Notes (1)

1725220   New Trace Flags set and recommended with SQL Server 2012

1725220   New Trace Flags set and recommended with SQL Server 2012