Controling Fragmentation and Page Usage

Updating statistics (choose the best one...)

-- EXEC sp_updatestats
-- Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.
-- http://msdn.microsoft.com/en-us/library/ms173804(v=sql.110).aspx
--EXEC sp_updatestats
--GO

-- http://msdn.microsoft.com/en-us/library/ms187348.aspx
EXEC sp_MSforeachtable @command1="UPDATE STATISTICS ? WITH FULLSCAN"
GO

-- Do not run DBCC UPDATEUSAGE routinely for databases created in SQL Server 2005 or higher or on upgraded databases that have been corrected once by using DBCC UPDATEUSAGE. Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should not be used only unless you suspect incorrect values are being returned by sp_spaceused.
-- Consider running DBCC UPDATEUSAGE routinely (for example, weekly) only if the database undergoes frequent Data Definition Language (DDL) modifications, such as CREATE, ALTER, or DROP statements.
-- http://msdn.microsoft.com/en-us/library/ms188414.aspx
DBCC UPDATEUSAGE(0) --If 0 is specified, the current database is used.
GO

Getting the information...

SELECT
QUOTENAME(b.name) AS IndexName
, QUOTENAME(e.name)+'.'+QUOTENAME(a.name) AS TableName
--, b.OBJECT_ID
--, b.index_id
, CASE indexproperty(a.object_id, b.name, 'indexfillfactor')
WHEN 0 THEN 100
ELSE indexproperty(a.object_id, b.name, 'indexfillfactor')
END
AS actFillFactor
, ISNULL(ROUND(avg_fragmentation_in_percent,3),0) AS Fragmentation
, ISNULL(ROUND(avg_page_space_used_in_percent,3),0) AS PageUsage
, STATS_DATE(a.OBJECT_ID, sc.stats_id) AS StatsDate
, ISNULL(a.modify_date,GETDATE()) AS ModifyDate
, ISNULL(Page_Count*8/1024.0,0) AS DataSpace_MB
, ISNULL(ROUND(avg_record_size_in_bytes/1024,3),0) AS AvgRowSize_KB
, ISNULL(record_count,0) AS RowsCount
, ISNULL(Detail.fragment_count,0) AS FragmentCount
, ISNULL(Page_Count,0) AS PageCount
--, ISNULL(d.system_type_id,0) AS [type_id]
, b.TYPE AS IndexType
, d.max_length AS PrimaryColumnLength
, ISNULL(t.name,'') AS FirstKey_DataType
, is_primary_key
, is_unique
, is_identity
--,*
FROM sys.objects a JOIN sys.indexes b
ON b.object_id = a.object_id
JOIN sys.index_columns c
ON c.object_id = b.object_id AND c.index_id = b.index_id
JOIN sys.columns d
ON d.object_id = c.object_id AND d.column_id = c.column_id
JOIN sys.schemas e
ON e.schema_id = a.schema_id
INNER JOIN sys.stats s
ON s.object_id = a.object_id
INNER JOIN sys.stats_columns sc
ON s.stats_id = sc.stats_id AND s.object_id=sc.object_id AND sc.column_id = c.column_id
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'SAMPLED') Detail
ON Detail.index_id = b.index_id AND Detail.object_id = b.object_id
INNER JOIN sys.types t
ON t.system_type_id = d.system_type_id
WHERE a.type = 'U'
AND s.name=b.name
AND a.is_ms_shipped = 0
AND a.object_id NOT IN (SELECT major_id FROM sys.extended_properties WHERE name = N'microsoft_database_tools_support')
AND b.name IS NOT NULL
AND c.index_column_id = 1
AND b.type IN (1,2)
AND Fragment_Count > 10
ORDER BY
--Current_Fragmentation DESC,
PageUsage ASC

Making decisions...

ALTER INDEX <INDEX>|ALL ON <SCHEMA>.<TABLE>
REBUILD WITH (FILLFACTOR=100, SORT_IN_TEMPDB=OFF, STATISTICS_NORECOMPUTE=OFF);
GO