Script to find PKs on IDENTITY with low FillFactor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Script to find PKs on IDENTITY with low FillFactor

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-04-02 : 03:24:51

Anyone got a script, or can point me to one, that will find Clustered Indexes on columns with IDENTITY attribute which are NOT using 100% Fill Factor.

I've got a database to tidy up

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-02 : 05:04:47

This may be a starting point


select distinct id.name, sysin.OrigFillFactor   from sys.identity_columns as id
   inner join sys.sysindexkeys  as ikeys on id.column_id =ikeys.colid 
   inner join sys.sysindexes as sysin on sysin.indid =ikeys.indid 
   where id.object_id=ikeys.id and sysin.OrigFillFactor  >0 and sysin.OrigFillFactor  <100

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 05:24:45

here's my try at this


select i.name,OBJECT_NAME(i.object_id) AS Table_name,COL_NAME(ic.object_id,ic.column_id) AS ColumnName,i.fill_factor 
from sys.indexes i
join sys.index_columns ic
on  ic.object_id=i.object_id
and ic.index_id=i.index_id
and i.type_desc='CLUSTERED'
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON ccu.COLUMN_NAME=COL_NAME(ic.object_id,ic.column_id) 
AND ccu.TABLE_NAME=OBJECT_NAME(i.object_id) 
join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON tc.CONSTRAINT_TYPE='PRIMARY KEY'
AND tc.CONSTRAINT_NAME=ccu.CONSTRAINT_NAME
join sys.identity_columns idc
ON idc.column_id=ic.column_id
AND idc.object_id=ic.object_id
where i.fill_factor<100

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-04-02 : 09:35:08

Similar, but not identical!, results.

Visakh method:

Finding some UDFs that return @table with IDENTITY column. Interesting ... does that mean that that UDF is somehow defaulting to 90% fill factor? Might be worth my trying to fix that?

I did say Clustered Index, but it turns out that I have some NON Clustered PK on IDENTITY (and clustered index on something else). Visakh method found those (How? It seems to restrict to CLUSTERED), and Madhi did not.

Seems like it would be sensible to include ANY index that has IDENTITY and less than 100% fill-factor (well, probably only if IDENTITY field is the first key field?)

2 tables where Visakh reports 90% and Madhi reports 0% ... I'm looking into those, they show 90% in SSMS ... dunno how that comes about though ... they don't seem to be any different to the others that show 90% by both methods.

Thanks chaps, very useful in helping me sort this out.

Kristen
Test

22859 Posts

Posted - 2010-04-02 : 09:59:40

Added to Madhi method:

AND sysin.id = id.object_id

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-02 : 10:10:20