Sql Server查询优化

如何找出你性能最差的SQL Server查询 SQL Server 2014里的性能提升 sql server查询优化 SQL SERVER 的用户数，连接，连接池 license

--查看sqlserver数据库版本
SELECT @@VERSION
--查询某个数据库的连接数
select count(*) from Master.dbo.SysProcesses where dbid=db_id()
--前10名其他等待类型
SELECT TOP 10 * from sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
SELECT * FROM sys.dm_os_wait_stats 
WHERE wait_type like 'PAGELATCH%'
    OR wait_type like 'LAZYWRITER_SLEEP%'
--CPU的压力
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
    WHERE scheduler_id < 255
--表现最差的前10名使用查询
SELECT TOP 10 ProcedureName = t.text,
    ExecutionCount = s.execution_count,
    AvgExecutionTime = isnull ( s.total_elapsed_time / s.execution_count, 0 ),
    AvgWorkerTime = s.total_worker_time / s.execution_count,
    TotalWorkerTime = s.total_worker_time,
    MaxLogicalReads = s.max_logical_reads,
    MaxPhysicalReads = s.max_physical_reads,
    MaxLogicalWrites = s.max_logical_writes,
    CreationDateTime = s.creation_time,
    CallsPerSecond = isnull ( s.execution_count / datediff ( second , s.creation_time, getdate ()), 0 )
FROM sys.dm_exec_query_stats s
    CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t 
ORDER BY s.max_physical_reads DESC
SELECT SUM(signal_wait_time_ms) AS total_signal_wait_time_ms总信号等待时间 ,
  SUM(wait_time_ms - signal_wait_time_ms) AS resource_wait_time_ms资源的等待时间,
  SUM(signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [signal_wait_percent信号等待％],
  SUM(wait_time_ms - signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [resource_wait_percent资源等待％]
FROM sys.dm_os_wait_stats
--一个信号等待时间过多对资源的等待时间那么你的CPU是目前的一个瓶颈。
--查看进程所执行的SQL语句
if (select COUNT(*) from master.dbo.sysprocesses) > 500
begin
    select text,CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) from master.sys.sysprocesses a
end
select text,a.* from master.sys.sysprocesses a
    CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
where a.spid = '51'
dbcc inputbuffer(53)
with tb
as
(
    select blocking_session_id,
    session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requests a
    CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
),
tb1 as
(
    select a.,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage8 as 'memory_usage(KB)',
    total_scheduled_time,reads,writes,logical_reads
    from tb a inner join master.sys.dm_exec_sessions b
    on a.session_id=b.session_id
)
select a.*,connect_time,client_tcp_port,client_net_address from tb1 a inner join master.sys.dm_exec_connections b on a.session_id=b.session_id
--当前进程数
select * from master.dbo.sysprocesses
order by cpu desc
--查看当前活动的进程数
sp_who active
--查询是否由于连接没有释放引起CPU过高
select * from master.dbo.sysprocesses
where spid> 50
    and waittype = 0x0000
    and waittime = 0
    and status = 'sleeping '
    and last_batch < dateadd(minute, -10, getdate())
    and login_time < dateadd(minute, -10, getdate())
--强行释放空连接
select 'kill ' + rtrim(spid) from master.dbo.sysprocesses
where spid> 50
    and waittype = 0x0000
    and waittime = 0
    and status = 'sleeping '
    and last_batch < dateadd(minute, -60, getdate())
    and login_time < dateadd(minute, -60, getdate())
--查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)
select spid,cmd,cpu,physical_io,memusage,
    (select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc
--查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局
SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text]
FROM sys.dm_exec_cached_plans p 
    OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts,p.size_in_bytes desc
SELECT top 25 qt.text,qs.plan_generation_num,qs.execution_count,dbid,objectid
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt
WHERE plan_generation_num >1
ORDER BY qs.plan_generation_num
SELECT top 50 qt.text AS SQL_text ,
    SUM(qs.total_worker_time) AS total_cpu_time,
    SUM(qs.execution_count) AS total_execution_count,
    SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time,
    COUNT(*) AS number_of_statements
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY qt.text
ORDER BY total_cpu_time DESC --统计总的CPU时间
--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间
-- 计算可运行状态下的工作进程数量
SELECT COUNT(*) as workers_waiting_for_cpu,s.scheduler_id
FROM sys.dm_os_workers AS o
    INNER JOIN sys.dm_os_schedulers AS s 
  ON o.scheduler_address=s.scheduler_address AND s.scheduler_id<255
WHERE o.state='RUNNABLE'
GROUP BY s.scheduler_id
--表空间大小查询
create table #tb(表名 sysname,记录数 int,保留空间 varchar(100),使用空间 varchar(100),索引使用空间 varchar(100),未用空间 varchar(100))
insert into #tb exec sp_MSForEachTable 'EXEC sp_spaceused ''?'''
select * from #tb
go
SELECT
    表名,
    记录数,
    cast(ltrim(rtrim(replace(保留空间,'KB',''))) as int)/1024 保留空间MB,
    cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024 使用空间MB,
    cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024/1024.00 使用空间GB,
    cast(ltrim(rtrim(replace(索引使用空间,'KB',''))) as int)/1024 索引使用空间MB,
    cast(ltrim(rtrim(replace(未用空间,'KB',''))) as int)/1024 未用空间MB
FROM #tb
WHERE cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024 > 0
--order by 记录数 desc
ORDER BY 使用空间MB DESC
DROP TABLE #tb
--查询是否由于连接没有释放引起CPU过高
select * from master.dbo.sysprocesses
where spid> 50
    and waittype = 0x0000
    and waittime = 0
    and status = 'sleeping '
    and last_batch < dateadd(minute, -10, getdate())
    and login_time < dateadd(minute, -10, getdate())
--强行释放空连接
select 'kill ' + rtrim(spid) from master.dbo.sysprocesses
where spid> 50
    and waittype = 0x0000
    and waittime = 0
    and status = 'sleeping '
    and last_batch < dateadd(minute, -60, getdate())
    and login_time < dateadd(minute, -60, getdate())
----查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)
select spid,cmd,cpu,physical_io,memusage,
    (select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses 
order by cpu desc,physical_io desc
----查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局
SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text]
FROM sys.dm_exec_cached_plans p 
    OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts,p.size_in_bytes desc
SELECT top 25 qt.text,qs.plan_generation_num,qs.execution_count,dbid,objectid
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt
WHERE plan_generation_num >1
ORDER BY qs.plan_generation_num
SELECT top 50 qt.text AS SQL_text,
    SUM(qs.total_worker_time) AS total_cpu_time,
    SUM(qs.execution_count) AS total_execution_count,
    SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time,
    COUNT(*) AS number_of_statements
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY qt.text
ORDER BY total_cpu_time DESC --统计总的CPU时间
--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间
-- 计算可运行状态下的工作进程数量
SELECT COUNT(*) as workers_waiting_for_cpu,s.scheduler_id
FROM sys.dm_os_workers AS o
    INNER JOIN sys.dm_os_schedulers AS s ON o.scheduler_address=s.scheduler_address AND s.scheduler_id<255
WHERE o.state='RUNNABLE'
GROUP BY s.scheduler_id
SELECT creation_time N'语句编译时间'
    , last_execution_time N'上次执行时间'
    , total_physical_reads N'物理读取总次数'
    , total_logical_reads/execution_count N'每次逻辑读次数'
    , total_logical_reads N'逻辑读取总次数'
    , total_logical_writes N'逻辑写入总次数'
    , execution_count N'执行次数'
    , total_worker_time/1000 N'所用的CPU总时间ms'
    , total_elapsed_time/1000 N'总花费时间ms'
    , (total_elapsed_time / execution_count)/1000 N'平均时间ms'
    , SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) N'执行语句'
FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) not like '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC

--查看当前连接的在线用户数
select loginame,count(0) from sysprocesses 
group by loginame
order by count(0) desc
select nt_username,count(0) from sysprocesses 
group by nt_username
order by count(0) desc
--如果某个SQL Server用户名sa连接比较多,查看它来自的主机名:
select hostname,count(0) from sysprocesses where loginame='sa'
group by hostname
order by count(0) desc
--如果某个SQL Server用户名sa连接比较多,查看它最后一次操作的时间范围分组
select convert(varchar,last_batch,111),count(0) from sysprocesses where loginame='sa'
group by convert(varchar,last_batch,111)
order by count(0) desc
--如果从主机(DESKTOP-L8B71S9)来的连接比较多,可以查看它的进程详细情况
select * from sysprocesses where hostname='DESKTOP-L8B71S9'
--如果www机器主要提供网页服务,可能是asp程序处理连接时出了问题, 生成杀这些进程的SQL语句:
select 'kill '+convert(varchar,spid) from sysprocesses where hostname='DESKTOP-L8B71S9'

SELECT  DB_ID(DB.dbid) '数据库名'
      , OBJECT_ID(db.objectid) '对象'
      , QS.creation_time '编译计划的时间'
      , QS.last_execution_time '上次执行计划的时间'
      , QS.execution_count '执行的次数'
      , QS.total_elapsed_time / 1000 '占用的总时间（秒）'
      , QS.total_physical_reads '物理读取总次数'
      , QS.total_worker_time / 1000 'CPU 时间总量（秒）'
      , QS.total_logical_writes '逻辑写入总次数'
      , QS.total_logical_reads N'逻辑读取总次数'
      , QS.total_elapsed_time / 1000 N'总花费时间（秒）'
      , SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,
                  ( ( CASE statement_end_offset
                        WHEN -1 THEN DATALENGTH(st.text)
                        ELSE QS.statement_end_offset
                      END - QS.statement_start_offset ) / 2 ) + 1) AS '执行语句'
FROM    sys.dm_exec_query_stats AS QS CROSS APPLY 
        sys.dm_exec_sql_text(QS.sql_handle) AS ST INNER JOIN
        ( SELECT    *
          FROM      sys.dm_exec_cached_plans cp CROSS APPLY
                    sys.dm_exec_query_plan(cp.plan_handle)
        ) DB
            ON QS.plan_handle = DB.plan_handle
where   SUBSTRING(st.text, ( qs.statement_start_offset / 2 ) + 1,
                  ( ( CASE statement_end_offset
                        WHEN -1 THEN DATALENGTH(st.text)
                        ELSE qs.statement_end_offset
                      END - qs.statement_start_offset ) / 2 ) + 1) not like '%fetch%'
                      ORDER BY QS.total_elapsed_time / 1000 DESC

SELECT  spid,
         blocked,
         DB_NAME(sp.dbid) AS DBName,
         program_name as [应用程序名],
         waitresource,
         lastwaittype,
         sp.loginame as [登录名],
         sp.hostname as [客户端名称],
         a.[Text] AS [命令文本] --,
         --SUBSTRING(A.text, sp.stmt_start , 2) 
FROM     sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A where a.text is not null 
SELECT   spid,
         blocked,
         DB_NAME(sp.dbid) AS DBName,
         program_name,
         waitresource,
         lastwaittype,
         sp.loginame,
         sp.hostname,
         a.[Text] AS [TextData],
         SUBSTRING(A.text, sp.stmt_start / 2, 
         (CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text) ELSE sp.stmt_end 
         END - sp.stmt_start) / 2) AS [current_cmd]
FROM     sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A
WHERE    spid > 50
ORDER BY blocked DESC, DB_NAME(sp.dbid) ASC, a.[text];

--看一下各项指标是否正常，是否有阻塞，正常情况下搜索结果应该为空
--会话中有多少个worker在等待
SELECT TOP 10
 [session_id],
 [request_id],
 [start_time] AS '开始时间',
 [status] AS '状态',
 [command] AS '命令',
 dest.[text] AS 'sql语句',  
 DB_NAME([database_id]) AS '数据库名',
 [blocking_session_id] AS '正在阻塞其他会话的会话ID',
 der.[wait_type] AS '等待资源类型',
 [wait_time] AS '等待时间',
 [wait_resource] AS '等待的资源',
 [dows].[waiting_tasks_count] AS '当前正在进行等待的任务数',
 [reads] AS '物理读次数',
 [writes] AS '写次数',
 [logical_reads] AS '逻辑读次数',
 [row_count] AS '返回结果行数'
 FROM sys.[dm_exec_requests] AS der  
 INNER JOIN [sys].[dm_os_wait_stats] AS dows  
 ON der.[wait_type]=[dows].[wait_type]
 CROSS APPLY  
 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest  
 WHERE [session_id]>50  
 ORDER BY [cpu_time] DESC

--检查锁表情况语句
--查询被锁表
select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName    
from   sys.dm_tran_locks where resource_type='OBJECT'