Fill Factor in Indexing - SQL Server Forums

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 2005 Forums

SQL Server Administration (2005)

Fill Factor in Indexing

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2008-02-21 : 19:28:36

Hi there

Is there any such thing that you can find the current Fill Factor for each indexing? The only thing that you have an indication is by looking at DBCC SHOWCONTIF > Scan Density [Best Count:Actual Count].......: 100.00% [0:0] and if this value is not reaching 100% means may have an issue with fill factor ?!!?

Also, how about SQL2000?

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-21 : 22:44:07

It's in sysindexes table.

cybertron
Starting Member

4 Posts

Posted - 2008-02-26 : 06:18:06

you use this query:

SELECT
C.name TableName,
B.name IndexName,
(isnull(A.user_seeks, 0) + isnull(A.user_scans, 0) + isnull(A.user_lookups, 0) +
isnull(A.system_seeks, 0) + isnull(A.system_scans, 0) + isnull(A.system_lookups, 0))
total_used, A.user_seeks, A.user_scans, A.user_lookups, A.user_updates, A.system_seeks, A.system_scans,
A.system_lookups,
A.system_updates,
A.last_user_seek,
A.last_user_scan,
A.last_user_lookup,
A.last_user_update,
A.last_system_seek,
A.last_system_scan,
A.last_system_lookup,
A.last_system_update,
B.type_desc,
B.is_unique,
D.name DataSpaceName,
D.type DataSpaceType,
D.is_default DataSpaceIsDefault,
B.ignore_dup_key,
B.is_primary_key,
B.is_unique_constraint,
B.fill_factor,
B.is_padded,
B.is_disabled, B
.is_hypothetical,
B.allow_row_locks,
B.allow_page_locks
FROM sys.indexes B
LEFT JOIN sys.dm_db_index_usage_stats A
ON A.index_id = B.index_id and A.object_id = B.object_id
AND A.database_id = @DBID
INNER JOIN sys.tables C
ON B.object_id = C.object_id
INNER JOIN sys.data_spaces D
ON B.data_space_id = D.data_space_id
--WHERE C.name = 'SUBSIDY_REFUND_AMORTIZED' -- specify your table here
ORDER BY (A.user_seeks + A.user_scans + A.user_lookups + A.system_seeks + A.system_scans + A.system_lookups) desc

http://cybertron-network.blogspot.com/2008/02/how-to-find-unused-index-on-sql-server.html

i've used this query for finding unused index. and also the result can resolve any problem, for searching fill factor for example

---------------------------------------------------------------------------------------------------

"We give you that we know, We share to you that we've learned, We send to you that we need to be discussed"
http://cybertron-network.blogspot.com

dewacorp.alliances

452 Posts

Posted - 2008-02-27 : 04:34:51

Do you know the similar query for SQL2K?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-27 : 05:33:56

no. sql server 2000 doesn't have anything like that.
you'll have to run index tuning wizard.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources