SQL 2016

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS S INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 AND S.database_id = DB_ID() AND USER_UPDATES > 50000 AND (USER_SEEKS + USER_SCANS + USER_LOOKUPS) < 1000 ORDER BY USER_UPDATES desc

SELECT TOP 25 dm_mid.database_id AS DatabaseID, dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact, dm_migs.last_user_seek AS Last_User_Seek, OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName], 'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','') + ']' + ' ON ' + dm_mid.statement + ' (' + ISNULL (dm_mid.equality_columns,'') + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (dm_mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement FROM sys.dm_db_missing_index_groups dm_mig INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle WHERE dm_mid.database_ID = DB_ID() ORDER BY Avg_Estimated_Impact DESC

SELECT cp.objtype AS ObjectType, OBJECT_NAME(st.objectid,st.dbid) AS ObjectName, cp.usecounts AS ExecutionCount, st.TEXT AS QueryText, qp.query_plan AS QueryPlan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st --WHERE OBJECT_NAME(st.objectid,st.dbid) = 'YourObjectName' SELECT type as 'plan cache store', buckets_count FROM sys.dm_os_memory_cache_hash_tables WHERE type IN ('CACHESTORE_OBJCP', 'CACHESTORE_SQLCP', 'CACHESTORE_PHDR', 'CACHESTORE_XPROC'); SELECT plan_handle, pvt.set_options, pvt.object_id, pvt.sql_handle FROM (SELECT plan_handle, epa.attribute, epa.value FROM sys.dm_exec_cached_plans OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa WHERE cacheobjtype = 'Compiled Plan' ) AS ecpa PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN (set_options, object_id, sql_handle)) AS pvt; SELECT st.text, qs. sql_handle, qs.plan_handle FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) st; SELECT st.text, cp.plan_handle, cp.usecounts, cp.size_in_bytes, cp.cacheobjtype, cp.objtype FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st ORDER BY cp.usecounts DESC SELECT text, plan_handle, d.usecounts, d.cacheobjtype FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_cached_plan_dependent_objects(plan_handle) d; SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(text) ELSE statement_end_offset END - statement_start_offset)/2) + 1) AS query_text, * FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) ORDER BY total_elapsed_time DESC SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(text) ELSE statement_end_offset END - statement_start_offset)/2) + 1) AS query_text, * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle) ORDER BY total_elapsed_time/execution_count DESC; SELECT type as 'plan cache store', buckets_count FROM sys.dm_os_memory_cache_hash_tables WHERE type IN ('CACHESTORE_OBJCP', 'CACHESTORE_SQLCP'); SELECT type, count(*) total_entries FROM sys.dm_os_memory_cache_entries WHERE type IN ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP') GROUP BY type; SELECT text, objtype, refcounts, usecounts, size_in_bytes, disk_ios_count, context_switches_count, original_cost, current_cost FROM sys.dm_exec_cached_plans p CROSS APPLY sys.dm_exec_sql_text(plan_handle) JOIN sys.dm_os_memory_cache_entries e ON p.memory_object_address = e.memory_object_address WHERE cacheobjtype = 'Compiled Plan' AND type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP') ORDER BY objtype desc, usecounts DESC; dbcc memorystatus