SQL Abfragen

Select db.name, se.login_name,
char(34) + SUBSTRING( t.text, 1, 350) + char(34) as First350Chars,
char(34) + SUBSTRING( t.text,
case when sr.statement_start_offset > 0 then
sr.statement_start_offset /2 else sr.statement_start_offset
end, case when sr.statement_end_offset > 0 then
(sr.statement_end_offset - sr.statement_start_offset)/2 else
len(t.text) end ) + char(34) as SQLAusschnitt,
sr.session_id, se.logical_reads as Session_Logical_Reads,
se.total_elapsed_time as Session_Total_Elapsed_Time,
se.reads as Session_Reads, se.writes as Session_Writes,
sr.start_time, sr.status, sr.command, sr.blocking_session_id,
sr.wait_type, sr.wait_time, sr.last_wait_type, sr.wait_resource,
sr.transaction_id, sr.cpu_time, sr.total_elapsed_time, sr.reads,
sr.writes, sr.logical_reads
from sys.dm_exec_requests sr
left join sys.databases db on db.database_id = sr.database_id
join sys.dm_exec_sessions se on se.session_id = sr.session_id
cross apply sys.dm_exec_sql_text(sr.sql_handle) t
where sr.session_id > 50
and sr.[sql_handle] is not null
and sr.session_id != @@spid
order by sr.session_id

Select * from basis.vr_LockView
where request_mode like '%X%'

Select * from basis.vr_LockView

WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
N'BROKER_EVENTHANDLER',N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP',N'BROKER_TO_FLUSH',N'BROKER_TRANSMITTER',
N'CHECKPOINT_QUEUE',N'CHKPT',N'CLR_AUTO_EVENT',N'CLR_MANUAL_EVENT',
N'CLR_SEMAPHORE',N'DBMIRROR_DBM_EVENT',N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE',N'DBMIRRORING_CMD',N'DIRTY_PAGE_POLL',
N'DISPATCHER_QUEUE_SEMAPHORE',N'EXECSYNC',N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT',N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL',N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT',N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK',N'HADR_WORK_QUEUE',N'KSOURCE_WAKEUP',
N'LAZYWRITER_SLEEP',N'LOGMGR_QUEUE',N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH',N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK',N'SLEEP_BPOOL_FLUSH',N'SLEEP_DBSTARTUP',
N'SLEEP_DCOMSTARTUP',N'SLEEP_MASTERDBREADY',N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED',N'SLEEP_MSDBSTARTUP',N'SLEEP_SYSTEMTASK',
N'SLEEP_TASK',N'SLEEP_TEMPDBSTARTUP',N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP',N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',N'SQLTRACE_WAIT_ENTRIES',
N'WAIT_FOR_RESULTS',N'WAITFOR',N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT',N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE',N'XE_DISPATCHER_JOIN',N'XE_DISPATCHER_WAIT',
N'XE_TIMER_EVENT')
)
SELECT
W1.wait_type AS WaitType,
CAST(W1.WaitS AS DECIMAL(16,2)) AS Wait_Sec,
CAST(W1.ResourceS AS DECIMAL(16,2)) AS Resource_Sec,
CAST(W1.SignalS AS DECIMAL(16,2)) AS Signal_Sec,
W1.WaitCount AS WaitCount,
CAST(W1.Percentage AS DECIMAL(5,2)) AS Percentage,
CAST((W1.WaitS / W1.WaitCount) AS DECIMAL(16,4)) AS AvgWait_Sec,
CAST((W1.ResourceS / W1.WaitCount) AS DECIMAL(16,4)) AS AvgRes_Sec,
CAST((W1.SignalS / W1.WaitCount) AS DECIMAL(16,4)) AS AvgSig_Sec
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS,
 W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM(W2.Percentage)-W1.Percentage < 95;

CREATE EVENT SESSION [databaseGrowth] ON SERVER 
ADD EVENT sqlos.exception_ring_buffer_recorded(ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.database_file_size_change(SET collect_database_name=(1) ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.databases_data_file_size_changed(ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.databases_log_file_size_changed(ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.databases_log_growth(ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.error_reported(ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username) WHERE ([error_number]<>(5701) AND [error_number]<>(5703))),
ADD EVENT sqlserver.errorlog_written(ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.module_end(SET collect_statement=(1) ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.username) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [package0].[greater_than_uint64]([duration],(500000)))),
ADD EVENT sqlserver.rpc_completed(ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.username) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [package0].[greater_than_uint64]([duration],(500000)))),
ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1) ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.username) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [package0].[greater_than_int64]([duration],(500000)))),
ADD EVENT sqlserver.sql_batch_completed( ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.username) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [package0].[greater_than_uint64]([duration],(500000)))),
ADD EVENT sqlserver.sql_statement_completed( ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.username) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [package0].[greater_than_int64]([duration],(500000))))
ADD TARGET package0.event_file(SET filename=N'D:\Data\log_path\databaseGrowth.xel',max_file_size=(200),max_rollover_files=(50))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

SELECT cp.plan_handle,
       qp.query_plan,
       cp.cacheobjtype, 
       cp.size_in_bytes, 
       cp.usecounts, 
       SQLText.text
  FROM sys.dm_exec_cached_plans AS CP
  CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText
  CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP
  where SQLText.text like '%drucksubjekt%'
  option (recompile)

DBCC freeproccache(0x0102) --plan_handle

use [datenbank];
select
  o.name as [ObjectName]
  ,uses_ansi_nulls
  ,o.type_desc as [ObjectType]
  ,convert(varchar, o.modify_date, 13) as [ModifyDate]
  ,s.name as [SchemaName]
from
  sys.sql_modules m
  join sys.objects o
    on m.object_id = o.object_id
  join sys.schemas s
    on s.schema_id = o.schema_id
  where uses_ansi_nulls = 1
    and is_ms_shipped = 0
    and s.name not in ('datamart_evu', 'datamart')
    and o.name not like 'RCV[_]%'
  order by
    s.name asc,
    o.name asc
    --,o.modify_date desc