SQL server 2008 数据库优化常用脚本

--查询某个数据库的连接数
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