SQL Server 2008 Database Optimization Common script _mssql2008

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

--Top 10 other wait types
SELECT Top * 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 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 the wait time for the resource,
SUM (Signal_wait_time_ms) * 1.0/sum (Wait_time_ms) * As [signal_wait_percent signal waiting%],
SUM (Wait_time_ms-signal_wait_time_ms) * 1.0/sum (Wait_time_ms) * As [resource_wait_percent resource waiting%]
From Sys.dm_os_wait_stats

--a signal waiting time too much for the resource waiting time then your CPU is a bottleneck at the moment.
--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 join 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 processes currently active
sp_who Active

--Query whether the CPU is too high because the connection is not 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 the null 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 current CPU-intensive sessions and statements executed in it (Just-in-time 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 the less reuse in the cache, a query statement that consumes large memory (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_i N_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 check Consult CPU 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.schedul er_id<255
WHERE o.state= ' RUNNABLE '
GROUP by s.scheduler_id

-tablespace size query
CREATE TABLE #tb (table name sysname, number of records int, reserve space varchar (100), use space varchar (100), Index use space varchar (100), unused space varchar (MB)
INSERT into #tb exec sp_msforeachtable ' exec sp_spaceused '? '
Select * from #tb
Go
Select
Table name,
record number,
cast (LTrim (replace (reserved space, ' KB ', ') as int)/1024 Reserved space MB ,
Cast (RTrim (replace (use space, ' KB ', ') as int)/1024 uses space MB,
cast (LTrim (replace (use space, ' KB ', ')) as int)/1024/1024.00 uses space GB,
cast (RTrim (replace (index use space, ' KB ', ') as int)/1024 index uses space MB,
cast (LTrim (RTrim (Replace (unused space, ' KB ', ')) as int)/1024 unused MB
from #tb
WHERE cast (LTrim (replace (use space, ' KB ', ') as int)/1024 > 0
--order by record number DESC
Order by using space MB desc
DROP TABLE #tb

--Query whether the CPU is too high because the connection is not 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 the null 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 most CPU-intensive session and the statements executed in it (Just-in-time 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 the cache for less reuse, a query statement that consumes large memory (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_i N_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 check Consult CPU time

--Calculate 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
SELECT creation_time N ' statement compile time '
, Last_execution_time N ' last Execution time '
, total_physical_reads N ' total number of physical reads '
, Total_logical_reads/execution_count N ' per logical read times '
, the total number of total_logical_reads N ' logical reads '
, Total_logical_writes N ' logical write total number of times '
, Execution_count N ' execution times '
, total_worker_time/1000 N ' CPU total 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