Effective way of accumulating index fragmentation statisitcs Please help me figure out a way to gather fragmentation statistics of all tables in my DB on a daily basis.

The problem is that scripts like this one:

SELECT OBJECT_NAME( ind.OBJECT_ID )
AS TableName,
       ind.name
AS IndexName,
       indexstats.index_type_desc
AS IndexType,
       indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID( ), NULL, NULL, NULL, NULL) indexstats
INNER JOIN
sys.indexes ind
ON ind.object_id=indexstats.object_id
   AND ind.index_id=indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent&gt;30
ORDER BY indexstats.avg_fragmentation_in_percent DESC;


will work forever to complete as tables in our DB are &lt;=300GB. 
My goal is to regularly gather statistics on fragmentation to implement proper fillfactor for indexes based on how heavily they get fragmented over time according to this article:

5 Things About Fillfactor
I fully expect down-votes for this because my opinion doesn't fit convention in the SQL community, but I don't think you should be focusing on fragmentation levels nor should you be adjusting the fillfactor unless absolutely necessary.

The definition of Index Fragmentation is as follows:


  The logical ordering [of pages within an index], based on the key value, does not match the physical ordering inside the data file. 


Ok, so what does that really mean?  It just means that you have an ordering mismatch between how the values are stored in the Index Tree and how they are stored on disk.  Low Index fragmentation does NOT mean data is stored contiguously or even on the same drive within a drive array.

How does Index Fragmentation hurt you?  Really, through my investigation, the only downside to Index Fragmentation that I've been able to identify is that read-ahead operations are less efficient (e.g. more are performed) against an index that is fragmented (in regards to Seeks and Range Scans operations) as opposed to said index being absent of fragmentation.  Read-Ahead operations only happen when you pull an index/table off of disk.  If you are able to maximize the time that the index remains cached, you will see less and less read-aheads negatively affect performance because you won't constantly be going back to the disk.  Additionally, Index Scan operations (as opposed to Index Seeks or Range Scans) are less troublesome because the entire index/table is coming off of disk any way.

Why does most documentation attribute High Index Fragmentation with Poor Performance?  The answer is that often times, High Index Fragmentation creates unnecessary white-space within an index due to page splits and other operations.  This white-space is the real detriment to performance because it requires more pages be read into memory to get less data.  More white space = More I/O requests = less performance.

As Kendra's article alludes to, even the BOL article makes mention that setting FILLFACTOR too low will cause performance issues.  This is because of the exact same reason.  More reads for less data is less performant.  Setting FILLFACTOR to anything other than 0 when necessary is forcefully adding whitespace to your indexes/tables.  Let's do some basic math here and see why this isn't a good blanket-recommendation against your database.

Let's say you have an index that is 100GB in a completely unfragmented state with the default FILLFACTOR set.  You set FILLFACTOR to 90 and rebuild the index.  The index will now become 111GB (100/90 * 100), which is an increase by 11%.  This also means that you'll have to read 11% more pages to get the same amount of data off of disk.  Apply this system-wide, and you've just increase read operations by 11% across the system.  The other detriment to white space is that it is not only stored on disk, but it's also stored when the page resides in memory.  That means you're reducing your memory footprint by 11% just to help "prevent" bad page splits.  Really, are page-splits so bad that you need to reduce your memory footprint and increase the amount of read operations off of disk?  This setting is a negative double-whammy.

There are definitely scenarios where adjusting FILLFACTOR is needed and in the best interest of your database, but to set it across the board in a blanket fashion is not a good idea in my opinion.  I'm not saying you shouldn't adjust FILLFACTOR, rather  you should only adjust it when other options are less desirable.

So, after my rambling, what I suggest you should be reviewing instead of index fragmentation is the avg_page_space_used_in_percent field in the sys.dm_db_index_physical_stats DMV.  If you see that creep up past 10% or higher, a reorg/rebuild would be worthwhile because this is the only way to compress white space out of an index.  Often times, I see that this percentage of white space hits when the index is significantly fragmented, generally at 60% or more.  Also, because this value doesn't creep nearly as quickly as index fragmentation, this may allow you to reduce the frequency you perform index maintenance operations.