Home>

--Query the number of connections to a database

select count (*) from master.dbo.sysprocesses where dbid=db_id ()

--Top 10 other waiting types

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 pressure

select scheduler_id, current_tasks_count, runnable_tasks_count

from sys.dm_os_schedulers

where scheduler_id<255

-Top 10 worst performing queries

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 resource wait time,

sum (signal_wait_time_ms) * 1.0/sum (wait_time_ms) * 100 as [signal_wait_percent signal wait%],

sum (wait_time_ms-signal_wait_time_ms) * 1.0/sum (wait_time_ms) * 100 as [resource_wait_percent resource wait%]

from sys.dm_os_wait_stats

-A signal waiting time too much waiting time for resources then your cpu is currently a bottleneck.

-View the SQL statements executed by the process

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

--Number of current processes

select * from master.dbo.sysprocesses

order by cpu desc

--View the number of currently active processes

sp_who active

-Query whether the CPU is too high due to the connection not being released

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 ())

--Forcibly release the empty connection

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 ())

--View the session that currently uses the most CPU resources and the statements executed in them (timely 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

--Look at the number of reuses in the cache,Memory-hungry query statements (not released in the current cache)-global

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-count the total cpu time

--order by avg_cpu_time desc --Statistical average single query cpu time

-Calculate the number of worker processes in a runnable state

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

--Table space size query

create table #tb (table name sysname, number of records int, reserved space varchar (100), used space varchar (100), index used space varchar (100), unused space varchar (100))

insert into #tb exec sp_msforeachtable "exec sp_spaceused" "?" ""

select * from #tb

go

select

Table Name,

Records,

cast (ltrim (rtrim (replace (reserve space,"kb", ""))) as int)/1024 reserved space mb,

cast (ltrim (rtrim (replace (use space,"kb", ""))) as int)/1024 use space mb,

cast (ltrim (rtrim (replace (use space,"kb", ""))) as int) /1024/1024.00 use space gb,

cast (ltrim (rtrim (replace (index uses space,"kb", ""))) as int)/1024 index uses space mb,

cast (ltrim (rtrim (replace (unused space,"kb", ""))) as int)/1024 unused space mb

from #tb

where cast (ltrim (rtrim (replace (use space,"kb", ""))) as int)/1024>0

--order by number of records desc

order by mb desc

drop table #tb

-Query whether the CPU is too high due to the connection not being released

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 ())

--Forcibly release the empty connection

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 ())

---- View the session that currently occupies the most CPU resources and the statements executed in it (timely 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

---- Look at the low number of reuses in the cache,Memory-hungry query statements (not released in the current cache)-global

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-count the total cpu time

--order by avg_cpu_time desc --Statistical average single query cpu time

-Calculate the number of worker processes in a runnable state

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 "statement compilation time"

, last_execution_time n "Last execution time"

, total_physical_reads n "Total number of physical reads"

, total_logical_reads/execution_count n "Number of logical reads per time"

, total_logical_reads n "Total number of logical reads"

, total_logical_writes n "Total logical writes"

, execution_count n "Number of executions"

, total_worker_time/1000 n "Total CPU time used ms"

, total_elapsed_time/1000 n "Total elapsed time ms"

, (total_elapsed_time/execution_count)/1000 n "Average time 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 "Execute statement"

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