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

Monday, March 16, 2015

Fixing VLF issues for all databases on a server

Note: This script presumes that databases are in SIMPLE recovery mode (as it was at this client site at the time). It reduces log file size to minimum, then increases it to what it was before it was shrunk, in one big alter. At the same time, it changes autogrowth to 10% across the board.




 


/*


select db_name(),(size+127)/128,* from sys.sysfiles where fileid = 2


DBCC SHRINKFILE (N'IMChartData_log' , 2)


GO


ALTER DATABASE [AMDDEMO] MODIFY FILE ( NAME = N'IMChartData_log', SIZE = 267264KB )


GO


*/


DECLARE @cmd VARCHAR(6000)


SET @cmd = 'USE [?] ;


declare @filename varchar(1000), @dbcc varchar(2000), @alter varchar(1000), @size int


select @filename = name, @size = (size+127)/128 from sys.sysfiles where filename like ''%ldf''


print db_name()


print @filename


set @dbcc = ''dbcc shrinkfile (xx, 2)''


set @dbcc = REPLACE(@dbcc, ''xx'' ,@filename)


set @alter = ''ALTER DATABASE ['' + db_name() +


''] MODIFY FILE ( NAME = xx, SIZE = '' + convert(varchar, @size ) + ''MB, FILEGROWTH = 10%)''


set @alter = REPLACE(@alter, ''xx'' ,@filename)


print @dbcc


print @alter


exec (@dbcc)


exec (@alter)


'


Exec sp_msforeachdb @cmd

Tuesday, February 3, 2015

How big are the tables in my databases?

I stole this code, and put it here so that I could find it. Have fun.

SELECT

t.NAME AS TableName,

s.Name AS SchemaName,

p.rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN

sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.NAME NOT LIKE 'dt%'

AND t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

GROUP BY

t.Name, s.Name, p.Rows

ORDER BY

TotalSpaceKB desc




-- t.Name

Wednesday, October 8, 2014

List procedures and their dependent tables

select

distinct s1.name as 'Procedure Schema Name', o1.name as 'Procedure Name', s2.name as 'Table Schema Name', o2.name as 'Table Name' from sysdepends d


join sys.objects o1 on d.id = o1.object_id


join sys.schemas s1 on o1.schema_id = s1.schema_id


join sys.objects o2 on d.depid = o2.object_id


join sys.schemas s2 on o2.schema_id = s2.schema_id


where


o1.type = 'P' and


o2.type = 'U'

order

by 1,2,3,4