SQL Spark

  USE master Declare @strFind varchar(max) Set @strFind = '%ma_cancadeal%' select distinct object_name(OBJECT_ID)  from sys.sql_modules  where definition like @strFind  order by object_name(OBJECT_ID)  declare @sql varchar(max) SELECT @sql = 'USE ? print ''?''; select ''?'' as DB_Name, object_Name(object_id),* from sys.sql_modules WHERE definition LIKE ''%'+@strFind+'%'''  EXEC sp_MSforeachdb @sql 

  -- Save File as .xdl and then open in SSMS  SELECT DeadloackDetail = CONVERT(xml, event_data).query('/event/data/value/child::*'), CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime') as Execution_Time FROM sys.fn_xe_file_target_read_file('F:\SQL13BIN\MSSQL13.MSSQLSERVER\MSSQL\Log\system_health*.xel', null, null, null) WHERE object_name like 'xml_deadlock_report'

  1. Register SPN for SQL service account SetSPN -S MSSQLSvc/win2012r2-e424b.dynamic.sit.cloud.td.com:3341 D2-TDBFG\TCRBI084SQLS 2. Enable Delegate in Active Directory, go to SQL server account Properties, on "Delegation" tab, select "Trust this user for delegation to any service (Kerberos only)" 3. Verify SPN and delegate Get-ADUser -Identity TCRDM463SQLSS -Properties * After Enabled 4. confirm Authentication use K erberos select ServerName =@@ServerName, Servicename, service_account, status_desc from sys.dm_server_services select @@ServerName, auth_scheme from sys.dm_exec_connections where session_id =@@SPID 5. Create Linked Server use AD authentication, access based on account use linked server USE [master] GO /****** Object:  LinkedServer [DEV3]    Script Date: 11/9/2021 12:12:02 PM ******/ IF NOT EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'DEV3') BEGIN EXEC master.dbo.sp_addlinkedserver @server = N'DEV3...

  select * from msdb.dbo.sysmail_profile p join msdb.dbo.sysmail_profileaccount pa on p.profile_id = pa.profile_id   join msdb.dbo.sysmail_account a on pa.account_id = a.account_id       join msdb.dbo.sysmail_server s on a.account_id = s.account_id

  DECLARE @tracefile VARCHAR(500)  -- Get path of default trace file  SELECT @tracefile = CAST(value AS VARCHAR(500))  FROM ::fn_trace_getinfo(DEFAULT)  WHERE traceid = 1  AND property = 2  -- Get security changes from the default trace  SELECT *  FROM ::fn_trace_gettable(@tracefile, DEFAULT) trcdata -- DEFAULT means all trace files will be read  INNER JOIN sys.trace_events evt ON trcdata.EventClass = evt.trace_event_id  WHERE trcdata.EventClass IN (102, 103, 104, 105, 106, 108, 109, 110, 111)  and trcdata.DatabaseName = 'CRDM'  ORDER BY trcdata.StartTime 

  if Select SQL take lots of time, try below options Inner HASH JOIN Query hint - Option (recompile) – running more than 20 minutes, cancelled query Cross Apply/Outer Apply (Optimize the query to use cross apply/outer apply instead of subquery)  – completed in 4 min Use Temp table with index Create update statement to replace sub query Query hint - FORCE_LEGACY_CARDINALITY_ESTIMATION Use this hint in select statement option (use hint('FORCE_LEGACY_CARDINALITY_ESTIMATION'))  SELECT * FROM Schema.Table WHERE Condition = 'Value' OPTION (USE HINT ('FORCE_DEFAULT_CARDINALITY_ESTIMATION'));

select max(local_tcp_port) from sys.dm_exec_connections where net_transport ='TCP' USE master GO xp_readerrorlog 0, 1, N'Server is listening on', 'any', NULL, NULL, N'asc'  GO