Unused indexes -- updated script

DECLARE @cmd VARCHAR(6000)

DECLARE @TempIndexDrop TABLE (
       [Database_Name] [nvarchar](128) NULL,
       [Object_Name] [nvarchar](128) NULL,
       [Index_Name] [sysname] NULL,
       [Drop_Statement] [nvarchar](283) NULL,
       [user_seeks] [bigint]  NULL,
       [user_scans] [bigint]  NULL,
       [user_lookups] [bigint]  NULL,
       [SpaceSavedMeg] decimal (12,2)  null
) 

SET @cmd = 'USE [?] ;
select db_name(database_id) as ''Database_Name'', object_name(i.object_id) as ''Object_Name'',
i.name as ''Index_Name''
, ''USE ['' + db_name(database_id) + '']; drop index [''  + i.name + ''] on ['' + sch.name + ''].['' + obj.name + '']'' as ''Drop_Statement'',
user_seeks, user_scans, user_lookups,
(select sum(s.used_page_count)*8./1024. from sys.dm_db_partition_stats s where i.object_id = s.object_id and i.index_id = s.index_id)
from sys.dm_db_index_usage_stats ius join sys.indexes i
on ius.index_id = i.index_id and ius.object_id = i.object_id join sys.objects obj
on ius.object_id = obj.object_id join            sys.schemas sch
on            obj.schema_id = sch.schema_id
where      
database_id = db_id(''?'') and
user_seeks + user_scans + user_lookups = 0
and i.name not like ''pk%''
and ''?'' not in (''master'', ''model'',''tempdb'',''msdb'')

order by       object_name(i.object_id),       i.name'

INSERT INTO @TempIndexDrop 
(
[Database_Name]
,[Object_Name]
,[Index_Name] 
,[Drop_Statement] 
,[user_seeks]
,[user_scans]
,[user_lookups]
,[SpaceSavedMeg]
)
Exec sp_msforeachdb @cmd

SELECT SUM(SpaceSavedMeg) as 'Potential Storage Savings' FROM  @TempIndexDrop

SELECT *
FROM @TempIndexDrop
ORDER BY [Database_Name], [Object_Name]

Missing indexes -- revised script

Thanks to Darren Myher (https://DarrenMyher.com) for a few intelligent tweaks as well as some useful formatting for this revised version. UPDATED 12/9/24 for SQL Azure

--Use this SQL Script to identify indexes that if added could help improve performance.

--IMPORTANT: Do not BLINDLY implement the script recommendations.  For example, if the SAME TABLE is mentioned multiple times, then

--           it may be better to COMBINE the recommendations of multiple statements into a single one.

--           see later posting on this blog for detail

DECLARE @Edition varchar(50)

SET @Edition = CONVERT(varchar(50), SERVERPROPERTY('Edition'))

DECLARE @bEnterpriseEdition bit

IF LEFT(@Edition, 9) in ('Enterpris', 'Developer', 'SQL Azure')  

SET @bEnterpriseEdition = 1

SELECT

      'CREATE INDEX IX_'

            + REPLACE(obj.Name, ' ', '') +'_'

            + replace(replace(replace (equality_columns, '[', ''),']',''),', ','_')

            + ' ON '

            + sch.name COLLATE SQL_Latin1_General_CP1_CI_AS + '.' + QuoteName(obj.name)

            + ' ('

            + equality_columns

            + CASE WHEN inequality_columns IS NULL

                   THEN ''

                   ELSE ',' +  inequality_columns

              END

            + ')'

            + CASE WHEN included_columns IS NOT NULL

                   THEN ' INCLUDE (' + ISNULL(included_columns,'') + ') '

                   ELSE ''

              END

            + CASE WHEN @bEnterpriseEdition = 1

                   THEN ' WITH (online = ON)'

                   ELSE ''

              END

            + ' -- ' + CONVERT (varchar, avg_user_impact) + '% anticipated impact'

      FROM sys.dm_db_missing_index_details mid

        JOIN sys.dm_db_missing_index_groups mig

            ON mid.index_handle = mig.index_handle

        JOIN sys.dm_db_missing_index_group_stats migs

            ON migs.group_handle = mig.index_group_handle

        JOIN sys.objects obj

            ON obj.object_id = mid.object_id

        JOIN sys.schemas sch

            ON obj.schema_id = sch.schema_id

      WHERE

        database_id = db_id()

        AND avg_user_impact > 60

        AND equality_columns IS NOT NULL

      ORDER BY

        obj.name

        , equality_columns

        , inequality_columns

        , included_columns

What's going on right now?

Got the following code from the site I'm at today, I'll be using it for a while. The first chunk of code grabs current query plans; the second chunk looks at how memory is being used. Enjoy.

EXEC dbo.sp_WhoIsActive

@filter = '', -- varchar(128)

@filter_type = 'session', -- varchar(10)

@not_filter = '', -- varchar(128)

@not_filter_type = 'session', -- varchar(10)

@show_own_spid = 1, -- bit

@show_sleeping_spids = 0, -- tinyint

@get_full_inner_text = 1, -- bit

@get_plans = 1, -- tinyint

@get_outer_command = 1, -- bit

@get_task_info = 0, -- tinyint

@get_locks = 0, -- bit

@get_avg_time = 0, -- bit

@find_block_leaders = 1 -- bit

SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName],

p.index_id, COUNT(*)/128 AS [Buffer size(MB)],  COUNT(*) AS [BufferCount],

p.data_compression_desc AS [CompressionType], a.type_desc, p.[rows]

FROM sys.allocation_units AS a WITH (NOLOCK)

INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK)

ON a.allocation_unit_id = b.allocation_unit_id

INNER JOIN sys.partitions AS p WITH (NOLOCK)

ON a.container_id = p.partition_id

WHERE b.database_id = CONVERT(int,DB_ID())

AND p.[object_id] > 100

GROUP BY p.[object_id], p.index_id, p.data_compression_desc, a.type_desc, p.[rows]

ORDER BY [BufferCount] DESC OPTION (RECOMPILE);