Database index analysis

I find myself fine tuning the ax database more frequently to get the best possible performance. I have noticed that sometimes the default dba scripts to rebuild, reorganize indexes and update statistics for tables don't always run properly.

I use the following to break down the current avg fragmentation for a specific table

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N',<database name>'),
      OBJECT_ID(N'dbo.<table name>'), NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b
      ON a.object_id = b.object_id AND a.index_id = b.index_id; 

I also use the following script to look at overall indexes which  may need attention. The below will generate the commands to rebuild or reorganize a fragmented index along with updating a specific tables index instead of relaying on the dba scripts.

DECLARE @fragmentPercent int = 30;
SELECT OBJECT_NAME(ind.OBJECT_ID) AS Tablename,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent,
'ALTER INDEX ' + ind.name + ' ON ' + OBJECT_NAME(ind.OBJECT_ID) + ' REBUILD;' as RebuildIndex,
'ALTER INDEX ' + ind.name + ' ON ' + OBJECT_NAME(ind.OBJECT_ID) + ' REORGANIZE;' as ReorganizeIndex,
'UPDATE STATISTICS ' + OBJECT_NAME(ind.OBJECT_ID) + ' WITH FULLSCAN;' as UpdateStatics
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 >= @fragmentPercent  and indexstats.index_type_desc <> 'HEAP'
ORDER BY indexstats.avg_fragmentation_in_percent DESC