分类导航
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLOceanBase

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

MsSQL   发布时间：2022-05-16  发布网站：大佬教程  code.js-code.com
大佬教程收集整理的这篇文章主要介绍了【SqlServer】 分享 几个 优化 Sql 的 语句，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

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( @H_659_28@mAX ), s2.text))
                                                * 2 )
ELSE  statement_end_offset
                                    END  ) - statement_start_offset ) / 2 + 1)
)  [语句] ,
execution_count  [执行次数] ,
last_execution_time  [上次开始执行计划的时间] ,
@R_203_10586@l_worker_time  [自编译以来执行所用的 cpu 时间总量（微秒）] ,
last_worker_time  [上次执行计划所用的 cpu 时间（微秒）] ,
@H_503_109@min_worker_time  [单次执行期间曾占用的最小 cpu 时间（微秒）] ,
@H_795_113@max_worker_time  [单次执行期间曾占用的最大 cpu 时间（微秒）] ,
@R_203_10586@l_logical_reads  [总逻辑读] ,
last_logical_reads  [上次逻辑读] ,
@H_108_125@min_logical_reads  [最少逻辑读] ,
@H_440_129@max_logical_reads  [最大逻辑读] ,
@R_203_10586@l_logical_writes  [总逻辑写] ,
last_logical_writes  [上次逻辑写] ,
@H_242_141@min_logical_writes  [最小逻辑写] ,
@H_470_145@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.@R_203_10586@l_worker_time  @R_203_10586@lWorkerTime 
        ,EQs.@R_203_10586@l_logical_reads + EQs.@R_203_10586@l_logical_writes  @R_203_10586@lLogicalIO 
number7 index6 alt2" style='list-style:none; font-family:Consolas,EQs.execution_count  As  ExeCnt 
number8 index7 alt1" style='list-style:none; font-family:Consolas,EQs.last_execution_time  Lastusage 
number9 index8 alt2" style='list-style:none; font-family:Consolas,EQs.@R_203_10586@l_worker_time / EQs.execution_count  as  AvgcpuTimeMiS 
number10 index9 alt1" style='list-style:none; font-family:Consolas,(EQs.@R_203_10586@l_logical_reads + EQs.@R_203_10586@l_logical_writes) / EQs.execution_count  
         AvgLogicalIO 
number12 index11 alt1" style='list-style:none; font-family:Consolas,DB. name  Databasename 
number13 index12 alt2" style='list-style:none; font-family:Consolas, (EST.text 
                  EQs.statement_end_offset = -1  
                         LEN( convert (nvarchar( @H_659_28@max EQs.statement_end_offset  END  
@H_262_270@                   - 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( @H_733_54@mONTH AvgLogicalIo  DESC 
number30 index29 alt1" style='list-style:none; font-family:Consolas,AvgcpuTimeMiS  -- Current processes and their sql statements 
PRO.loginame  LoginName 
Databasename 
number4 index3 alt1" style='list-style:none; font-family:Consolas,PRO.[status]  Processstatus 
number5 index4 alt2" style='list-style:none; font-family:Consolas,PRO.cmd  Command 
number6 index5 alt1" style='list-style:none; font-family:Consolas,PRO.lasT_Batch  LastBatch 
number7 index6 alt2" style='list-style:none; font-family:Consolas,PRO.cpu  cpu 
number8 index7 alt1" style='list-style:none; font-family:Consolas,PRO.physical_io  PhysicalIo 
number9 index8 alt2" style='list-style:none; font-family:Consolas,SEs.row_count  [RowCount] 
number10 index9 alt1" style='list-style:none; font-family:Consolas,STm.[text]  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、存储过程执行情况：

SELECT  ISNULL (DBs. name '' Databasename 

number3 index2 alt2" style='list-style:none; font-family:Consolas,object_name(EPs.object_id, EPs.database_id)  ObjectName 
number4 index3 alt1" style='list-style:none; font-family:Consolas,EPs.cached_time  CachedTime 
number5 index4 alt2" style='list-style:none; font-family:Consolas,EPs.last_elapsed_time  Lastelapsedtime 
number6 index5 alt1" style='list-style:none; font-family:Consolas,EPs.execution_count  ExecutionCount 
number7 index6 alt2" style='list-style:none; font-family:Consolas,EPs.@R_203_10586@l_worker_time / EPs.execution_count  AvgWorkerTime 
number8 index7 alt1" style='list-style:none; font-family:Consolas,EPs.@R_203_10586@l_elapsed_time / EPs.execution_count  Avgelapsedtime 
number9 index8 alt2" style='list-style:none; font-family:Consolas,(EPs.@R_203_10586@l_logical_reads + EPs.@R_203_10586@l_logical_writes) 
         / 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_@R_203_10586@l_elapsed_time ,
ss.sum_@R_203_10586@l_worker_time ,
ss.sum_@R_203_10586@l_logical_reads ,
ss.sum_@R_203_10586@l_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.@R_203_10586@l_elapsed_timE) SUM_@R_203_10586@l_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.@R_203_10586@l_worker_timE) SUM_@R_203_10586@l_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.@R_203_10586@l_logical_reads) SUM_@R_203_10586@l_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.@R_203_10586@l_logical_writes) SUM_@R_203_10586@l_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_@R_203_10586@l_logical_reads  DESC  

大佬总结

以上是大佬教程为你收集整理的【SqlServer】 分享 几个 优化 Sql 的 语句全部内容，希望文章能够帮你解决【SqlServer】 分享 几个 优化 Sql 的 语句所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。
标签：
猜你在找的MsSQL相关文章
其他相关热搜词更多
phpJavaPython程序员load中如何string使用参数jquery开发安装listlinuxiosandroid工具javascriptcap