【SqlServer】分享几个优化 Sql 的语句

发布时间：2020-12-12 13:45:14 所属栏目：MsSql教程来源：网络整理

导读：1、查找目前SQL?Server所执行的SQL语法，并展示资源情况：? SELECT ?? s2.dbid?, ????????? DB_NAME(s2.dbid)? AS ? [数据库名]?, ????????? --s1.sql_handle?, (? SELECT ? TOP ? 1 ????????????????????? SUBSTRING (s2.text,?statement_start_offset?/?2?

1、查找目前SQL?Server所执行的SQL语法，并展示资源情况：?

SELECT?? s2.dbid?, ????????? DB_NAME(s2.dbid)? AS? [数据库名]?, ????????? --s1.sql_handle?, (? SELECT? TOP? 1 ????????????????????? SUBSTRING (s2.text,?statement_start_offset?/?2?+?1, ??????????????????????????????? (?(? CASE? WHEN? statement_end_offset?=?-1 ???????????????????????????????????????? THEN? (?LEN( CONVERT (NVARCHAR( MAX ),?s2.text)) ??????????????????????????????????????????????? *?2?) ELSE? statement_end_offset ??????????????????????????????????? END? )?-?statement_start_offset?)?/?2?+?1) )? [语句]?, execution_count? [执行次数]?, last_execution_time? [上次开始执行计划的时间]?, total_worker_time? [自编译以来执行所用的?CPU?时间总量（微秒）]?, last_worker_time? [上次执行计划所用的?CPU?时间（微秒）]?, min_worker_time? [单次执行期间曾占用的最小?CPU?时间（微秒）]?, max_worker_time? [单次执行期间曾占用的最大?CPU?时间（微秒）]?, total_logical_reads? [总逻辑读]?, last_logical_reads? [上次逻辑读]?, min_logical_reads? [最少逻辑读]?, max_logical_reads? [最大逻辑读]?, total_logical_writes? [总逻辑写]?, last_logical_writes? [上次逻辑写]?, min_logical_writes? [最小逻辑写]?, max_logical_writes? [最大逻辑写] ? FROM???? sys.dm_exec_query_stats? s1 ????????? CROSS? APPLY?sys.dm_exec_sql_text(sql_handle)? s2 WHERE??? s2.objectid? IS? NULL ORDER? BY? DESC

2、展示耗时查询：

--?List?expensive?queries? DECLARE? @MinExecutions? int ;? SET? @MinExecutions?=?5? ??? SELECT? EQS.total_worker_time? TotalWorkerTime? ??????? ,EQS.total_logical_reads?+?EQS.total_logical_writes? TotalLogicalIO? As? ExeCnt? LastUsage? as? AvgCPUTimeMiS? ???????? AvgLogicalIO? name? DatabaseName? (EST.text? ????????????????? EQS.statement_end_offset?=?-1?? ???????????????????????? LEN( convert (nvarchar( max END?? ?????????????????? -?EQS.statement_start_offset)?/?2? SqlStatement? ??????? --?Optional?with?Query?plan;?remove?comment?to?show,?but?then?the?query?takes?!!much?longer?time!!? --,EQP.[query_plan]?AS?[QueryPlan]? FROM? EQS? ?????? APPLY?sys.dm_exec_sql_text(EQS.sql_handle)? EST? APPLY?sys.dm_exec_query_plan(EQS.plan_handle)? EQP? ?????? LEFT? JOIN? sys.databases? DB? ?????????? ON? EST.dbid?=?DB.database_id?????? WHERE? EQS.execution_count?>?@MinExecutions? ??????? AND? EQS.last_execution_time?>?DATEDIFF( MONTH DESC? LoginName? DatabaseName? ProcessStatus? Command? LastBatch? Cpu? PhysicalIo? [RowCount]? SQLStatement? sys.sysprocesses? PRO? ?????? INNER? DB? PRO.dbid?=?DB.database_id? sys.dm_exec_sessions? SES? ????????? PRO.spid?=?SES.session_id? APPLY?sys.dm_exec_sql_text(PRO.sql_handle)? STM?????? PRO.spid?>=?50?? --?Exclude?system?processes? PRO.physical_io? DESC? DESC ;

4、存储过程执行情况：

ISNULL (DBS. name '' DatabaseName? ObjectName? CachedTime? LastElapsedTime? ExecutionCount? AvgWorkerTime? AvgElapsedTime? ???????? /?EPS.execution_count? AvgLogicalIO? sys.dm_exec_procedure_stats? EPS? DBS? EPS.database_id?=?DBS.database_id? AvgWorkerTime? /* ? 开销较大的查询 */ ss.SUM_execution_count?, t.text?,monospace!important; font-size:1em!important; padding:0px!important; color:black!important; background:none!important; margin:0px!important; border:0px!important; outline:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important'>ss.SUM_total_elapsed_time?, ss.sum_total_worker_time?, ss.sum_total_logical_reads?, ss.sum_total_logical_writes SELECT???? S.plan_handle?, SUM (s.execution_count)?SUM_Execution_count?,monospace!important; font-size:1em!important; padding:0px!important; color:black!important; background:none!important; margin:0px!important; border:0px!important; outline:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important'>(s.total_elapsed_time)?SUM_total_elapsed_time?,monospace!important; font-size:1em!important; padding:0px!important; color:black!important; background:none!important; margin:0px!important; border:0px!important; outline:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important'>(s.total_worker_time)?SUM_total_worker_time?,monospace!important; font-size:1em!important; padding:0px!important; color:black!important; background:none!important; margin:0px!important; border:0px!important; outline:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important'>(s.total_logical_reads)?SUM_total_logical_reads?,monospace!important; font-size:1em!important; padding:0px!important; color:black!important; background:none!important; margin:0px!important; border:0px!important; outline:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important'>(s.total_logical_writes)?SUM_total_logical_writes ??????????? FROM?????? sys.dm_exec_query_stats?s GROUP? BY?? S.plan_handle ss APPLY?sys.dm_exec_sql_text(ss.plan_handle)?t sum_total_logical_reads? DESC??

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

【SqlServer】 分享 几个 优化 Sql 的 语句

【SqlServer】分享几个优化 Sql 的语句