SQL2008 Database Optimization Common scripts

--Query the number of connections for a database
Select COUNT (*) from Master.dbo.SysProcesses where dbid=db_id ()

--Top 10 other wait types
SELECT TOP Ten * 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% '

The pressure of--CPU
SELECT scheduler_id, Current_tasks_count, Runnable_tasks_count
From Sys.dm_os_schedulers
WHERE scheduler_id < 255

-The top 10 worst performers use the query
SELECT TOP 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 total signal wait time,
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) * [signal_wait_percent signal wait%],
SUM (Wait_time_ms-signal_wait_time_ms) * 1.0/sum (Wait_time_ms) * [resource_wait_percent resource wait%]
From Sys.dm_os_wait_stats

--a signal waits too much time on the resource wait time then your CPU is a bottleneck at present.
--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_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 joins master.sys.dm_exec_connections B on a.session_id=b.session_id

--Current number of 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 a 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 releasing an 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 consumes 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

-View a query statement (not released in the current cache) that uses a small amount of reuse in the cache, which is not freed in the buffer-global
SELECT TOP 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 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 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--Statistics total CPU time
--order by avg_cpu_time DESC--Statistical average single query CP U time

-Calculates the number of worker processes in a running 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, record int, reserve space varchar (100), use space varchar (100), Index use space varchar (100), unused space varchar ()
INSERT into #tb exec sp_msforeachtable ' exec sp_spaceused '?
Select * from #tb
Go
Select
Table name,
record number,
cast (LTrim (RTrim (replace (reserved space, ' KB ', ')) as int)/1024 Reserved space MB ,
Cast (LTrim (RTrim (Use space, ' KB ', ')) as int)/1024 Use space MB,
cast (LTrim (replace (use space, ' KB ', ')) as int)/1024/1024.00 uses space GB,
Cast (LTrim (RTrim (replace (index use 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 (with space, ' KB ', ')) as int)/1024 > 0
--order by record D ESC
ORDER by using space MB DESC
DROP TABLE #tb

--Query whether the CPU is too high due to a 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 releasing an 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 consumes the most CPU resources and the statements it executes (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 reuse in the cache, memory-intensive query statements (not released in the current cache)-Global
SELECT TOP 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 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 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--statistics total CPU time
--order by Avg_cpu_time DESC--statistical average single query CPU time

--Calculate the number of worker processes in the operational 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 ' Each logical read count '
, Total_logical_reads N ' logical read total number of times '
, Total_logical_writes N ' logical write total number of times '
, Execution_count N ' execution times '
, total_worker_time/1000 N ' Total CPU time MS '
, total_elapsed_time/1000 N ' total time spent 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

SQL2008 Database Optimization Common scripts