sqlserver2008数据库优化常用脚本
发布时间：2020-12-12 14:47:37 所属栏目：MsSql教程 来源：网络整理
导读：--查询某个数据库的连接数 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
|
--查询某个数据库的连接数
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_usage*8 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,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())? --查看当前占用 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(使用空间,''))) as int)/1024 使用空间MB,''))) as int)/1024/1024.00 使用空间GB, cast(ltrim(rtrim(replace(索引使用空间,''))) as int)/1024 索引使用空间MB, cast(ltrim(rtrim(replace(未用空间,''))) as int)/1024 未用空间MB? FROM #tb? WHERE cast(ltrim(rtrim(replace(使用空间,''))) 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,getdate())? ----查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU) select spid, (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,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, ? ? ? ? ? ((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 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |