Wednesday, April 13, 2016

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.

--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.
--           If you need help, contact Darren and/or open a support ticket with Soaring Eagle. 
DECLARE @Edition varchar(50)
SET @Edition = CONVERT(varchar(50), SERVERPROPERTY('Edition'))
DECLARE @bEnterpriseEdition bit
IF (LEFT(@Edition, 9) = 'Enterpris' OR LEFT(@Edition, 9) = 'Developer')
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);