SQL记录 CUP占用率，锁使用情况，JOB情况等.sql
as
SET NOCOUNT ON
-- Author: Saleem Hakani(Microsoft Corporation)
-- Email: Saleem@sqlcommunity.com
-- Website: www.sqlcommunity.com
-- Date: June 3rd 2008
-- Purpose: This procedure contains set of tasks that can help you troubleshoot performance related issues on your SQL Server
If (@Command is null or @Command='Help')
Begin
Print 'HELP for Ops_RoboPERF'
Print '======================='
Print ''
Print 'Ops_RoboPERF procedure provides you with the following information:'
Print''
Print '1) HIGHCPU - Top 50 statements that are CPU intensive'
Print '2) LOCKS - Detect real time locking information'
Print '3) TempDBData - View current SQL Statements from TempDB'
Print '4) SQLJobs - View SQL Server Job details'
Print '5) IndexUsage - View which indexes are being used'
Print ''
Print 'Ops_RoboPERF Syntax and Usage'
Print '-----------------------------'
Print 'Syntax: Exec Ops_RoboPERF [<Command> | ''Help'']'
Print ''
Print 'Examples:'
Print '---------'
Print 'To obtain this help'
Print 'Example: Exec Ops_RoboPERF ''Help'''
Print ''
Print 'To get the top 50 SQL Statements that are CPU intensive'
Print 'Exec Ops_RoboPERF ''HIGHCPU'''
Print ''
Print 'To get real-time locking information'
Print 'Exec Ops_RoboPERF ''Locks'''
Return
End
--Top 50 CPU bound statements
--CPU Expensive Queries
If (@Command='HIGHCPU')
Begin
SELECT TOP 50 (a.total_worker_time/a.execution_count) as [Avg_CPU_Time],
Convert(Varchar,Last_Execution_Time) as 'Last_execution_Time',
Total_Physical_Reads,
SUBSTRING(b.text,a.statement_start_offset/2,
(case when a.statement_end_offset = -1 then len(convert(nvarchar(max), b.text)) * 2
else
a.statement_end_offset end - a.statement_start_offset)/2) as Query_Text,
dbname=Upper(db_name(b.dbid)),
b.objectid as 'Object_ID'
FROM sys.dm_exec_query_stats a
cross apply
sys.dm_exec_sql_text(a.sql_handle) as b
ORDER BY
[Avg_CPU_Time] DESC
End
--End of HIGHCPU
--Detect real-time locks and blocker information
If (@Command='Locks')
Begin
If Exists (Select Name from TempDB..SysObjects Where Name = '##Tmp_Ops_Locks')
Begin
Drop Table ##Tmp_Ops_Locks
End
Select t1.resource_type as [Lock_Type]
,db_name(resource_database_id) as [DB_Name]
,t1.resource_associated_entity_id as [Victim]
,t1.request_mode as [Requested_by] -- lock requested
,t1.request_session_id as [Waiter_SPID] -- spid of waiter
,t2.wait_duration_ms as [Wait_Time]
,(select text from sys.dm_exec_requests as r --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as Waiter_Batch
,(select substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2)
from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as Waiter_STMT --- this is the statement executing right now
,t2.blocking_session_id as [Blocker_SPID] -- spid of blocker
,(select text from sys.sysprocesses as p --- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as Blocker_STMT
into ##Tmp_Ops_Locks from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address
If (Select count(*) from ##Tmp_Ops_Locks) = 0
Begin
Print 'No locks found'
End
If (Select count(*) from ##Tmp_Ops_Locks) > 0
Begin
Select * from ##Tmp_Ops_Locks
End
End
--End of LOCKS
--Dump all SQL Handles from TempDB into #Tmp_Ops_TempDATA
If (@Command = 'TEMPDBData')
Begin
SELECT
t3.sql_handle as 'SQLHandle',
t3.statement_start_offset as 'Statement_Start_OffSET',
t3.statement_end_offset as 'Statement_End_OffSET'
Into #Tmp_Ops_TempDATA
from sys.dm_db_session_space_usage as t1, sys.dm_exec_requests t3,
(select session_id, sum(internal_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count) as task_dealloc
from sys.dm_db_task_space_usage group by session_id) as t2
where t1.session_id = t2.session_id and t1.session_id >50
and t1.database_id = 2 --- tempdb is database_id=2
and t1.session_id = t3.session_id
If Exists (Select Name from SysObjects where Name='#Tmp_TempData')
Begin
Drop Table #Tmp_TempData
End
If Exists (Select Name from SysObjects where Name='#Tmp_Ops_TempDATA')
Begin
Drop Table #Tmp_Ops_TempDATA
End
--Go thru each handle and display the results
Declare @SQLHandle VarBinary(64),@StartOffSet int,@EndOffSet int
Declare CheckOps_TempDATA Cursor For Select [SQLHandle],Statement_Start_OffSET,Statement_End_OffSET from #Tmp_Ops_TempDATA
Open CheckOps_TempDATA
Fetch Next from CheckOps_TempDATA into @SQLHandle,@StartOffSet,@EndOffSet
While (@@Fetch_Status=0)
Begin
--Insert into #Tmp_TempData
Select
DB_Name(qt.dbid) as 'Database_Name',
substring(qt.text,s.statement_start_offset/2,
(case when s.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else s.statement_end_offset end -s.statement_start_offset)/2)
as 'SQL_Statement'
,s.statement_start_offset as 'Start_OffSET'
,s.statement_end_offset as 'End_OffSET'
,qt.text as 'TSQL_Batch'
,qt.objectid as 'Object_ID'
,s.execution_count as 'Execution_Count'
,s.total_physical_reads as 'Total_Physical_Reads'
,s.total_logical_writes as 'Total_Logical_Writes'
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text(s.sql_handle) as qt
where s.sql_handle = @SQLHANDLE
and s.statement_start_offset = @StartOffSET
and s.statement_end_offset = @EndOffSET
Fetch Next from CheckOps_TempDATA into @SQLHandle,@StartOffSet,@EndOffSet
End
Close CheckOps_TempDATA
Deallocate CheckOps_TempDATA
--Select * from #Tmp_TempData
--End of TEMPData
--SQL Server Job related information
If (@Command = 'TEMPDBData')
Begin
If Exists (Select Name from TempDB..SysObjects where Name='##Ops_JobDETAILS')
Begin
DRop Table ##Ops_JobDETAILS
End
If Exists (Select Name From SysObjects where Name='#Ops_TmpJobFailures')
Begin
Drop Table #Ops_TmpJobFailures
End
Select
a.Name as 'Job_Name'
,Count(B.Run_Status) as 'Total_Failures'
,ISNULL(Convert(Varchar(12),Cast(cast(min(b.run_date) as varchar(8)) as Datetime)),'0') as 'Failing_Since'
Into #Ops_TmpJobFailures
from MSDB.dbo.sysJobHistory b
right join MSDB.dbo.SysJobs a
on a.Job_ID=b.Job_ID
and b.Run_Status=0
Group by a.Name
Select
Substring(a.Name,1,50) as 'SQL Job Name'
,Max(b.Step_id) as 'Total Steps'
,Substring(SUSER_SNAME(a.Owner_SID),1,30) as 'Owner'
,Convert(varchar(24),a.Date_Created) as 'Created on'
,"Enabled"=
Case a.Enabled
When 1 then 'Yes'
When 2 then 'No'
End
,b.Retry_attempts
,c.Total_Failures
,Convert(Varchar(24),c.Failing_Since) as 'Failing_Since'
Into ##Ops_JobDETAILS
from msdb..sysjobs a
Join msdb..sysjobsteps b
on a.job_id=b.job_id
Join #Ops_TmpJobFailures c
on a.[Name]=c.Job_Name
Group by a.[Name]
,a.owner_SID
,a.Date_Created
,a.Enabled
,b.retry_attempts
,c.Total_Failures
,c.Failing_Since
Select * from ##Ops_JobDETAILS
End
--End of Job details
---
If (@Command='IndexUsage')
Begin
Create Table #TmpTable
(
Database_Name Varchar(255),
ObjectName Varchar(255),
Index_Name Varchar(255),
Index_Type Varchar(50),
Total_Reads Int,
Total_Writes Int
)
Declare @DBName Varchar(255),@Str NVarchar(4000)
SET @DBName=(SELECT MIN(Name) FROM Master.sys.Databases where Name not in ('Master','MSDB','Model','TempDB'))
WHILE @DBName IS NOT NULL
BEGIN
Select @Str='Use ['+@DBName+'];
Select '''+Ltrim(@DBName)+''' as ''Database_Name'',
Object_Name(a.Object_ID) as ''Object_Name'',
a.Name as ''Index_NAME'',
a.Type_Desc as ''Index_TYPE'',
(b.User_Seeks + b.User_Scans + User_Lookups) as ''Total_Reads'',
b.User_Updates as ''Total_Writes''
From
Sys.DM_DB_Index_Usage_Stats b join Sys.Indexes a
on a.Object_ID = b.Object_ID and
a.Index_ID = b.Index_ID and
b.Database_ID = DB_ID('''+@DBName+''') and
Object_Name(a.Object_ID) not like ''sys%''
and ObjectProperty(a.object_id,''IsUserTable'') = 1'
Insert Into #TmpTable Exec SP_ExecuteSQL @Str
SET @DBName=(SELECT MIN(Name) FROM Master.Sys.Databases where name > @DBName and Name not in ('Master','MSDB','Model','TempDB'))
END
Select * from #TmpTable
End
------ End of IndexUsage
End
GO
