Better Index Usage DMV

Slightly altered Aaron Bertrand’s “Better Index Usage DMV” to script out CREATE INDEX-statements.

 

SELECT
 d.[object_id],
 s = OBJECT_SCHEMA_NAME(d.[object_id]),
 o = OBJECT_NAME(d.[object_id]),
 d.equality_columns,
 d.inequality_columns,
 d.included_columns,
 s.unique_compiles,
 s.user_seeks, s.last_user_seek,
 s.user_scans, s.last_user_scan,
 'CREATE INDEX [IX_' + OBJECT_NAME(d.OBJECT_ID,d.database_id) + '_'
 + REPLACE(REPLACE(REPLACE(ISNULL(d.equality_columns,''),', ','_'),'[',''),']','') +
 CASE
 WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN '_'
 ELSE ''
 END
 + REPLACE(REPLACE(REPLACE(ISNULL(d.inequality_columns,''),', ','_'),'[',''),']','')
 + ']'
 + ' ON ' + d.statement
 + ' (' + ISNULL (d.equality_columns,'')
 + CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ',' ELSE
 '' END
 + ISNULL (d.inequality_columns, '')
 + ')'
 + ISNULL (' INCLUDE (' + d.included_columns + ')', '') AS Create_Statement
 INTO #candidates
 FROM sys.dm_db_missing_index_details AS d
 INNER JOIN sys.dm_db_missing_index_groups AS g
 ON d.index_handle = g.index_handle
 INNER JOIN sys.dm_db_missing_index_group_stats AS s
 ON g.index_group_handle = s.group_handle
 WHERE d.database_id = DB_ID()
 AND OBJECTPROPERTY(d.[object_id], 'IsMsShipped') = 0;
 CREATE TABLE #planops
 (
 o INT,
 i INT,
 h VARBINARY(64),
 uc INT,
 Scan_Ops INT,
 Seek_Ops INT,
 Update_Ops INT
 );
DECLARE @sql NVARCHAR(MAX) = N'';
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
 insert #planops
 select o = coalesce(T1.o, T2.o),
 i = coalesce(T1.i, T2.i),
 h = coalesce(T1.h, T2.h),
 uc = coalesce(T1.uc, T2.uc),
 Scan_Ops = isnull(T1.Scan_Ops, 0),
 Seek_Ops = isnull(T1.Seek_Ops, 0),
 Update_Ops = isnull(T2.Update_Ops, 0)
 from
 (
 select o = i.object_id,
 i = i.index_id,
 h = t.plan_handle,
 uc = t.usecounts,
 Scan_Ops = sum(case when t.LogicalOp in ('Index Scan', 'Clustered Index Scan') then 1 else 0 end),
 Seek_Ops = sum(case when t.LogicalOp in ('Index Seek', 'Clustered Index Seek') then 1 else 0 end)
 from (
 select
 r.n.value('@LogicalOp', 'varchar(100)') as LogicalOp,
 o.n.value('@Index', 'sysname') as IndexName,
 pl.plan_handle,
 pl.usecounts
 from sys.dm_exec_cached_plans as pl
 cross apply sys.dm_exec_query_plan(pl.plan_handle) AS p
 cross apply p.query_plan.nodes('//RelOp') as r(n)
 cross apply r.n.nodes('*/Object') as o(n)
 where p.dbid = db_id()
 and p.query_plan is not null
 ) as t
 inner join sys.indexes as i
 on t.IndexName = quotename(i.name)
 where t.LogicalOp in ('Index Scan', 'Clustered Index Scan', 'Index Seek', 'Clustered Index Seek')
 and exists (select 1 from #candidates as c where c.object_id = i.object_id)
 group by i.object_id,
 i.index_id,
 t.plan_handle,
 t.usecounts
 ) as T1
 full outer join
 (
 select o = i.object_id,
 i = i.index_id,
 h = t.plan_handle,
 uc = t.usecounts,
 Update_Ops = count(*)
 from (
 select
 o.n.value('@Index', 'sysname') as IndexName,
 pl.plan_handle,
 pl.usecounts
 from sys.dm_exec_cached_plans as pl
 cross apply sys.dm_exec_query_plan(pl.plan_handle) AS p
 cross apply p.query_plan.nodes('//Update') as r(n)
 cross apply r.n.nodes('Object') as o(n)
 where p.dbid = db_id()
 and p.query_plan is not null
 ) as t
 inner join sys.indexes as i
 on t.IndexName = quotename(i.name)
 where exists
 (
 select 1 from #candidates as c where c.[object_id] = i.[object_id]
 )
 and i.index_id > 0
 group by i.object_id,
 i.index_id,
 t.plan_handle,
 t.usecounts
 ) as T2
 on T1.o = T2.o and
 T1.i = T2.i and
 T1.h = T2.h and
 T1.uc = T2.uc;
 SELECT [object_id], index_id, user_seeks, user_scans, user_lookups, user_updates
 INTO #indexusage
 FROM sys.dm_db_index_usage_stats AS s
 WHERE database_id = DB_ID()
 AND EXISTS (SELECT 1 FROM #candidates WHERE [object_id] = s.[object_id]);
 ;WITH x AS
 (
 SELECT
 c.[object_id],
 potential_read_ops = SUM(c.user_seeks + c.user_scans),
 [write_ops] = SUM(iu.user_updates),
 [read_ops] = SUM(iu.user_scans + iu.user_seeks + iu.user_lookups),
 [write:read ratio] = CONVERT(DECIMAL(18,2), SUM(iu.user_updates)*1.0 /
 SUM(iu.user_scans + iu.user_seeks + iu.user_lookups)),
 current_plan_count = po.h,
 current_plan_use_count = po.uc
 FROM
 #candidates AS c
 LEFT OUTER JOIN
 #indexusage AS iu
 ON c.[object_id] = iu.[object_id]
 LEFT OUTER JOIN
 (
 SELECT o, h = COUNT(h), uc = SUM(uc)
 FROM #planops GROUP BY o
 ) AS po
 ON c.[object_id] = po.o
 GROUP BY c.[object_id], po.h, po.uc
 )
 SELECT [object] = QUOTENAME(c.s) + '.' + QUOTENAME(c.o),
 c.equality_columns,
 c.inequality_columns,
 c.included_columns,
 x.potential_read_ops,
 x.write_ops,
 x.read_ops,
 x.[write:read ratio],
 x.current_plan_count,
 x.current_plan_use_count,
 c.create_statement
 FROM #candidates AS c
 INNER JOIN x
 ON c.[object_id] = x.[object_id]
 ORDER BY x.[write:read ratio];
/*
 drop table #candidates
 drop table #indexusage
 drop table #planops
 */

SP_HELP_REVLOGIN

Very useful to transfer logins with their passwords intact.

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO
 
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname
 
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
 
        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
 
            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

Delete large number of rows

SELECT 'Starting' --sets @@ROWCOUNT
 WHILE @@ROWCOUNT <>; 0
 DELETE TOP (xxx) MyTable

Table size information

SELECT 
t.NAME AS TableName, 
i.name as indexName, 
sum(p.rows) as RowCounts, 
sum(a.total_pages) as TotalPages, 
sum(a.used_pages) as UsedPages, 
sum(a.data_pages) as DataPages, 
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB 
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 
WHERE t.NAME NOT LIKE 'dt%' 
AND i.OBJECT_ID > 255 
AND i.index_id <= 1 
GROUP BY t.NAME, i.object_id, i.index_id, i.name 
ORDER BY object_name(i.object_id)

Erland Sommarskogs wait_stats_monitor

CREATE PROCEDURE wait_stats_monitor AS
SET NOCOUNT ON
DECLARE @latest datetime,
 @now    datetime
SELECT @now = getdate()
IF object_id('waitstats') IS NULL
 BEGIN
 CREATE TABLE waitstats (
 sample_time       datetime     NOT NULL,
 wait_type         nvarchar(60) NOT NULL,
 sample_length_sec int          NULL,
 delta_tasks_count bigint       NULL,
 delta_wait_time   bigint       NULL,
 delta_signal      bigint       NULL,
 max_wait_time_ms  bigint       NOT NULL,
 tot_tasks_count   bigint       NOT NULL,
 tot_wait_time     bigint       NOT NULL,
 tot_signal        bigint       NOT NULL,
 CONSTRAINT pk_waitstats PRIMARY KEY (wait_type, sample_time)
 )
CREATE INDEX sample_time ON waitstats (sample_time)
 END
IF NOT EXISTS (SELECT * FROM waitstats)
 BEGIN
 INSERT waitstats (sample_time, wait_type, max_wait_time_ms,
 tot_tasks_count, tot_wait_time, tot_signal)
 SELECT @now, wait_type, max_wait_time_ms,
 waiting_tasks_count, wait_time_ms, signal_wait_time_ms
 FROM   sys.dm_os_wait_stats
 END
 ELSE
 BEGIN
 SELECT @latest = MAX(sample_time) FROM waitstats
INSERT waitstats (sample_time, wait_type, sample_length_sec,
 delta_tasks_count,
 delta_wait_time,
 delta_signal,
 max_wait_time_ms, tot_tasks_count, tot_wait_time,
 tot_signal)
 SELECT @now, ws.wait_type, datediff(ss, m.sample_time, @now),
 ws.waiting_tasks_count - m.tot_tasks_count,
 ws.wait_time_ms        - m.tot_wait_time,
 ws.signal_wait_time_ms - m.tot_signal,
 ws.max_wait_time_ms, ws.waiting_tasks_count, ws.wait_time_ms,
 ws.signal_wait_time_ms
 FROM   sys.dm_os_wait_stats ws
 CROSS  APPLY (SELECT TOP 1 *
 FROM   waitstats m
 WHERE  m.wait_type = ws.wait_type
 ORDER  BY sample_time DESC) m
 WHERE  ws.waiting_tasks_count <> m.tot_tasks_count OR
 ws.wait_time_ms        <> m.tot_wait_time   OR
 ws.signal_wait_time_ms <> m.tot_signal
 END

Get statements from waiter list

if exists (select * from sys.objects where object_id = object_id(N'[dbo].[get_statements_from_waiter_list]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1)
 drop procedure [dbo].[get_statements_from_waiter_list]
 go
create proc get_statements_from_waiter_list (@wait_type nvarchar(60)=NULL)
 as
 select
 r.wait_type
 ,r.wait_time
 ,SUBSTRING(qt.text,r.statement_start_offset/2,
 (case when r.statement_end_offset = -1
 then len(convert(nvarchar(max), qt.text)) * 2
 else r.statement_end_offset end -r.statement_start_offset)/2)
 as query_text
 ,qt.dbid, dbname=db_name(qt.dbid)
 ,qt.objectid
 ,r.sql_handle
 ,(SELECT query_plan FROM sys.dm_exec_query_plan (r.plan_handle)) as plan_handle
 FROM sys.dm_exec_requests r
 cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
 where r.session_id > 50
 and r.wait_type = isnull(upper(@wait_type),r.wait_type)
 go
exec get_statements_from_waiter_list