/*
** U_Tables.CQL    --- 1996/09/16 12:22
** Copyright Microsoft, Inc. 1994 - 2000
** All Rights Reserved.
** ***********************
** If this file is updated for Denali, then do not forget to update the target level for u_table.sql
** in sql\ntdbms\mkmaster\scriptdlls\upgradesrc\sqlscriptupgrade.cpp
** 
*/

go
use master
go
set nocount on
go


declare @vdt varchar(99)
select  @vdt = convert(varchar,getdate(),113)
raiserror('Starting u_Tables.SQL at  %s',0,1,@vdt) with nowait
raiserror('This file creates all the system tables in master.',0,1)
go

-- Create a synonym spt_values in master pointing to spt_master in Resource DB,
-- for backward compat

if object_id('spt_values','U') IS NOT NULL
	begin
	print 'drop table spt_values ....'
	drop table spt_values
	end
go

if object_id('spt_values','SN') IS NOT NULL
	begin
	print 'drop synonym spt_values ....'
	drop synonym spt_values
	end
go

if object_id('spt_values','V') IS NOT NULL
	begin
	print 'drop view spt_values ....'
	drop view spt_values
	end
go

raiserror('Creating view ''%s''.', -1,-1,'spt_values')
go

create view spt_values as
select name collate database_default as name,
	number,
	type collate database_default as type,
	low, high, status
from sys.spt_values
go

EXEC sp_MS_marksystemobject 'spt_values'
go

grant select on spt_values to public
go

------------------------------------------------------------------

if object_id('spt_monitor','U') IS NOT NULL
	begin
	print 'drop table spt_monitor ....'
	drop table spt_monitor
	end
go

------------------------------------------------------------------
------------------------------------------------------------------

raiserror('Creating ''%s''.', -1,-1,'spt_monitor')
go

create table spt_monitor
(
	lastrun		datetime	NOT NULL,
	cpu_busy	int		NOT NULL,
	io_busy		int		NOT NULL,
	idle		int		NOT NULL,
	pack_received	int		NOT NULL,
	pack_sent	int		NOT NULL,
	connections	int		NOT NULL,
	pack_errors	int		NOT NULL,
	total_read	int		NOT NULL,
	total_write 	int		NOT NULL,
	total_errors 	int		NOT NULL
)
go

EXEC sp_MS_marksystemobject 'spt_monitor'
go

------------------------------------------------------------------

raiserror('Grant Select on spt_monitor',0,1)
go

grant select on spt_monitor to public
go


------------------------------------------------------------------
------------------------------------------------------------------


raiserror('Insert into spt_monitor ....',0,1)
go

insert into spt_monitor
	select
	lastrun = getdate(),
	cpu_busy = @@cpu_busy,
	io_busy = @@io_busy,
	idle = @@idle,
	pack_received = @@pack_received,
	pack_sent = @@pack_sent,
	connections = @@connections,
	pack_errors = @@packet_errors,
	total_read = @@total_read,
	total_write = @@total_write,
	total_errors = @@total_errors
go

-- Extended events default session
    if exists(select * from sys.server_event_sessions where name='system_health')
        drop event session system_health on server
    go
    -- The predicates in this session have been carefully crafted to minimize impact of event collection
    -- Changing the predicate definition may impact system performance
    --
    create event session system_health on server
    add event sqlserver.error_reported
    (
        action (package0.callstack, sqlserver.session_id, sqlserver.database_id, sqlserver.sql_text, sqlserver.tsql_stack)
        -- Get callstack, SPID, and query for all high severity errors ( above sev 20 )
        where severity >= 20
        -- Get callstack, SPID, and query for OOM errors ( 17803 , 701 , 802 , 8645 , 8651 , 8657 , 8902 )
        or (error_number = 17803 or error_number = 701 or error_number = 802 or error_number = 8645 or error_number = 8651 or error_number = 8657 or error_number = 8902)
    ),
    add event sqlclr.clr_allocation_failure
        (action (package0.callstack, sqlserver.session_id)),
    add event sqlclr.clr_virtual_alloc_failure
        (action (package0.callstack, sqlserver.session_id)),
    add event sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded,
    add event sqlserver.xml_deadlock_report,
    add event sqlos.wait_info
    (
        action (package0.callstack, sqlserver.session_id, sqlserver.sql_text)
        where 
        (duration > 15000 and 
            (   
                (wait_type > 31 -- Waits for latches and important wait resources (not locks ) that have exceeded 15 seconds. 
                    and
                    (
                        (wait_type > 47 and wait_type < 54) --PAGELATCH_NL;PAGELATCH_KP;PAGELATCH_SH;PAGELATCH_UP;PAGELATCH_EX;PAGELATCH_DT
                        or wait_type < 38 --LATCH_NL;LATCH_KP;LATCH_SH;LATCH_UP;LATCH_EX;LATCH_DT
                        or (wait_type > 63 and wait_type < 70) --PAGELATCH_DT;PAGEIOLATCH_NL;PAGEIOLATCH_KP;PAGEIOLATCH_SH;PAGEIOLATCH_UP;PAGEIOLATCH_EX;PAGEIOLATCH_DT
                        or (wait_type > 96 and wait_type < 100) --IO_COMPLETION;ASYNC_IO_COMPLETION;NETWORK_IO
                        or (wait_type = 111) --RESOURCE_SEMAPHORE
                        or (wait_type = 117) --SOS_WORKER
                        or (wait_type > 178 and wait_type < 183) --FCB_REPLICA_WRITE;FCB_REPLICA_READ;WRITELOG
                        or (wait_type = 190) --CMEMTHREAD
                        or (wait_type = 214) --TRACEWRITE
                        or (wait_type = 276) --RESOURCE_SEMAPHORE_MUTEX
                    )
                )
                or 
                (duration > 30000       -- Waits for locks that have exceeded 30 secs.
                    and wait_type < 22 -- all lock waits
                ) 
            )
        )
    ),
    add event sqlos.wait_info_external
    (
        action (package0.callstack, sqlserver.session_id, sqlserver.sql_text)
        where 
        (duration > 5000 and
            (   
                (   -- Login related preemptive waits that have exceeded 5 seconds.
                    (wait_type > 410 and wait_type < 419) --PREEMPTIVE_OS_GENERICOPS;PREEMPTIVE_OS_AUTHENTICATIONOPS;PREEMPTIVE_OS_ACCEPTSECURITYCONTEXT;PREEMPTIVE_OS_ACQUIRECREDENTIALSHANDLE;PREEMPTIVE_OS_COMPLETEAUTHTOKEN;PREEMPTIVE_OS_DECRYPTMESSAGE;PREEMPTIVE_OS_DELETESECURITYCONTEXT;PREEMPTIVE_OS_ENCRYPTMESSAGE
                    or  (wait_type > 419 and wait_type < 423) --PREEMPTIVE_OS_INITIALIZESECURITYCONTEXT;PREEMPTIVE_OS_LOGONUSER;PREEMPTIVE_OS_QUERYSECURITYCONTEXTTOKEN
                    or  (wait_type > 425 and wait_type < 431) --PREEMPTIVE_OS_AUTHZGETINFORMATIONFROMCONTEXT;PREEMPTIVE_OS_AUTHZINITIALIZECONTEXTFROMSID;PREEMPTIVE_OS_AUTHZINITIALIZERESOURCEMANAGER;PREEMPTIVE_OS_LOOKUPACCOUNTSID;PREEMPTIVE_OS_REVERTTOSELF
                    or  (wait_type > 468 and wait_type < 472) --PREEMPTIVE_OS_CRYPTACQUIRECONTEXT;PREEMPTIVE_OS_CRYPTIMPORTKEY;PREEMPTIVE_OS_DEVICEOPS
                    or  (wait_type > 474 and wait_type < 480) --PREEMPTIVE_OS_NETGROUPGETUSERS;PREEMPTIVE_OS_NETLOCALGROUPGETMEMBERS;PREEMPTIVE_OS_NETUSERGETGROUPS;PREEMPTIVE_OS_NETUSERGETLOCALGROUPS;PREEMPTIVE_OS_NETUSERMODALSGET
                    or  (wait_type > 480 and wait_type < 483) --PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICYFREE;PREEMPTIVE_OS_DOMAINSERVICESOPS
                    or wait_type = 424 --PREEMPTIVE_OS_VERIFYSIGNATURE
                )
                or 
                (duration > 45000   -- Preemptive OS waits that have exceeded 45 seconds. 
                    and 
                    (   
                        (wait_type > 430 and wait_type < 434) --PREEMPTIVE_OS_SETNAMEDSECURITYINFO;PREEMPTIVE_OS_CLUSTEROPS;PREEMPTIVE_CLUSAPI_CLUSTERRESOURCECONTROL
                        or  (wait_type > 471 and wait_type < 475) --PREEMPTIVE_OS_RSFXDEVICEOPS;PREEMPTIVE_OS_DIRSVC_NETWORKOPS;PREEMPTIVE_OS_DSGETDCNAME
                        or  (wait_type > 482 and wait_type < 485) --PREEMPTIVE_OS_DTCOPS;PREEMPTIVE_DTC_ABORT
                        or  (wait_type > 490 and wait_type < 498) --PREEMPTIVE_OS_CLOSEHANDLE;PREEMPTIVE_OS_COPYFILE;PREEMPTIVE_OS_CREATEDIRECTORY;PREEMPTIVE_OS_CREATEFILE;PREEMPTIVE_OS_DELETEFILE;PREEMPTIVE_OS_DEVICEIOCONTROL;PREEMPTIVE_OS_FINDFILE
                        or  (wait_type > 499 and wait_type < 521) --PREEMPTIVE_OS_GETCOMPRESSEDFILESIZE;PREEMPTIVE_OS_GETDISKFREESPACE;PREEMPTIVE_OS_GETFILEATTRIBUTES;PREEMPTIVE_OS_GETFILESIZE;PREEMPTIVE_OS_GETLONGPATHNAME;PREEMPTIVE_OS_GETVOLUMEPATHNAME;PREEMPTIVE_OS_GETVOLUMENAMEFORVOLUMEMOUNTPOINT;PREEMPTIVE_OS_MOVEFILE;PREEMPTIVE_OS_OPENDIRECTORY;PREEMPTIVE_OS_REMOVEDIRECTORY;PREEMPTIVE_OS_SETENDOFFILE;PREEMPTIVE_OS_SETFILEPOINTER;PREEMPTIVE_OS_SETFILEVALIDDATA;PREEMPTIVE_OS_WRITEFILE;PREEMPTIVE_OS_WRITEFILEGATHER;PREEMPTIVE_OS_LIBRARYOPS;PREEMPTIVE_OS_FREELIBRARY;PREEMPTIVE_OS_GETPROCADDRESS;PREEMPTIVE_OS_LOADLIBRARY;PREEMPTIVE_OS_MESSAGEQUEUEOPS;PREEMPTIVE_ODBCOPS
                        or  (wait_type > 532 and wait_type < 547) --PREEMPTIVE_OS_DISCONNECTNAMEDPIPE;PREEMPTIVE_OS_PROCESSOPS;PREEMPTIVE_OS_SECURITYOPS;PREEMPTIVE_OS_SERVICEOPS;PREEMPTIVE_OS_SQLCLROPS;PREEMPTIVE_OS_WINSOCKOPS;PREEMPTIVE_OS_GETADDRINFO;PREEMPTIVE_OS_WSASETLASTERROR;PREEMPTIVE_OS_FORMATMESSAGE;PREEMPTIVE_OS_REPORTEVENT;PREEMPTIVE_OS_BACKUPREAD;PREEMPTIVE_OS_WAITFORSINGLEOBJECT;PREEMPTIVE_OS_QUERYREGISTRY;PREEMPTIVE_CLOSEBACKUPMEDIA
                        or wait_type = 412 --PREEMPTIVE_OS_AUTHENTICATIONOPS
                        or wait_type = 419 --PREEMPTIVE_OS_FREECREDENTIALSHANDLE
                        or wait_type = 425 --PREEMPTIVE_OS_AUTHORIZATIONOPS
                        or wait_type = 435 --PREEMPTIVE_COM_COCREATEINSTANCE
                        or wait_type = 480 --PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY
                        or wait_type = 550 --PREEMPTIVE_VSS_CREATESNAPSHOT
                    )
                )
            )
        )
    ),
    add event sqlos.memory_broker_ring_buffer_recorded,
    add event sqlos.scheduler_monitor_deadlock_ring_buffer_recorded,
    add event sqlos.scheduler_monitor_system_health_ring_buffer_recorded,
    add event sqlos.scheduler_monitor_non_yielding_iocp_ring_buffer_recorded,
    add event sqlos.scheduler_monitor_non_yielding_rm_ring_buffer_recorded,
    add event sqlos.scheduler_monitor_stalled_dispatcher_ring_buffer_recorded,
    add event sqlos.memory_node_oom_ring_buffer_recorded
        (action (package0.callstack, sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack)),
    add event sqlserver.security_error_ring_buffer_recorded
        (set collect_call_stack = 1),
    add event sqlserver.connectivity_ring_buffer_recorded
        (set collect_call_stack = 1),
    add event sqlserver.sp_server_diagnostics_component_result
    (
        set collect_data = 1
        where
            sqlserver.is_system = 1 and
            component != 4 /* DiagnoseComponent::DCCI_EVENTS */
    )
    add target package0.event_file      -- Store events on disk (in the LOG folder of the instance)
    (
        set filename           = N'system_health.xel',
            max_file_size      = 5, /* MB */
            max_rollover_files = 4
    ),
    add target package0.ring_buffer     -- Store events in the ring buffer target
        (set max_memory = 4096, max_events_limit = 5000)
    with
    (
        MAX_DISPATCH_LATENCY = 120 SECONDS,
        startup_state = on
    )
    go
    if not exists (select * from sys.dm_xe_sessions where name = 'system_health')
        alter event session system_health on server state=start
    go

-- AlwaysOn default session: AlwaysOn_health
-- For now the session is off by default.
if exists(select * from sys.server_event_sessions where name='AlwaysOn_health')
	drop event session AlwaysOn_health on server;
go

create event session AlwaysOn_health on server 
    add event sqlserver.alwayson_ddl_executed,
    add event sqlserver.availability_group_lease_expired,
    add event sqlserver.availability_replica_automatic_failover_validation,
    add event sqlserver.availability_replica_manager_state_change,
    add event sqlserver.availability_replica_state_change,
    add event lock_redo_blocked,
    add event sqlserver.error_reported
    (
        where 
        (
            --endpoint issue:stopped
            [error_number]=(9691)  
            or [error_number]=(35204) 
        
            --endpoint issue:invalid ip address
            or [error_number]=(9693) 
            or [error_number]=(26024)
         
            --endpoint issue:encryption and handshake issue
            or [error_number]=(28047)

            --endpoint issue:port conflict
            or [error_number]=(26023)
            or [error_number]=(9692) 

            --endpoint issue:authentication failure
            or [error_number]=(28034) 
            or [error_number]=(28036) 
            or [error_number]=(28048) 
            or [error_number]=(28080) 
            or [error_number]=(28091) 

            --endpoint:listening
            or [error_number]=(26022) 

            --endpoint issue:generic message
            or [error_number]=(9642) 

            --alwayson connection timeout information
            or [error_number]=(35201) --new connection timeout
            or [error_number]=(35202) --connected
            or [error_number]=(35206) --existing connection timeout
            or [error_number]=(35207) --general connection issue message

            --alwayson listener state
            or [error_number]=(26069) --started 
            or [error_number]=(26070) --stopped

            --wsfc cluster issues
            or [error_number]>(41047) and [error_number]<(41056)

            --failover validation message
            or [error_number]=(41142) 

            --availability group resource failure
            or [error_number]=(41144) 
            
            --database replica role change	
            or [error_number]=(1480) 

            --automatic page repair event
            or [error_number]=(823) 
            or [error_number]=(824) 
            or [error_number]=(829) 
            
            --database replica suspended resumed
            or [error_number]=(35264) 
            or [error_number]=(35265)
        )
    ) 

    add target package0.event_file
    (
        set filename = N'AlwaysOn_health.xel',
            max_file_size = 5,
            max_rollover_files = 4
    )
    with 
    (
        max_memory = 4 mb,
        event_retention_mode = allow_single_event_loss,
        max_dispatch_latency = 30 seconds,
        max_event_size = 0 mb,
        memory_partition_mode = none,
        track_causality = off,
        startup_state=off
    );
go

declare @vdt varchar(99)
select  @vdt = convert(varchar,getdate(),113)
raiserror('Finishing at  %s',0,1,@vdt)
go

checkpoint
go