The DBA Blog by Tomer Lev

SQL information, scripts, tricks and more…

50 top high CPU queries

select top 50

st.objectid, st.dbid,

— object_name(st.objectid, st.dbid), — uncomment this line if you’re using SQL 2005 SP2

total_worker_time/execution_count AS AverageCPUTime,

CASE statement_end_offset

WHEN -1 THEN st.text

ELSE SUBSTRING(st.text,statement_start_offset/2,statement_end_offset/2)

END AS StatementText

from

sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

ORDER BY AverageCPUTime DESC

Advertisements

June 22, 2009 Posted by | Scripts, SQL SERVER | , , , , , , , | Leave a comment

runtime of Jobs scheduled to run multiple times day on SQL Server 2005

I have developed a script to review the runtime of Jobs scheduled to run multiple times day on SQL Server 2005.

the script has been developed on SQL server 2005 and gives the ability to find out from all active jobs the precentage of running time divided to job interval in the last 24 hours.

couple of remarks:

1) Does not work in SQL Server 2000 because sp_help_jobschedule returns less columns in 2000
2) Does not support multiple Job schedules such as onetimes
3) Only list jobs that are scheduled to run multiple times a day

You can also find the post here:

http://www.sqlservercentral.com/scripts/Administration/63245/

The ones who does not have an account at sqlservercentral.com, here is the script:

USE [msdb]
GO
/*******************************************************
created by: Tomer Lev / Israel / SQL Server DBA
date: 28/05/2008
out put:
Run Date – job running date
Job Name
Average running time/Interval
Min running time/Interval
Max running time/Interval
stdev running time/Interval
*****************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[JobsTimeIntervalCheckingTime]
AS

create table #tempschedule
(
id int identity(1,1) ,
schedule_id int,
schedule_name sysname,
enabled int,
freq_type int,
freq_interval int,
freq_subday_type int,
freq_subday_interval int,
freq_relative_interval int,
freq_recurrence_factor int,
active_start_date int,
active_end_date int,
active_start_time int,
active_end_time int,
date_created datetime,
schedule_description nvarchar(4000),
next_run_date int,
next_run_time int,
schedule_uid uniqueidentifier,
jobcoubnt int
)

create table #helpschedule
(
jobname sysname,
freq_type int,
freq_subday_type int,
freq_subday_interval int
)

declare @jobnametemp sysname
declare @schedule_id int

DECLARE CursorQuery CURSOR FOR
SELECT [name] FROM sysjobs WHERE [enabled] = 1

OPEN CursorQuery
FETCH NEXT FROM CursorQuery
INTO @jobnametemp
PRINT ‘Record Status’ + CAST(@@FETCH_STATUS as varchar)
WHILE @@FETCH_STATUS = 0
BEGIN
insert #tempschedule exec msdb..sp_help_jobschedule @job_name=@jobnametemp
insert #helpschedule select @jobnametemp,freq_type,freq_subday_type,freq_subday_interval from #tempschedule
where id = @@identity
FETCH NEXT FROM CursorQuery
INTO @jobnametemp
END
CLOSE CursorQuery
DEALLOCATE CursorQuery

drop table #tempschedule

select
[Run Date],
[Job Name],
avg([RunningTime/Interval])*100 as [Avg RunningTime/Interval],
min([RunningTime/Interval])*100 as [Min RunningTime/Interval],
max([RunningTime/Interval])*100 as [Max RunningTime/Interval],
stdev([RunningTime/Interval])*100 as [stdev RunningTime/Interval],
count(*) as [count]
from(
select
[Run Date],
[Job Name],
(seconds + minutes * 60 + hours * 3600) [total seconds runnning time],
([frequency_sub_type] * [freq_subday_interval]) as [interval Time in seconds],
cast((seconds + minutes * 60 + hours * 3600) as float) / cast(([frequency_sub_type] * [freq_subday_interval]) as float) [RunningTime/Interval]
from(
SELECT CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) [Run Date],
SUBSTRING(T2.name,1,40) [Job Name],
T1.[run_duration],
len(T1.[run_duration]) [len],
case len(T1.[run_duration])
when 4 then right(T1.[run_duration],2)
when 5 then right(T1.[run_duration],2)
when 6 then right(T1.[run_duration],2)
else T1.[run_duration] end [seconds],
case len(T1.[run_duration])
when 1 then 0.00001
when 2 then 0.00001
when 3 then 0.00001
when 4 then left(T1.[run_duration],2)
when 5 then substring(cast(T1.[run_duration] as varchar),2,2)
when 6 then substring(cast(T1.[run_duration] as varchar),3,2)
else T1.[run_duration] end [minutes],
case len(T1.[run_duration])
when 1 then 0.00001
when 2 then 0.00001
when 3 then 0.00001
when 4 then 0.00001
when 5 then substring(cast(T1.[run_duration] as varchar),1,1)
when 6 then substring(cast(T1.[run_duration] as varchar),1,2)
else T1.[run_duration] end [hours],
case(h.freq_type)
when 1 then ‘Once’
when 4 then ‘Daily’
when 8 then ‘Weekly’
when 16 then ‘Monthly ‘
when 32 then ‘Monthly, relative to the freq_interval’
when 64 then ‘Run when SQLServerAgent service starts’ end [frequency_type],
case(h.freq_subday_type)
when 4 then 60
when 1 then 1
when 8 then 3600 end [frequency_sub_type], — min, sec, hours
h.freq_subday_interval — time interval
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
join #helpschedule h
on T2.name = h.jobname
where T2.enabled = 1 and T1.run_status not in (2,3,4) and T1.step_id = 0
AND run_date >= CONVERT(char(8), (select dateadd (day,-1,getdate())), 112))q
where [frequency_sub_type] * [freq_subday_interval] !=0
)
q2
group by [Run Date],[Job Name]

drop table #helpschedule

go

exec JobsTimeIntervalCheckingTime

Enjoy,
Tomer Lev

June 11, 2009 Posted by | Administration, SQL SERVER | , , | Leave a comment