日常工作中常用的sql 脚本之一

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= ''