Wednesday, April 13, 2016

Unused indexes -- updated script

DECLARE @cmd VARCHAR(6000)

CREATE TABLE tempdb..TempIndexDrop(
       [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
) ON [PRIMARY]

SET @cmd = 'USE [?] ;
INSERT INTO tempdb..TempIndexDrop select db_name(database_id) as ''Database_Name'', object_name(i.object_id) as ''Object_Name'',
i.name as ''Index_Name'', ''drop index ['' + sch.name + ''].['' + obj.name + ''].['' + i.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 <100 and="" i.name="" i.object_id="" like="" nbsp="" not="" object_name="" span="" sys="">
order by       object_name(i.object_id),       i.name'

--print @cmd

Exec sp_msforeachdb @cmd

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

SELECT * FROM tempdb..TempIndexDrop

ORDER BY Database_Name, Object_Name

DROP TABLE tempdb..TempIndexDrop

Missing indexes -- revised script

set concat_null_yields_null off
select
      'create index IX_' +
            replace(replace(replace (equality_columns, '[', ''),']',''),', ','_') +
            ' on ' +
            sch.name collate SQL_Latin1_General_CP1_CI_AS + '.' + 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 +
            ' -- ' + 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 db_name(database_id) = 'WSS_COntent_COmDoc_Intranet'
                                 
                             and            avg_user_impact > 20
                             and equality_columns is not null

      order by obj.name, equality_columns

--       avg_user_impact desc

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);