[MSSQL]数据库优化-阿里售后给出的建议

数据库查询优化

https://yq.aliyun.com/articles/260826?spm=a2c4e.11163080.searchblog.65.66112ec1Fee1QE&tdsourcetag=s_pctim_aiomsg

 

售后：需要注意的是这里要选择【master】数据库，否则会报错

售后：查询慢查询语句

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] AS [完整语法],
  DB_NAME(qt.[dbid]) AS dbname,
  OBJECT_NAME(qt.objectid, qt.[dbid]) AS 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
ORDER BY total_worker_time DESC

 

 

售后：如慢查询语句过多，建议您对sql语句做下索引和优化

SELECT TOP 20
  (qs.total_logical_reads + qs.total_logical_writes) AS [Total IO],
  (qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count AS [Average IO],
  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 AS qs
  CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
  CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY [Average IO] DESC