How to see queries run on a SQL Server database? (ดู log ของ Query ที่ส่งจาก AOS ไปที่ SQL server)

Some time some record had deleted from table and we don't know why so we can search it from queries log on SQL server by use this

บางครั้งในการ track data บาง record อาจจะถูกลบออกไปทำให้เราไม่สามารถรู้ได้ว่าเกิดอะไรขึ้น เรายังสามารถหาได้จาก SQL log

SELECT  dest.text, *
FROM    sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE   deqs.last_execution_time > '9/9/2016 9:00'
        AND dest.text LIKE 'WITH%' --input text what you need to search 

For FETCH API_CURSOR00000000000XXXXX

SELECT creation_time,
cursor_id,
c.session_id,
c.properties,
c.creation_time,
c.is_open,
SUBSTRING(st.TEXT, ( c.statement_start_offset / 2) + 1, (
( CASE c.statement_end_offset
WHEN -1 THEN DATALENGTH(st.TEXT)
ELSE c.statement_end_offset
END - c.statement_start_offset) / 2) + 1) AS statement_text
FROM   sys.Dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
CROSS apply sys.Dm_exec_sql_text(c.sql_handle) AS st
GO

 SELECT 

CASE WHEN qt.dbid IS NULL THEN 'Ad Hoc Query'

                 WHEN qt.dbid = 32767 THEN 'Resource Database'

  ELSE
DB_NAME(qt.dbid)

END AS [Database],

SUBSTRING(qt.TEXT , (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.TEXT)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1) as query_text,

qs.creation_time,

qs.last_execution_time,

qs.execution_count,

qs.last_logical_reads,

qs.last_elapsed_time/1000 last_elapsed_time_in_ms,

qs.total_elapsed_time/1000000 total_elapsed_time_in_S,

qs.total_logical_reads, 

qs.total_logical_writes, qs.last_logical_writes,

qs.total_physical_reads, qs.last_physical_reads,

qs.total_worker_time, -- in microSeconds

qs.last_worker_time, -- in microSeconds

qs.last_elapsed_time/1000000 last_elapsed_time_in_S,

CURRENT_TIMESTAMP as QueryRunTime ,          

ses.session_id SessionID -- new

, qt.objectid as ObjectID

,qs.sql_handle as SQLHandle

,qs.plan_handle as PlanHandle

,qt.text as QueryText

,qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

LEFT JOIN sys.dm_exec_requests req ON qs.sql_handle =
req.sql_handle

LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id =
req.session_id

where 1=1

And qs.last_execution_time   >= '2017-10-31 08:46:18.283' 

and qs.last_logical_reads       >=1

and qs.execution_count >= 1

AND 1=1 AND 

(

                2=2

                and
(qt.dbid IS NULL)

                and
(qt.text not like 'Fetch %')

                and
(qt.text not like '%SQLOptimisingTaskDB%')

                and
(qt.text not like '%sys.%')

                and
(qt.text not like '% Batchhistory%')

                and
(qt.text  like '%VCSALESSTAGINGLINE%') --
target table This is the key
table to use in the DMV, to identify the impact of the AX Query

)
order by qs.last_execution_time desc