首页 > 代码库 > SQL 常用语句

SQL 常用语句

2024-07-06 05:39:04   235人阅读

–-表现最差的前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
 
--查询显示 CPU 平均占用率最高的前 50 个 SQL 语句。
SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESC
 
--查看等待sql
select
s.session_id, r.blocking_session_id,
s.host_name, s.login_name,
databasename=db_name(r.database_id),r.command, r.status as sqlexecstatus,
current_execute_sql = substring(t.text,
r.statement_start_offset / 2 + 1,
case
when statement_end_offset = -1 then len(t.text)
else (r.statement_end_offset - statement_start_offset) / 2+1
end),
s.program_name,
s.status,
s.cpu_time, memory_usage_kb = s.memory_usage * 8, s.reads, s.writes,
s.transaction_isolation_level,
c.connect_time, c.last_read, c.last_write,
c.net_transport, c.client_net_address, c.client_tcp_port, c.local_tcp_port,
r.start_time,
r.wait_time, r.wait_type, r.last_wait_type, r.wait_resource,
r.open_transaction_count, r.transaction_id
from sys.dm_exec_sessions s
left join sys.dm_exec_connections c
on s.session_id = c.session_id
left join sys.dm_exec_requests r
on s.session_id = r.session_id
and c.connection_id = r.connection_id
outer apply sys.dm_exec_sql_text(r.sql_handle) t
where 1=1
and s.is_user_process = 1
and command is not null
 
--查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)
select * from (
select spid
,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses
) t where t.sql_text is not null  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时间
 
 
 
--TOP 50 sql
select *,t1.text from (
SELECT TOP 50
SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
COUNT(*) AS number_of_statements,
qs.sql_handle
FROM sys.dm_exec_query_stats AS qs
 
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC
) t CROSS APPLY sys.dm_exec_sql_text( t.sql_handle ) t1
 
 
 
--查询显示缓存计划所占用的 CPU 总使用率
SELECT
total_cpu_time,
total_execution_count,
number_of_statements,
s2.text
--(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text
FROM
(SELECT TOP 50
SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
COUNT(*) AS number_of_statements,
qs.sql_handle --,
--MIN(statement_start_offset) AS statement_start_offset,
--MAX(statement_end_offset) AS statement_end_offset
FROM
sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC) AS stats
CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2
 
 
--下面的示例查询显示已重新编译的前 25 个存储过程。plan_generation_num 指示该查询已重新编译的次数。
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc
 
 
 
--效率较低的查询计划可能增大 CPU 占用率。
--下面的查询显示哪个查询占用了最多的 CPU 累计使用率。
SELECT
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
 
 
 
--下面的查询显示一些可能占用大量 CPU 使用率的运算符（例如 ‘%Hash Match%’、‘%Sort%’）以找出可疑对象。
 
select *
from
sys.dm_exec_cached_plans
cross apply sys.dm_exec_query_plan(plan_handle)
where
cast(query_plan as nvarchar(max)) like ‘%Sort%‘
or cast(query_plan as nvarchar(max)) like ‘%Hash Match%‘
 
 
 
--查询哪个查询阻塞了其他的查询
SELECT
blocked_query.session_id AS blocked_session_id,
blocking_query.session_id AS blocking_session_id,
sql_text.text AS blocking_text,
waits.wait_type AS blocking_resource
FROM sys.dm_exec_requests blocked_query
JOIN sys.dm_exec_requests blocking_query ON blocked_query.blocking_session_id = blocking_query.session_id
CROSS APPLY (SELECT * FROM sys.dm_exec_sql_text(blocking_query.sql_handle) ) sql_text
JOIN sys.dm_os_waiting_tasks waits ON waits.session_id = blocking_query.session_id
 
 
 
--运行下面的查询可确定阻塞的会话。
 
select blocking_session_id, wait_duration_ms, session_id from
sys.dm_os_waiting_tasks
where blocking_session_id is not null
使用此调用可找出 blocking_session_id 所返回的 SQL。例如，如果 blocking_session_id 是 87，则运行此查询可获得相应的 SQL。
 
 
 
 
--查看被锁表：
  
select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName  
from   sys.dm_tran_locks where resource_type=‘OBJECT‘
  
--spid   锁表进程
--tableName   被锁表名
  
--解锁：
  
declare @spid  int
Set @spid  = 57 --锁表进程
declare @sql varchar(1000)
set @sql=‘kill ‘+cast(@spid  as varchar)
exec(@sql)
 
 
检查闩锁等待统计信息以确定 I/O 瓶颈。运行下面的 DMV 查询以查找 I/O 闩锁等待统计信息。
 
select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms / waiting_tasks_count
from sys.dm_os_wait_stats
where wait_type like ‘PAGEIOLATCH%‘ and waiting_tasks_count > 0
order by wait_type
如果 waiting_task_counts 和 wait_time_ms 与正常情况相比有显著变化，则可以确定存在 I/O 问题。获取 SQL Server 平稳运行时性能计数器和主要 DMV 查询输出的基线非常重要。
这些 wait_types 可以指示您的 I/O 子系统是否遇到瓶颈。
使用以下 DMV 查询来查找当前挂起的 I/O 请求。请定期执行此查询以检查 I/O 子系统的运行状况，并隔离 I/O 瓶颈中涉及的物理磁盘。
 
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
在正常情况下，该查询通常不返回任何内容。如果此查询返回一些行，则需要进一步调查。
您还可以执行下面的 DMV 查询以查找 I/O 相关查询。
 
select top 5 (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_physical_reads,
Execution_count, statement_start_offset, p.query_plan, q.text
from sys.dm_exec_query_stats
cross apply sys.dm_exec_query_plan(plan_handle) p
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by (total_logical_reads + total_logical_writes)/execution_count Desc
 
 
 
 
下面的 DMV 查询可用于查找哪些批处理/请求生成的 I/O 最多。如下所示的 DMV 查询可用于查找可生成最多 I/O 的前五个请求。调整这些查询将提高系统性能。
 
select top 5
(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,
sql_handle,
plan_handle
from sys.dm_exec_query_stats
order by (total_logical_reads + total_logical_writes) Desc
 
 
 
–-查询某个数据库的连接数
select count(*) from Master.dbo.SysProcesses where dbid=db_id()
 
–-CPU的压力
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
 
 
--查询是否由于连接没有释放引起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())


联系
我们