January 10, 2008 at 10:15 am
I've recently started a new position and have been looking into index fragmentation for a series of databases. The fragmentation figures are alarmingly high for a number of the tables despite a weekly rebuild of all the indexes. I have conclude that the fill factor is a major culprit here. The default value is 0 and many of these tables experience high levels of inserts daily. Conclusion, I want to change from rebuilding all to using a script that determines level of fragmentation and reorgs those indexes in the 10 to 30% fragmentation range while rebuilding those with over 30% fragmentation.
Initially, I will script a rebuild for each index and set a different fill factor for each.
My question is will the fill factor set by the initial rebuild continue to be used with future reorgs and rebuilds without specifying fill factor or will it revert to the DB default fill factor of 0?
January 10, 2008 at 11:03 am
It depends on how you reindex, you have an option to rebuild using the default amount of free space, if you use maintenance plans. From MSDN
Reorganize pages with the default amount of free space
Drop the indexes on the tables in the database and recreate them with the fill factor that was specified when the indexes were created.
After you rebuild the index to adjust the fill factor your reindexing job (Rebuild Index Task) will keep your changes. If you reindex using scripts, check the script to ensure that the same applies, ensure that the script does not use the same fillfactor for all indexes.
January 11, 2008 at 9:59 am
i have a process where daily i run the system view to gather the data and then alter index based on the frag data. i have separate db's on each server for this.
if you want the details i can post them here, you can search for my posts and find where i posted it here some months back or PM me and i'll email you my process and scripts
January 11, 2008 at 10:44 am
SQL Noob,
I think I found your script that uses an ALTER INDEX to either reorg or rebuild indexes. Sure would be nice to have the DDL for the physical_stats table!
Script will be handy but I still would like to know if fill factor once set by an ALTER INDEX...REBUILD will persist for future ALTER INDEX runs.
TIA,
Chuck
January 11, 2008 at 12:43 pm
whatever column is not in the system view i run an update to fill in after the system view runs
USE [Index_Stats]
GO
/****** Object: Table [dbo].[physical_stats] Script Date: 01/11/2008 14:42:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[physical_stats](
[server_name] [varchar](25) NOT NULL,
[database_id] [smallint] NULL,
[database_name] [varchar](25) NULL,
[object_id] [int] NULL,
[table_name] [varchar](128) NULL,
[index_id] [int] NULL,
[partition_number] [int] NULL,
[index_type_desc] [nvarchar](60) NULL,
[alloc_unit_type_desc] [nvarchar](60) NULL,
[index_depth] [tinyint] NULL,
[index_level] [tinyint] NULL,
[avg_fragmentation_in_percent] [float] NULL,
[fragment_count] [bigint] NULL,
[avg_fragment_size_in_pages] [float] NULL,
[page_count] [bigint] NULL,
[avg_page_space_used_in_percent] [float] NULL,
[record_count] [bigint] NULL,
[ghost_record_count] [bigint] NULL,
[version_ghost_record_count] [bigint] NULL,
[min_record_size_in_bytes] [int] NULL,
[max_record_size_in_bytes] [int] NULL,
[avg_record_size_in_bytes] [float] NULL,
[forwarded_record_count] [bigint] NULL,
[date] [datetime] NULL,
[index_name] [varchar](128) NULL,
[psuidid] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_servername_psuidid] PRIMARY KEY NONCLUSTERED
(
[psuidid] ASC,
[server_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
January 11, 2008 at 12:45 pm
here it is
in my alter index posts from months ago, i changed the temp tables to table variables. too much trouble with temp tables and job failures
-- Run system view to gather fragmentation data and insert into table
INSERT into index_stats.dbo.physical_stats ( database_id,
object_id,
index_id,
partition_number,
index_type_desc,
alloc_unit_type_desc,
index_depth,
index_level,
avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages,
page_count,
avg_page_space_used_in_percent,
record_count,
ghost_record_count,
version_ghost_record_count,
min_record_size_in_bytes,
max_record_size_in_bytes,
avg_record_size_in_bytes,
forwarded_record_count
)
select * from sys.dm_db_index_physical_stats (21, null, null, null, 'detailed')
update index_stats.dbo.physical_stats
set date = getdate() where date is null and database_id = 21
update index_stats.dbo.physical_stats
set database_name = 'billing' where database_name is null and database_id = 21
update physical_stats
set table_name = name from billing..sysobjects b join physical_stats a on a.object_id = b.id where table_name is null and database_id = 21
update physical_stats
set index_name = name FROM billing.sys.indexes b inner join physical_stats a on a.object_id = b.object_id AND a.index_id = b.index_id where database_id = 21 and index_name is null and datepart(d, date) = datepart(d, getdate())
January 11, 2008 at 12:48 pm
From what I've seen - the fillfactor persists until you specifically change it, so it will use the last specified value..
So if you create the index with a 90% fill, then at some point later run
Alter index myindexname on mytable REBUILD with (FILLFACTOR=70)
Your index gets rebuit with a 70% fillfactor. Every subsequent rebuild without a specific fill factor will rebuild with 70%.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 11, 2008 at 6:10 pm
Matt,
That was just the information I was looking for. It seems the only logical approach but you know how often logic is shaky in the IT world!
January 11, 2008 at 6:16 pm
SQL Noob,
Appreciate the code for creating the physical_stats table.
Chuck
January 11, 2008 at 6:35 pm
the system views are pretty easy to dump data into tables
just select into new_table_name from system_view_name
it will create the table name and all the right data types. then i usually add a date column, bigint with identity for a PK and in this case table names and index names. when i first started this i would go nuts trying to figure out which indexes were fragmented on a daily basis so i added these
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply