June 23, 2011 at 3:09 pm
Recently tested setting fill factor to 90 on an entire testing database. What's the simplest way to set it back to the default value of 0 for all indexes?
June 23, 2011 at 3:39 pm
The same way I presume you set it to 90, rebuild all indexes specifying a fill factor of 0.
---------------------------------------------------------------------
June 23, 2011 at 3:40 pm
You can set the default for the database back to 0 (functionally same as 100) with this...
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'fill factor', 100;
GO
RECONFIGURE;
GO
...however that only affects new indexes that are created where the fill factor is not explicitly specified.
To change the fill factor for existing indexes you have to rebuild them (or drop and recreate them) and explicitly specify the fill factor.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 23, 2011 at 5:42 pm
george sibbald (6/23/2011)
The same way I presume you set it to 90, rebuild all indexes specifying a fill factor of 0.
When you rebuild indexes through the maint. plan GUI, there are 2 options:
- reorganize pages with the default amount of free space
(doesn't change the fill factor of the existing indexes)
- change free space percentage to a value
(doesn't allow you to apply a value of 0 or 100)
Maybe I am missing something here. I usually drop and recreate specifying the fill factor, but I have 4200 indexes.
Suppose I can restore from a backup - just thought there might to be another way.
June 24, 2011 at 3:24 am
Here's a query I have used in the past to generate commands I can run later. The script was not tested on partitioned tables. Adjust the WHERE clause and fill factor in the reindex_command column to suit:
SELECT DISTINCT
DB_NAME() AS db,
SCHEMA_NAME(o.schema_id) AS [schema_name],
o.name AS table_name,
o.create_date AS table_create_date,
i.name AS index_name,
i.fill_factor AS fill_factor,
p.rows AS table_row_count,
'ALTER INDEX [' + i.NAME + '] ON ' + DB_NAME() + '.' + SCHEMA_NAME(o.schema_id) + '.' + o.name + ' REBUILD'
+ CASE WHEN i.fill_factor IN (0, 100) THEN ' WITH (FILLFACTOR = 80)'
ELSE ''
END + ';' AS reindex_command
FROM sys.objects o
INNER JOIN sys.indexes i ON o.object_id = i.object_id
INNER JOIN sys.partitions p ON o.object_id = p.object_id
WHERE i.index_id > 0
AND o.is_ms_shipped = 0
--AND p.rows > 1000000
AND i.fill_factor IN (0, 100)
ORDER BY p.rows DESC
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 24, 2011 at 3:45 am
opc.three (6/24/2011)
Here's a query I have used in the past to generate commands I can run later. The script was not tested on partitioned tables.
Very nice - looks like this will do the trick...
June 24, 2011 at 4:24 am
No problem. I am curious now though, because a default of 100 is not really appropriate for most shops IMO...I usually change the default to 90 on all my systems regardless of workload. Why are you going back to 100 as a default, and why are you rebuilding everything with 100?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 24, 2011 at 4:37 am
Hi,
You need do the analyze you indexes on the server before you change anything. You can calssify the table and index as per below
Low Update Tables (100-1 read to write ratio): 100% fillfactor -- Reference Table,
High Update Tables (where writes exceed reads): 50%-70% fillfactor -- High Transactional table
Everything In-Between: 80%-90% fillfactor.
Filter you FillFactor based on the above information.
June 26, 2011 at 7:04 am
opc.three (6/24/2011)
No problem. I am curious now though, because a default of 100 is not really appropriate for most shops IMO...I usually change the default to 90 on all my systems regardless of workload. Why are you going back to 100 as a default, and why are you rebuilding everything with 100?
I am testing the difference. My sandbox db runs on the same hardware as my prod at the moment, but my prod db is faster. I restore the sandbox db from a prod db copy every 2-3 weeks.
Trying to figure out if the fill factor change is the cause. Doubt it, but need to know before I change the fill factor in my prod to 90.
June 26, 2011 at 7:14 am
deepaksharma21 (6/24/2011)
Hi,You need do the analyze you indexes on the server before you change anything. You can calssify the table and index as per below
Low Update Tables (100-1 read to write ratio): 100% fillfactor -- Reference Table,
High Update Tables (where writes exceed reads): 50%-70% fillfactor -- High Transactional table
Everything In-Between: 80%-90% fillfactor.
Filter you FillFactor based on the above information.
I generally use 90 on high read tables and 70 for heavy write tables. Probably should get a query together and really analyze the read/write ratios. Thanks for the info.
June 26, 2011 at 8:00 am
SkyBox (6/26/2011)
opc.three (6/24/2011)
No problem. I am curious now though, because a default of 100 is not really appropriate for most shops IMO...I usually change the default to 90 on all my systems regardless of workload. Why are you going back to 100 as a default, and why are you rebuilding everything with 100?I am testing the difference. My sandbox db runs on the same hardware as my prod at the moment, but my prod db is faster. I restore the sandbox db from a prod db copy every 2-3 weeks.
Trying to figure out if the fill factor change is the cause. Doubt it, but need to know before I change the fill factor in my prod to 90.
What caused you to decide to change the fill factor in the first place? If you decided to go from 100 to 90, and then reverting based on nothing then I'm worried. You should be looking at Page Splits/sec and fragmentation.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 26, 2011 at 8:18 am
What caused you to decide to change the fill factor in the first place? If you decided to go from 100 to 90, and then reverting based on nothing then I'm worried. You should be looking at Page Splits/sec and fragmentation.
I read as a general rule of thumb, 90 is better than the default. I am reverting back to default based on trainers/users complaining about performance - knowing that the fillfactor is the only thing different from prod.
Are the page splits/sec captured in a DMV and the perf mon? Which DMV?
June 26, 2011 at 8:22 am
I always use PerfMon, not sure if it's available in a DMV. If you find a DMV/F please post back 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 26, 2011 at 10:04 am
take a look at sys.dm_db_index_operational_stats
http://msdn.microsoft.com/en-us/library/ms174281(v=SQL.100).aspx
---------------------------------------------------------------------
June 26, 2011 at 11:31 am
opc.three (6/26/2011)
I always use PerfMon, not sure if it's available in a DMV. If you find a DMV/F please post back 😀
Except that the perfmon counter doesn't actjually track page splits. It tracks new pages added to an index (no matter where they are added)
Track fragmentation. There's no current way to track page splits that cause fragmentation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply