SQL Index Checks

by lokointhehouse2

JavaScript

/* CHECL BLOKING QURIES */

SELECT st.text, r.session_id, r.status, r.command, r.cpu_time, r.total_elapsed_time, r.blocking_session_id
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
order by r.total_elapsed_time desc

/* FRAGMINTATION */

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
BEGIN TRANSACTION;
GO
SELECT db.name as Database_Name, so2.name as Table_Name, si.name as Index_Name, round(ps.avg_fragmentation_in_percent,1) as Fragmentation, si2.rows as records
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
    INNER JOIN sys.indexes si ON ps.OBJECT_ID = si.OBJECT_ID AND ps.index_id = si.index_id
	INNER JOIN sys.objects so2 on si.object_id = so2.object_id
	INNER JOIN sysindexes si2 on ps.object_id = si2.id and indid < 2
	INNER JOIN sys.databases db on ps.database_id = db.database_id
    WHERE ps.database_id = DB_ID() AND si.name is not null
and ps.avg_fragmentation_in_percent > 20	--Fragmentation of 40+ is detrimental
and rows > 40000							--Rows in excesss of 100k could be impacted
    ORDER BY si2.rows desc, ps.avg_fragmentation_in_percent desc
    --ORDER BY ps.avg_fragmentation_in_percent desc, si2.rows desc 
COMMIT TRANSACTION;
GO    




/* HEALTH INDEX */

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
BEGIN TRANSACTION;
GO
SELECT mid.statement as table_name, mid.equality_columns , mid.inequality_columns, mid.included_columns, avg_total_user_cost * avg_user_impact as impact, (user_seeks + user_scans) as requested_count
FROM sys.dm_db_missing_index_group_stats stats
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON (stats.group_handle = mig.index_group_handle)
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON (mig.index_handle = mid.index_handle)
--where avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) >= 20000
ORDER BY table_name;
COMMIT TRANSACTION;
GO