The default FILEGROWTH setting is 10% for SQL Server's database files. I don't know why they haven't changed it yet, but it is not recommended to keep it as 10%, or even to keep it as a percentage. If the files grow at 10%, there could be a lot of repetitive filegrowths. Repeat filegrowth will result in fragmentation, which will directly impact your performance. In addition to fragmentation, when the files are expanded, the current processing could be delayed, or even timeout. The larger your databases become, the more problems you will have with the 10% filegrowth.
It is recommended to use 'In Megabytes' option for the database filegrowth. What MB you use is dictated by the size of your database, and the type of activity. Or, how much the data is changing, and at what frequency. For larger databases, I typically start with 100MB, but I have used anywhere between 100MB to 1024MB.
This script is just a quick call to sys.master_files, to return details regarding your current data file configuration. Execute it as-is for your system databases, or remove the WHERE clause to target all databases.
/*
Helpful query to return the databases file details for each database. */
SELECT
DB_NAME(mf.database_id) [Database],
physical_name [PhysicalFileName],
CONVERT (DECIMAL(20,2),(CONVERT(DECIMAL, size)/128)) [FileSizeMB],
CASE mf.is_percent_growth
WHEN 1 THEN 'Yes' ELSE 'No' END AS [PctGrowth],
CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB' END [FileGrowth],
CASE mf.max_size WHEN 0 THEN 'Growth is not allowed'
WHEN -1 THEN 'Will grow until disk is full'
ELSE CONVERT(VARCHAR, mf.max_size) END [MaxSize]
FROM
sys.master_files mf
WHERE
DB_NAME(mf.database_id) IN('master','model','msdb') --- Adjust as needed.
Results:
As you can see above, the filegrowth was changed from 10% to 10MB. Understand, these are the system databases, within which there will be fewer changes and less activity. This is the reason for the small filegrowth of 10MB. You will not be able to use the same filegrowth for every database. You'll want to review each database selectively, and determine the most appropriate setting for each.
Here I have listed a few of my must-haves this topic. Definitely take a look.
This provides examples for altering the filegrowth and other file/filegroup properties.
Or see step 7 here, for a good practice for managing log filegrowth:
Brent Ozar's 'SQL Server Setup Checklist' AND the 'Instant File Initialization':
Yes, you should enable 'Instant File Initialization?'
No comments:
Post a Comment