Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Changing FILLFACTOR by Updating sys.indexes Expand / Collapse
Author
Message
Posted Monday, February 14, 2011 8:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 12:55 PM
Points: 169, Visits: 112
I have a database that is getting too large for a simple index rebuild periodically. As a result I am developing a plan to update statistics, reorganize or rebuild based on fragmentation and when the last statistics update happened.

My question is, can I just update the FILLFACTOR in the sys.indexes table so that the next reorganize or rebuild will pick up the new FILLFACTOR automatically without having to manually do a reorganize to change the FILLFACTOR?

Pat




Pat Buskey
Post #1063575
Posted Monday, February 14, 2011 8:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 12,916, Visits: 32,080
no you can't...all sys.* items are read only views; MS wanted to remove the ability to update meta data tables directly, because too many times an unintended consequence resulted in corrupt databases when you could do stuff like that in SQL 2000;
you'll need to simply use the meta data to generate the scripts for your indexes to specifically include a fill factor;

at the bottom of this post is a script i wrote to script out your indexes(it does not create/recreate them...only scripts them); change the case statement near the bottom for the situation where the fill factor is not included in the definition to have your new default. you cna then tweak the resulting script so that tables you know that need more or less of a fill factor.
see if this gets you what you are after:
--1. get all indexes from current db, place in temp table
select
tablename = object_name(i.id),
tableid = i.id,
indexid = i.indid,
indexname = i.name,
i.status,
isunique = indexproperty (i.id,i.name,'isunique'),
isclustered = indexproperty (i.id,i.name,'isclustered'),
indexfillfactor = indexproperty (i.id,i.name,'indexfillfactor')
into #tmp_indexes
from sysindexes i
where i.indid > 0 and i.indid < 255 --not certain about this
and (i.status & 64) = 0 --existing indexes


--add additional columns to store include and key column lists
alter table #tmp_indexes add keycolumns varchar(4000), includes varchar(4000)
go
--################################################################################################




--2. loop through tables, put include and index columns into variables
declare @isql_key varchar(4000), @isql_incl varchar(4000), @tableid int, @indexid int

declare index_cursor cursor for
select tableid, indexid from #tmp_indexes

open index_cursor
fetch next from index_cursor into @tableid, @indexid

while @@fetch_status <> -1
begin

select @isql_key = '', @isql_incl = ''

select --i.name, sc.colid, sc.name, ic.index_id, ic.object_id, *
--key column
@isql_key = case ic.is_included_column
when 0 then
case ic.is_descending_key
when 1 then @isql_key + coalesce(sc.name,'') + ' DESC, '
else @isql_key + coalesce(sc.name,'') + ' ASC, '
end
else @isql_key end,

--include column
@isql_incl = case ic.is_included_column
when 1 then
case ic.is_descending_key
when 1 then @isql_incl + coalesce(sc.name,'') + ', '
else @isql_incl + coalesce(sc.name,'') + ', '
end
else @isql_incl end
from sysindexes i
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.indid AS int) AND ic.object_id=i.id)
INNER JOIN sys.columns AS sc ON sc.object_id = ic.object_id and sc.column_id = ic.column_id

where i.indid > 0 and i.indid < 255
and (i.status & 64) = 0
and i.id = @tableid and i.indid = @indexid
order by i.name, case ic.is_included_column when 1 then ic.index_column_id else ic.key_ordinal end


if len(@isql_key) > 1 set @isql_key = left(@isql_key, len(@isql_key) -1)
if len(@isql_incl) > 1 set @isql_incl = left(@isql_incl, len(@isql_incl) -1)

update #tmp_indexes
set keycolumns = @isql_key,
includes = @isql_incl
where tableid = @tableid and indexid = @indexid

fetch next from index_cursor into @tableid,@indexid
end

close index_cursor
deallocate index_cursor

--remove invalid indexes,ie ones without key columns
delete from #tmp_indexes where keycolumns = ''
--################################################################################################




--3. output the index creation scripts
set nocount on

--separator
select '---------------------------------------------------------------------'

--create index scripts (for backup)
SELECT
'CREATE '
+ CASE WHEN ISUNIQUE = 1 THEN 'UNIQUE ' ELSE '' END
+ CASE WHEN ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END
+ 'INDEX [' + INDEXNAME + ']'
+' ON [' + TABLENAME + '] '
+ '(' + keycolumns + ')'
+ CASE
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 1 AND INCLUDES = '' THEN ''
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON)'
WHEN INDEXFILLFACTOR <> 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ')'
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES <> '' THEN ' INCLUDE (' + INCLUDES + ') WITH (ONLINE = ON)'
ELSE ' INCLUDE(' + INCLUDES + ') WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ', ONLINE = ON)'
END
FROM #tmp_indexes
where left(tablename,3) not in ('sys', 'dt_') --exclude system tables
order by tablename, indexid, indexname


set nocount off

--drop table #tmp_indexes




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1063581
Posted Monday, February 14, 2011 8:42 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 43,026, Visits: 36,192
Sys.indexes isn't even a table. It's a view. The real system tables are hidden, completely undocumented and changing them is the fastest way yo really mess up your database beyond recovery.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1063582
Posted Monday, February 14, 2011 8:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 12:55 PM
Points: 169, Visits: 112
Thank You! Nice script too.

Any ways, you answered my question. I can't just update the sys.indexes table to change future rebuild/reorganize FILLFACTORs. I tried.

Pat




Pat Buskey
Post #1063587
Posted Monday, February 14, 2011 8:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 12:55 PM
Points: 169, Visits: 112
Thank You! Nice script too.

Any ways, you answered my question. I can't just update the sys.indexes table to change future rebuild/reorganize FILLFACTORs. I tried.

Pat




Pat Buskey
Post #1063588
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse