use MES_WORKBECH_TD
----根据菜单名找对应的winform窗体
select * from SYS_BASE_RESOURCES a
where a.NAME like '装配转码%'
--系统日志记录档
select * from sys_debug_log (nolock) a
where a.op_time > 1000
---整体系统慢时常用到的检查sql
use MES_WORKBECH_TD_LOG
------------------------------查cpu占用-----------------------------------------------------------
SELECT TOP 20
total_worker_time/1000 AS [总消耗CPU 时间(ms)],
execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],
max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
dbname=db_name(qt.dbid),
object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs
WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
and last_execution_time >'2020-06-28'
ORDER BY total_worker_time DESC,last_execution_time desc
----------------------------查磁盘IO--------------------------------------------
select top 50
(total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_phys_reads,
Execution_count,
statement_start_offset as stmt_start_offset, statement_end_offset as stmt_end_offset,
substring(sql_text.text, (statement_start_offset/2),
case
when (statement_end_offset -statement_start_offset)/2 <=0 then 64000
else (statement_end_offset -statement_start_offset)/2 end) as exec_statement, sql_text.text,plan_text.*
from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
cross apply sys.dm_exec_query_plan(plan_handle) as plan_text
order by
(total_logical_reads + total_logical_writes) /Execution_count Desc
------------------排查历史慢查询：
SELECT TOP 20
[Total IO] = (qs.total_logical_reads + qs.total_logical_writes)
, [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) /
qs.execution_count
, qs.execution_count
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY [Average IO] DESC
------------------------查询当前正在执行的慢查询：
SELECT TOP 1 ST.transaction_id AS TransactionID ,
st.session_id ,
DB_NAME(DT.database_id) AS DatabaseName ,
ses.host_name ,
ses.login_name ,
ses.status,
AT.transaction_begin_time AS TransactionStartTime ,
s.text ,
c.connect_time ,
DATEDIFF(second, AT.transaction_begin_time, GETDATE()) "exec_time(s)" ,
DATEDIFF(minute, AT.transaction_begin_time, GETDATE()) AS Tran_run_time ,
CASE AT.transaction_type
WHEN 1 THEN 'Read/Write Transaction'
WHEN 2 THEN 'Read-Only Transaction'
WHEN 3 THEN 'System Transaction'
WHEN 4 THEN 'Distributed Transaction'
END AS TransactionType ,
CASE AT.transaction_state
WHEN 0 THEN 'Transaction Not Initialized'
WHEN 1 THEN 'Transaction Initialized & Not Started'
WHEN 2 THEN 'Active Transaction'
WHEN 3 THEN 'Transaction Ended'
WHEN 4 THEN 'Distributed Transaction Initiated Commit Process'
WHEN 5 THEN 'Transaction in Prepared State & Waiting Resolution'
WHEN 6 THEN 'Transaction Committed'
WHEN 7 THEN 'Transaction Rolling Back'
WHEN 8 THEN 'Transaction Rolled Back'
END AS TransactionState
FROM sys.dm_tran_session_transactions AS ST
INNER JOIN sys.dm_tran_active_transactions AS AT ON ST.transaction_id = AT.transaction_id
INNER JOIN sys.dm_tran_database_transactions AS DT ON ST.transaction_id = DT.transaction_id
LEFT JOIN sys.dm_exec_connections AS C ON st.session_id = c.session_id
LEFT JOIN sys.dm_exec_sessions AS ses ON c.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_Handle) s
WHERE DATEDIFF(second, AT.transaction_begin_time, GETDATE()) > 2
------------------其它--------------------------------------------------------
select * from dbo.[PRODUCT_WORKORDER_PROCESS_PU] a
where a.id = '764D6A6B-EEC3-4292-9CE4-87717AFE3009'
select COUNT(1)
from [dbo].[PRODUCT_WORKORDER_PROCESS_PU] A
where a.LinePlanId = ''
and a.TrackOutBarCode= ''
and a.ProcessCode = ''
and a.State = 1
and a.ClientCode= ''