SQL server 2008 数据库优化常用脚本
--查询某个数据库的连接数
selectcount(*)fromMaster.dbo.SysProcesseswheredbid=db_id()
--前10名其他等待类型
SELECTTOP10*fromsys.dm_os_wait_stats
ORDERBYwait_time_msDESC
SELECT*FROMsys.dm_os_wait_statsWHEREwait_typelike'PAGELATCH%'
ORwait_typelike'LAZYWRITER_SLEEP%'
--CPU的压力
SELECTscheduler_id,current_tasks_count,runnable_tasks_count
FROMsys.dm_os_schedulers
WHEREscheduler_id<255
--表现最差的前10名使用查询
SELECTTOP10ProcedureName=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)
FROMsys.dm_exec_query_statss
CROSSAPPLYsys.dm_exec_sql_text(s.sql_handle)tORDERBY
s.max_physical_readsDESC
SELECTSUM(signal_wait_time_ms)AStotal_signal_wait_time_ms总信号等待时间,
SUM(wait_time_ms-signal_wait_time_ms)ASresource_wait_time_ms资源的等待时间,
SUM(signal_wait_time_ms)*1.0/SUM(wait_time_ms)*100AS[signal_wait_percent信号等待％],
SUM(wait_time_ms-signal_wait_time_ms)*1.0/SUM(wait_time_ms)*100AS[resource_wait_percent资源等待％]
FROMsys.dm_os_wait_stats
--一个信号等待时间过多对资源的等待时间那么你的CPU是目前的一个瓶颈。
--查看进程所执行的SQL语句
if(selectCOUNT(*)frommaster.dbo.sysprocesses)>500
begin
selecttext,CROSSAPPLYmaster.sys.dm_exec_sql_text(a.sql_handle)frommaster.sys.sysprocessesa
end
selecttext,a.*frommaster.sys.sysprocessesa
CROSSAPPLYmaster.sys.dm_exec_sql_text(a.sql_handle)
wherea.spid='51'
dbccinputbuffer(53)
withtb
as
(
selectblocking_session_id,
session_id,db_name(database_id)asdbname,textfrommaster.sys.dm_exec_requestsa
CROSSAPPLYmaster.sys.dm_exec_sql_text(a.sql_handle)
),
tb1as
(
selecta.,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage8as'memory_usage(KB)',
total_scheduled_time,reads,writes,logical_reads
fromtbainnerjoinmaster.sys.dm_exec_sessionsb
ona.session_id=b.session_id
)
selecta.*,connect_time,client_tcp_port,client_net_addressfromtb1ainnerjoinmaster.sys.dm_exec_connectionsbona.session_id=b.session_id
--当前进程数
select*frommaster.dbo.sysprocesses
orderbycpudesc
--查看当前活动的进程数
sp_whoactive
--查询是否由于连接没有释放引起CPU过高
select*frommaster.dbo.sysprocesses
wherespid>50
andwaittype=0x0000
andwaittime=0
andstatus='sleeping'
andlast_batch<dateadd(minute,-10,getdate())
andlogin_time<dateadd(minute,-10,getdate())
--强行释放空连接
select'kill'+rtrim(spid)frommaster.dbo.sysprocesses
wherespid>50
andwaittype=0x0000
andwaittime=0
andstatus='sleeping'
andlast_batch<dateadd(minute,-60,getdate())
andlogin_time<dateadd(minute,-60,getdate())
--查看当前占用cpu资源最高的会话和其中执行的语句(及时CPU)
selectspid,cmd,cpu,physical_io,memusage,
(selecttop1[text]from::fn_get_sql(sql_handle))sql_text
frommaster..sysprocessesorderbycpudesc,physical_iodesc
--查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局
SELECTTOP100usecounts,objtype,p.size_in_bytes,[sql].[text]
FROMsys.dm_exec_cached_planspOUTERAPPLYsys.dm_exec_sql_text(p.plan_handle)sql
ORDERBYusecounts,p.size_in_bytesdesc
SELECTtop25qt.text,qs.plan_generation_num,qs.execution_count,dbid,objectid
FROMsys.dm_exec_query_statsqs
CROSSAPPLYsys.dm_exec_sql_text(sql_handle)asqt
WHEREplan_generation_num>1
ORDERBYqs.plan_generation_num
SELECTtop50qt.textASSQL_text,SUM(qs.total_worker_time)AStotal_cpu_time,
SUM(qs.execution_count)AStotal_execution_count,
SUM(qs.total_worker_time)/SUM(qs.execution_count)ASavg_cpu_time,
COUNT(*)ASnumber_of_statements
FROMsys.dm_exec_query_statsqs
CROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)asqt
GROUPBYqt.text
ORDERBYtotal_cpu_timeDESC--统计总的CPU时间
--ORDERBYavg_cpu_timeDESC--统计平均单次查询CPU时间
--计算可运行状态下的工作进程数量
SELECTCOUNT(*)asworkers_waiting_for_cpu,s.scheduler_id
FROMsys.dm_os_workersASo
INNERJOINsys.dm_os_schedulersASs
ONo.scheduler_address=s.scheduler_address
ANDs.scheduler_id<255
WHEREo.state='RUNNABLE'
GROUPBYs.scheduler_id
--表空间大小查询
createtable#tb(表名sysname,记录数int,保留空间varchar(100),使用空间varchar(100),索引使用空间varchar(100),未用空间varchar(100))
insertinto#tbexecsp_MSForEachTable'EXECsp_spaceused''?'''
select*from#tb
go
SELECT
表名,
记录数,
cast(ltrim(rtrim(replace(保留空间,'KB','')))asint)/1024保留空间MB,
cast(ltrim(rtrim(replace(使用空间,'KB','')))asint)/1024使用空间MB,
cast(ltrim(rtrim(replace(使用空间,'KB','')))asint)/1024/1024.00使用空间GB,
cast(ltrim(rtrim(replace(索引使用空间,'KB','')))asint)/1024索引使用空间MB,
cast(ltrim(rtrim(replace(未用空间,'KB','')))asint)/1024未用空间MB
FROM#tb
WHEREcast(ltrim(rtrim(replace(使用空间,'KB','')))asint)/1024>0
--orderby记录数desc
ORDERBY使用空间MBDESC
DROPTABLE#tb
--查询是否由于连接没有释放引起CPU过高
select*frommaster.dbo.sysprocesses
wherespid>50
andwaittype=0x0000
andwaittime=0
andstatus='sleeping'
andlast_batch<dateadd(minute,-10,getdate())
andlogin_time<dateadd(minute,-10,getdate())
--强行释放空连接
select'kill'+rtrim(spid)frommaster.dbo.sysprocesses
wherespid>50
andwaittype=0x0000
andwaittime=0
andstatus='sleeping'
andlast_batch<dateadd(minute,-60,getdate())
andlogin_time<dateadd(minute,-60,getdate())
----查看当前占用cpu资源最高的会话和其中执行的语句(及时CPU)
selectspid,cmd,cpu,physical_io,memusage,
(selecttop1[text]from::fn_get_sql(sql_handle))sql_text
frommaster..sysprocessesorderbycpudesc,physical_iodesc
----查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局
SELECTTOP100usecounts,objtype,p.size_in_bytes,[sql].[text]
FROMsys.dm_exec_cached_planspOUTERAPPLYsys.dm_exec_sql_text(p.plan_handle)sql
ORDERBYusecounts,p.size_in_bytesdesc
SELECTtop25qt.text,qs.plan_generation_num,qs.execution_count,dbid,objectid
FROMsys.dm_exec_query_statsqs
CROSSAPPLYsys.dm_exec_sql_text(sql_handle)asqt
WHEREplan_generation_num>1
ORDERBYqs.plan_generation_num
SELECTtop50qt.textASSQL_text,SUM(qs.total_worker_time)AStotal_cpu_time,
SUM(qs.execution_count)AStotal_execution_count,
SUM(qs.total_worker_time)/SUM(qs.execution_count)ASavg_cpu_time,
COUNT(*)ASnumber_of_statements
FROMsys.dm_exec_query_statsqs
CROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)asqt
GROUPBYqt.text
ORDERBYtotal_cpu_timeDESC--统计总的CPU时间
--ORDERBYavg_cpu_timeDESC--统计平均单次查询CPU时间
--计算可运行状态下的工作进程数量
SELECTCOUNT(*)asworkers_waiting_for_cpu,s.scheduler_id
FROMsys.dm_os_workersASo
INNERJOINsys.dm_os_schedulersASs
ONo.scheduler_address=s.scheduler_address
ANDs.scheduler_id<255
WHEREo.state='RUNNABLE'
GROUPBYs.scheduler_id
SELECTcreation_timeN'语句编译时间'
,last_execution_timeN'上次执行时间'
,total_physical_readsN'物理读取总次数'
,total_logical_reads/execution_countN'每次逻辑读次数'
,total_logical_readsN'逻辑读取总次数'
,total_logical_writesN'逻辑写入总次数'
,execution_countN'执行次数'
,total_worker_time/1000N'所用的CPU总时间ms'
,total_elapsed_time/1000N'总花费时间ms'
,(total_elapsed_time/execution_count)/1000N'平均时间ms'
,SUBSTRING(st.text,(qs.statement_start_offset/2)+1,
((CASEstatement_end_offset
WHEN-1THENDATALENGTH(st.text)
ELSEqs.statement_end_offsetEND
-qs.statement_start_offset)/2)+1)N'执行语句'
FROMsys.dm_exec_query_statsASqs
CROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)st
whereSUBSTRING(st.text,(qs.statement_start_offset/2)+1,
((CASEstatement_end_offset
WHEN-1THENDATALENGTH(st.text)
ELSEqs.statement_end_offsetEND
-qs.statement_start_offset)/2)+1)notlike'%fetch%'
ORDERBYtotal_elapsed_time/execution_countDESC