常用的sql
来源:互联网 发布:淘宝服装店创业计划书 编辑:程序博客网 时间:2024/06/11 19:49
1. 查看连接数:
use master
SELECT program_name,* FROM [Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN(SELECT [DBID]FROM [Master].[dbo].[SYSDATABASES] WHERE NAME='test') order by spid
sysprocesses : kpid 线程号(内核进程 id), spid进程号, lastwaittype
lastwaittype = SOS_SCHEDULER_YIELD 任务执行的时候主动放弃CPU轮询处理(Windows为多任务OS),并继续等待。
如果多个进程有SOS_SCHEDULER_YIELD,说明有CPU压力
2. 查看有未提交事务的进程
select sysprocesses.open_tran, * from sysprocesses where open_tran>0
3. 查看是否有进程在等待CPU
SELECT scheduler_id, current_tasks_count, runnable_tasks_count FROM sys.dm_os_schedulers WHERE scheduler_id < 255
如果 runnable_tasks_count>0 则可能存在CPU瓶颈
4. 占CPU时间最多的SQL
SELECT SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,((CASEqs.statement_end_offsetWHEN-1THENDATALENGTH(qt.TEXT)ELSEqs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,qs.total_logical_reads,qs.last_logical_reads,
qs.total_logical_writes,qs.last_logical_writes,qs.total_worker_time,
qs.last_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)qp
ORDER BY qs.total_worker_time DESC -- CPU time
当前缓存的哪些批处理或过程占用了大部分 CPU 资源
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 qsGROUP BY qs.sql_handleORDER BY SUM(qs.total_worker_time) DESC
缓存计划所占用的 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_textFROM ( 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
显示 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如果系统存在过多的编译和重新编译,可能会导致系统出现与 CPU 相关的性能问题。过多编译/重新编译的
select * from sys.dm_exec_query_optimizer_info where counter = 'optimizations' or counter = 'elapsed time'已重新编译的前 25 个存储过程哪个查询占用了最多的 CPU 累计使用率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_textwhere plan_generation_num > 1order by plan_generation_num desc
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 qorder by highest_cpu_queries.total_worker_time desc
可能占用大量 CPU 使用率的运算符
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%'
5.快速获取表的行数
select rows from sysindexes where id=object_id('weblog') and indid in(0,1)
6.清除缓存和执行计划缓存
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
7.清空日志
DUMP TRANSACTION 库名 WITH NO_LOG
8.截断事务日志
BACKUP LOG vttest TO DISK='X:\sql2008Data\vttest_log.bak' WITH INIT,NO_TRUNCATE
9.收缩数据库
DBCC SHRINKDATABASE(数据库名)
10.收缩文件
select * from sysfiles --找到文件ID
DBCC SHRINKFILE(2) --吧ID作为参数传入
11.查询所有表的大小
create PROCEDURE get_tableinfo AS
if not exists(select * from dbo.sysobjects where id=object_id(N'[dbo].[tablespaceinfo]')and OBJECTPROPERTY(id,N'IsUserTable')=1)
create table tablespaceinfo(nameinfo varchar(50), rowsinfo int, reserved varchar(20),
datainfo varchar(20), index_size varchar(20), unused varchar(20))
delete from tablespaceinfo--清空数据表
declare @tablename varchar(255) --表名称
declare @cmdsql varchar(500)
DECLARE Info_cursor CURSOR FOR
select o.name from dbo.sysobjects o where OBJECTPROPERTY(o.id,N'IsTable')=1 and o.name not like N'#%%' order by o.name
OPEN Info_cursor
FETCH NEXT FROM Info_cursor INTO @tablename
WHILE @@FETCH_STATUS=0
BEGIN
if exists(select * from dbo.sysobjects where id=object_id(@tablename) and OBJECTPROPERTY(id,N'IsUserTable')=1)
execute sp_executesql N'insert into tablespaceinfo exec sp_spaceused @tbname', N'@tbname varchar(255)', @tbname=@tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
GO
--执行存储过程
exec get_tableinfo
--查询运行该存储过程后得到的结果
select * from tablespaceinfo order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc
12.查看阻塞
use master
go
declare @spid int,@bl int
DECLARE s_cur CURSOR FOR
select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
begin
if @spid =0
select ' 引起数据库死锁的是 : '+ CAST(@bl AS VARCHAR(10)) + ' 进程号 , 其执行的 SQL 语法如下 '
else
select ' 进程号 SPID : '+ CAST(@spid AS VARCHAR(10))+ ' 被 ' + ' 进程号 SPID : '+ CAST(@bl AS VARCHAR(10)) +' 阻塞 , 其当前进程执行的 SQL 语法如下 '
DBCC INPUTBUFFER (@bl )
FETCH NEXT FROM s_cur INTO @spid,@bl
end
CLOSE s_cur
DEALLOCATE s_cur
exec sp_who2
- SQL-sql常用的语句
- 常用的SQL语句
- 常用的sql函数
- 常用的SQL语句
- 常用的SQL语句
- 常用的SQL归纳
- 常用的SQL语句
- 常用的sql命令
- 常用到的sql:
- 常用的SQL语句
- 常用的SQL
- SQL常用的函数
- SQL语句常用的
- 常用的SQL语言
- 常用的T-SQL
- 常用的sql面试
- 常用的SQL语言
- 常用的sql语法
- 回车字符'\r'与换行字符'\n'
- Linux From Scratch
- C++游戏开发需要阅读的书籍
- 10gR2 streams推荐安装配置
- 系统字符代码页
- 常用的sql
- C# 软件注册功能
- 为什么++[[]][+[]]+[+[]]=10
- J2ME内存占用详解及优化方法
- ORACLE SQL: 从一个表中查询数据插入另一个表中
- [Paper Reading] A Flexible New Technique for Camera Calibration
- chrome 扩展 hello world 本土版(煎蛋无聊图)
- C++ Native Multi-Targeting
- Android读写文件示例