High IO Waits


SELECT  DB_NAME(fs.database_id) AS [Database Name] ,
        mf.physical_name ,
        io_stall_read_ms ,
        num_of_reads ,
        CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] ,
        io_stall_write_ms ,
        num_of_writes ,
        CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] ,
        io_stall_read_ms + io_stall_write_ms AS [io_stalls] ,
        num_of_reads + num_of_writes AS [total_io] ,
        CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads
                                                          + num_of_writes )
AS NUMERIC(10,1)) AS [avg_io_stall_ms]

FROM    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
        INNER JOIN sys.master_files AS mf WITH ( NOLOCK )
        ON fs.database_id = mf.database_id
        AND fs.[file_id] = mf.[file_id]ORDER BY avg_io_stall_ms DESC
OPTION  ( RECOMPILE );

 

Generate Date Time Window Functions

Generate Date/Time data at the minute level:


-- TRUNCATE TABLE [SqlServerMap].[dbo].[date_pivot]

DECLARE @top bigint
SELECT @top = DATEDIFF(minute, '2012-01-01', '2015-01-01')

INSERT INTO  [SqlServerMap].[dbo].[date_pivot]([date_minute])
SELECT TOP(@top)
DATEADD(minute,-row_number() OVER(ORDER BY o1.id),'2015-01-01')
FROM MASTER..sysobjects o1, MASTER..sysobjects o2


SELECT MAX([date_minute]), MIN([date_minute])
FROM [SqlServerMap].[dbo].[date_pivot]

Audit Schema Changes DDL Events

DDL Events
http://msdn.microsoft.com/en-us/library/bb522542(v=sql.105).aspx

USE dba
GODROP TABLE [dbo].[ChangeLog]
GOCREATE TABLE [dbo].[ChangeLog](
   [LogId] [int] IDENTITY(1,1) NOT NULL,
   [EventType] [varchar](50) NOT NULL,
  [PostTime] [datetime] NOT NULL CONSTRAINT [DF_ChangeLog_EventDate]  DEFAULT (GETDATE()),
   [SPID] INT NOT NULL,
   [ServerName] SYSNAME NOT NULL,
   [LoginName] SYSNAME NOT NULL,
   [UserName] SYSNAME NOT NULL,
   [DatabaseName] SYSNAME NOT NULL,
   [ObjectName] SYSNAME NOT NULL,
   [ObjectType] [varchar](25) NOT NULL,
   [TSQLCommand] [varchar](MAX) NOT NULL
) ON [PRIMARY]

DROP TRIGGER schema_change_log ON DATABASEGOCREATE TRIGGER schema_change_log ON DATABASE
FOR create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_functionAS

SET NOCOUNT ON

DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO dba.dbo.ChangeLog(EventType, SPID, ServerName, LoginName, UserName, DatabaseName, ObjectName, ObjectType, TSQLCommand)VALUES(@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int'),@data.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname'),@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),@data.value('(/EVENT_INSTANCE/UserName)[1]', 'sysname'),@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname'),@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'), @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')
)
-- select @dataGOCREATE TABLE a(id INT)SELECT * FROM [dbo].[ChangeLog]DROP TABLE a

AUTO_UPDATE_STATISTICS_ASYNC

Queries will wait for updated statistics to complete if the statistics update process is running. 
On very write intensive systems this can cause application timeouts to occur.  

This setting will tell queries to use the existing statistics until the new statistics have completed being written.

For this setting to work Automatic statistics update must be enabled.


ALTER DATABASE MyDatabase SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE MyDatabase SET AUTO_UPDATE_STATISTICS_ASYNC ON

exec sp_updatestats

Script Stored Procedures

SET NOCOUNT ON;
SELECT o.name AS ObjectName, -- OBJECT_SCHEMA_NAME(o.[object_id]) + '.' + o.name + '.sql' AS FileName, '
GO
USE DW_SSIS
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
if object_id(''' + QUOTENAME(OBJECT_SCHEMA_NAME(o.[object_id])) + '.' + QUOTENAME(o.name) + ''') is null
begin
    exec(''CREATE PROCEDURE ' + QUOTENAME(OBJECT_SCHEMA_NAME(o.[object_id])) + '.' + QUOTENAME(o.name) + ' as select 1'')
end
GO
'
+
REPLACE(s.definition, 'create procedure', 'alter procedure')
+
'
GO
' AS CodeDefinition
FROM sys.objects o
JOIN sys.sql_modules s ON o.[object_id] = s.[object_id]
WHERE o.TYPE = 'P'
--AND OBJECT_SCHEMA_NAME(o.[object_id]) = 'dw'
AND o.name IN('MyProc')
ORDER BY o.NAME

Manage Identity Seed Values

-- Tables with identity values:

SELECT  
    OBJECT_SCHEMA_NAME(t.OBJECT_ID) AS SchemaName,
    t.name AS TableName,
    c.NAME AS ColumnName,
    c.SEED_VALUE,
    c.INCREMENT_VALUE,
    c.LAST_VALUE,
    c.IS_NOT_FOR_REPLICATION,
    t.is_replicated
FROM sys.tables t
JOIN SYS.IDENTITY_COLUMNS c ON t.OBJECT_ID = c.OBJECT_ID
WHERE t.is_ms_shipped = 0

Retrieve Current Identity Values
Not For Replication Settings


-- Manage Not for Replication Attribute:

ALTER TABLE [dbo].[canonical_country_alias] ALTER COLUMN [id] ADD NOT FOR REPLICATION /* or use the system stored procedure on all tables in the database */EXEC sp_msforeachtable @command1 =
'
declare @int int
set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 1
'
 

-- Update Subscriber Identity Keys to reflect next id

SET NOCOUNT ON;
DECLARE @review_only bit = 1
IF @review_only = 0BEGIN
    EXEC sp_msforeachtable @command1 = '
    declare @int int
    set @int =object_id("?")
    EXEC sys.sp_identitycolumnforreplication @int, 1
    'END

IF OBJECT_ID('tempdb..#IdentityTables') IS NULLBEGIN
    CREATE TABLE #IdentityTables(
       [SchemaName] [sysname] NOT NULL,
       [TableName] [sysname] NOT NULL,
       [ColumnName] [sysname] NULL,
       [SEED_VALUE] [bigint] NULL,
       [INCREMENT_VALUE] [int] NULL,
       [LAST_VALUE] [bigint] NULL,
        [current_identity] [bigint] NULL,
       [IS_NOT_FOR_REPLICATION] [bit] NULL,
       [is_replicated] [bit] NULL,
        [verified] [bigint] NULL
        PRIMARY KEY (TableName, SchemaName)
    ) ON [PRIMARY]END
ELSE
BEGIN
    -- DROP TABLE #IdentityTables
    TRUNCATE TABLE #IdentityTablesEND

--DROP TABLE #IdentityTablesINSERT INTO #IdentityTables(SchemaName, TableName, ColumnName, seed_value, increment_value, last_value, current_identity, is_not_for_replication, is_replicated)SELECT  
    QUOTENAME(OBJECT_SCHEMA_NAME(t.OBJECT_ID)) AS SchemaName,
    QUOTENAME(t.name) AS TableName,
    QUOTENAME(c.NAME) AS ColumnName,
    CONVERT(bigint,c.SEED_VALUE) AS seed_value,
    CONVERT(bigint,c.INCREMENT_VALUE) AS increment_value,
    ISNULL(CONVERT(bigint,c.LAST_VALUE),CONVERT(bigint, c.seed_value)) AS last_value,
    IDENT_CURRENT(t.name) AS current_identity,
    c.IS_NOT_FOR_REPLICATION,
    t.is_replicatedFROM sys.tables t
    JOIN SYS.IDENTITY_COLUMNS c ON t.OBJECT_ID = c.OBJECT_IDWHERE t.is_ms_shipped = 0
    AND t.name NOT IN('sysdiagrams')
    AND t.name NOT LIKE 'cdrs%'

DECLARE @id INT = 0, @sql NVARCHAR(1500), @SchemaName SYSNAME, @TableName SYSNAME, @ColumnName NVARCHAR(128), @SEED_VALUE bigint, @LAST_VALUE bigint, @verified bigint WHILE EXISTS(SELECT * FROM #IdentityTables WHERE [verified] IS NULL) -- @id < 10 -- BEGIN
    SET @id = @id + 1
    SELECT TOP 1
        @SchemaName = SchemaName,
        @TableName = TableName,
        @ColumnName = ColumnName,
        @SEED_VALUE = SEED_VALUE,
        @LAST_VALUE = LAST_VALUE
    FROM #IdentityTables WHERE verified IS NULL

    UPDATE t SET verified =  1
    FROM #IdentityTables t
    WHERE SchemaName = @SchemaName AND TableName = @TableName
        IF @@error <> 0
            GOTO ExitMyProcess

    /*
    --select @sql  = 'select @verified = isnull(max(' + @ColumnName + '), 0) from ' + @SchemaName + '.' + @TableName
    --select @sql
        --exec sp_executesql @sql, N'@verified varchar(50) OUTPUT', @verified OUTPUT

    --if @verified > @SEED_VALUE
    --    DBCC CHECKIDENT ('providers', RESEED)
    --print @verified
    */
    SELECT @sql = 'DBCC CHECKIDENT (' + CHAR(39) + @SchemaName + '.' + @TableName +  + CHAR(39) + ', reseed, ' +
            CAST(CASE
            WHEN (@SEED_VALUE < @verified OR @SEED_VALUE < @LAST_VALUE)
                THEN
                    CASE WHEN (@verified >= @LAST_VALUE) THEN @verified ELSE @LAST_VALUE END
            ELSE
                @SEED_VALUE
            END AS VARCHAR(20)) +
            ') '
    PRINT @sql

    IF @review_only = 0
    BEGIN
        EXEC sp_executesql @sql
    END
    IF @@error <> 0
        GOTO ExitMyProcess
END /* WHILE */ExitMyProcess:SELECT * FROM #IdentityTables

INDEX DEFINITION


SELECT
sch.name+'.'+obj.name [table_name],
i.name [index_name],
i.type_desc,
REPLACE(
(SELECT '['+col.name+']' AS [data()]
   FROM sys.index_columns AS k
   INNER JOIN sys.columns col
       ON k.OBJECT_ID=col.OBJECT_ID
       AND k.column_id=col.column_id
   WHERE k.OBJECT_ID = i.OBJECT_ID
   AND k.index_id = i.index_id
   AND k.index_id<>0
   AND k.is_included_column<>1
   ORDER BY key_ordinal, k.column_id
   FOR XML path(''))
,'] [','], [') AS [index_columns],
ISNULL(
REPLACE(
   (SELECT '['+col.name+']' AS [data()]
   FROM sys.index_columns AS k
   INNER JOIN sys.columns col
       ON k.OBJECT_ID=col.OBJECT_ID
       AND k.column_id=col.column_id
   WHERE k.OBJECT_ID = i.OBJECT_ID
   AND k.index_id = i.index_id
   AND k.index_id<>0
   AND k.is_included_column<>0
   ORDER BY key_ordinal, k.column_id
   FOR XML path(''))
,'] [','], [')
,'') AS [index_included_columns],
ISNULL(i.filter_definition,'') AS filter_definition,
is_unique,
is_primary_key,
is_unique_constraint,
fill_factor,
is_padded,
allow_row_locks,
allow_page_locks,
ignore_dup_key,
ISNULL(data_compression_desc,'') AS data_compression_desc,
no_recompute
FROM sys.indexes AS i
INNER JOIN sys.objects obj
   ON i.OBJECT_ID=obj.OBJECT_ID
INNER JOIN sys.schemas sch
   ON obj.schema_id=sch.schema_id
INNER JOIN sys.partitions part
   ON i.OBJECT_ID=part.OBJECT_ID
   AND i.index_id=part.index_id
INNER JOIN sys.stats stats
   ON i.OBJECT_ID=stats.OBJECT_ID
   AND i.index_id=stats.stats_id
WHERE i.name IS NOT NULL
AND obj.TYPE<>'S'
AND sch.name<>'sys'
--and sch.name='HumanResources'
--and obj.name='Employee'
ORDER BY table_name, type_desc, index_columns


Email Table as HTML

USE
SqlServerMap
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM sys.sysobjects WHERE OBJECT_ID('dbo.usp_email_trace_results_report') IS NOT NULL AND TYPE = 'P' )
BEGIN
    EXEC('create procedure dbo.usp_email_trace_results_report as select 1')
END
GO
ALTER PROCEDURE dbo.usp_email_trace_results_report(
@Report_Timeframe_minutes INT = 61,
@profile_name VARCHAR(128)= 'DbReports',
@recipients  VARCHAR(128) = 'cbaker@telesign.com',
@subject VARCHAR(128) = 'Trace Report')
AS

/*
SAMPLE CALL:
exec SqlServerMap.dbo.usp_email_trace_results_report
    @Report_Timeframe_minutes = 61,
    @profile_name = 'DbReports',
    @recipients = 'cbaker@telesign.com',
    @subject = 'Trace Report'

*/
DECLARE @Body VARCHAR(MAX),
      @TableHead VARCHAR(MAX),
      @TableTail VARCHAR(MAX)
SET NOCOUNT ON;SET @TableHead = '<html><head>' +
                  '<style>' +
                  'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:8pt;} ' +
                  '</style>' +
                  '</head>' +
                  '<body><table cellpadding=0 cellspacing=0 border=0>' +
                  '<tr bgcolor=#FFEFD8><td align=center><b>Counts</b></td>' +
                  '<td align=center><b>MaxDur</b></td>' +
                  '<td align=center><b>AsOf</b></td>' +
                  '</tr>';SELECT @Body = (
                SELECT TOP 10  Row_Number() OVER(ORDER BY d.AsOf, d.AsOf) % 2 AS [TRRow],
                    counts AS [TD],
                    MaxDur AS [TD],
                    LEFT(CAST(CAST(d.AsOf AS smalldatetime) AS time), 5) AS [TD align=center]
                FROM sqlservermap.dbo.v_duration_counts d (nolock)
                WHERE DATEDIFF(minute, Asof, GETDATE()) < @Report_Timeframe_minutes
                ORDER BY d.asof DESC
                FOR XML raw('tr'), Elements
                );SET @Body = REPLACE(@Body, '_x0020_', SPACE(1))SET @Body = REPLACE(@Body, '_x003D_', '=')SET @Body = REPLACE(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
SET @Body = REPLACE(@Body, '<TRRow>0</TRRow>', '')SET @TableTail = '</table></body></html>';SELECT @Body = @TableHead + @Body + @TableTail-- Select @Body
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile_name, -- 'DbReports',
@recipients = @recipients, -- 'cbaker@telesign.com',
@subject = @subject, -- 'Trace Report',
@Body = @Body,@body_format = 'HTML'GO

SSIS Error Report Email

http://thebakingdba.blogspot.com/2012/11/sql-server-2012-ssis-getting-useful.html


USE MASTERGOIF NOT EXISTS(SELECT TOP 1 1 FROM SYS.SYSOBJECTS WHERE OBJECT_ID('dbo.ssis_job_failure_info') IS NOT NULL AND TYPE = 'P')BEGIN
    EXEC ('create procedure dbo.ssis_job_failure_info as print 1')ENDGOALTER PROCEDURE dbo.ssis_job_failure_info
  @job_id UNIQUEIDENTIFIER,
  @current_step_id INT,
  @email_profile_name VARCHAR(128) = 'DbReports',
  @email_recipients VARCHAR(128) = 'cbaker@telesign.com'AS
DECLARE @full_ssis_command VARCHAR(4000)
  , @job_step_id INT
  , @package_name VARCHAR(4000)
  , @tableHTML NVARCHAR(MAX)
        ,   @MailSubject VARCHAR(200)
        , @job_name VARCHAR(100)

--token replacement happens in the job.
--select @job_id = $(ESCAPE_SQUOTE(JOBID)), @current_step_id = $(ESCAPE_SQUOTE(STEPID))
SELECT @job_name = name FROM msdb.dbo.sysjobs WHERE job_id = @job_id

--determine which job_step failed.
SELECT TOP 1 @job_step_id = step_id FROM msdb.dbo.sysjobhistory WHERE run_status <> 1 AND step_id > 0 AND job_id = @job_id AND step_id <> @current_step_id ORDER BY instance_id DESC

--now find the package name
SELECT @full_ssis_command = command FROM msdb.dbo.sysjobsteps WHERE job_id = @job_id AND step_id = @job_step_id
IF @full_ssis_command LIKE '%.dtsx%'
BEGIN
SELECT @package_name = RIGHT(LEFT(@full_ssis_command,CHARINDEX('.dtsx',@full_ssis_command)-1),CHARINDEX('\',REVERSE(LEFT(@full_ssis_command,CHARINDEX('.dtsx',@full_ssis_command)-1)))-1)+'.dtsx'
END

--goes in the error log, if you have one
SELECT
    [message_time]
      ,[extended_info_id]
      ,[package_name]
      ,[message_source_name]
      ,[subcomponent_name]
      ,[package_path]
      ,[execution_path]
      ,LEFT([message],400)
  FROM ssisdb.[catalog].[event_messages]
    WHERE [package_name] = @package_name
       AND event_name = 'OnError'
       AND operation_id IN (SELECT MAX(operation_id)
  FROM ssisdb.[catalog].[event_messages]
  WHERE [package_name] = @package_name)
ORDER BY message_time ASC


SELECT  @MailSubject = 'Job Failure on ' + @@servername + ': ' + @job_name FROM msdb.dbo.sysjobs WHERE job_id = @job_id

      SET @tableHTML = N'<H3>Error for job ' + @job_name + '</H3>'
            + N'<table border="1">'
            + N'<th>Message_Time</th>'
            + N'<th>Extended_info_id</th>'
            + N'<th>Package_Name</th>'
            + N'<th>Message_Source_Name</th>'
            + N'<th>subcomponent_name</th>'
            + N'<th>package_path</th>'
            + N'<th>execution_path</th>'
            + N'<th>message</th>'
            + CAST(( SELECT td = CONVERT(VARCHAR(24),message_time,121)
            , ''
            , td = CONVERT(VARCHAR(10), ISNULL(extended_info_id,''))
            , ''
            , td = CONVERT(VARCHAR(50), RTRIM(LTRIM(ISNULL(package_name,''))))
            , ''
            , td = CONVERT(VARCHAR(50),RTRIM(LTRIM(ISNULL([message_source_name],''))))
            , ''
            , td = CONVERT(VARCHAR(50), RTRIM(LTRIM(ISNULL([subcomponent_name],''))))
            , ''
            , td = CONVERT(VARCHAR(50), RTRIM(LTRIM(ISNULL([package_path],''))))
            , ''
            , td = CONVERT(VARCHAR(50),RTRIM(LTRIM(ISNULL([execution_path],''))))
            , ''
            , td = CONVERT(VARCHAR(400),RTRIM(LTRIM(LEFT(ISNULL([message],''),400))))
      FROM ssisdb.[catalog].[event_messages]
     WHERE [package_name] = @package_name
        AND event_name = 'OnError'
        AND operation_id IN (SELECT MAX(operation_id)
      FROM ssisdb.[catalog].[event_messages]
      WHERE [package_name] = @package_name
            AND event_name = 'OnError')
   ORDER BY event_messages.message_time


            FOR      XML PATH('tr')
                           , TYPE ) AS NVARCHAR(MAX)) + N'</table>' ;
  
      --PRINT @tableHTML      
     EXEC msdb.dbo.sp_send_dbmail @profile_name = @email_profile_name,
          @recipients = @email_recipients,
          @subject = @MailSubject,
          @body = @tableHTML,
          @body_format = 'HTML' ;

GO


Statistics Last Update DateTime


SELECT  [schema_name] = SCHEMA_NAME(T.[schema_id]),
        table_name = T.name,        
        T.type_desc,
        index_or_statistics_name = S.name,
        is_auto_stats = S.auto_created,
        user_created = S.user_created,
        last_updated = STATS_DATE(T.[object_id], S.stats_id)
FROM    sys.tables T
JOIN    sys.stats S
        ON  S.[object_id] = T.[object_id]
ORDER   BY
        STATS_DATE(T.[object_id], S.stats_id) DESC,
        T.[schema_id],
        T.name,
        S.stats_id;