Neil Hambly

Having recently started @ new employer, I'm still in the process of ("Setting up Shop") in regards to the various reports I want to be able to run on the instances

One of these items I had finally got around to do was installing was "SQL Server 2005 Performance Dashboard Reports"

This Company has SQL 2005 instances as well as SQL 2008 / 2008 R2 instances.

Well for SQL 2005 it was easy as pie, just run SETUP.sql and hey presto some great reports @ the drop of a hat, I had almost (well OK I had forgotten) 

That a small change to one of the DMV's in occurred in SQL Server 2008 this DMV is one used by reports, and it meant the script was not going

to work "as is" on the  SQL 2008 Server  without a small adjustment, so after I made a quick coffee I made the required change to the statement so it would work, specifically I'm talking about MS_PerfDashboard.usp_Main_GetCPUHistory stored procedure and I have shown them both below

Original (2005) which uses the cpu_ticks_in_ms and MY revised version for SQL 2008.. This uses the cpu_ticks  column (but it is not in ms) so needs to be multiple * 1000 as you can see in the scripts.. hope this can be of some benefit.

/* SQL SERVER 2005 Version (Original Script) */

if object_id('MS_PerfDashboard.usp_Main_GetCPUHistory', 'P') is not null

      drop procedure MS_PerfDashboard.usp_Main_GetCPUHistory

go

create procedure MS_PerfDashboard.usp_Main_GetCPUHistory

as

begin

      declare @ts_now bigint

      select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info

      select top 15 record_id,

            dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,

            SQLProcessUtilization,

            SystemIdle,

            100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization

      from (

            select

                  record.value('(./Record/@id)[1]', 'int') as record_id,

                  record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,

                  record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,

                  timestamp

            from (

                  select timestamp, convert(xml, record) as record

                  from sys.dm_os_ring_buffers

                  where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

                  and record like '%<SystemHealth>%') as x

            ) as y

      order by record_id desc

end

go

grant execute on MS_PerfDashboard.usp_Main_GetCPUHistory to public

go

/* SQL SERVER 2008+ Version (Modified Script) */

if object_id('MS_PerfDashboard.usp_Main_GetCPUHistory', 'P') is not null

      drop procedure MS_PerfDashboard.usp_Main_GetCPUHistory

go

create procedure MS_PerfDashboard.usp_Main_GetCPUHistory

as

begin

      declare @ts_now bigint

      select @ts_now = cpu_ticks / convert(float, ms_ticks)*1000 from sys.dm_os_sys_info

      SELECT TOP 15 record_id,

            dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,

            SQLProcessUtilization,

            SystemIdle,

            100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization

      from (

            select

                  record.value('(./Record/@id)[1]', 'int') as record_id,

                  record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,

                  record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,

                  timestamp

            from (

                  select timestamp, convert(xml, record) as record

                  from sys.dm_os_ring_buffers

                  where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

                  and record like '%<SystemHealth>%'

                  ) as x

            ) as y

      order by record_id desc

end

go

grant execute on dbo.usp_Main_GetCPUHistory to public

go

This blog is long overdue, I've had many an SQL related idea that could/should have made itself into a blog but has yet to happen.

So this is the going to be the first of the blogs I'm planning to post in next few weeks, so which topic is this one I have chosen today,

We'll try as I might to come up with a catchy title I must defeat as the Title clearly indicates.

Too many cooks' schedules ....spoil the broth MDSB database

So to be less cryptic, if you have anything like a typical environment, you will likely have some SQL instances

Which have many-many (this may even number in the hundreds) of SQL Jobs, I also suspect that a large proportion

Of these Jobs do have their very own schedules

So when we examine these what are the chances that some they have identical schedules?

(I'm thinking it is more than 50-50 chance), let's look @ an example to illustrate the point in case

Two example jobs (each with its own schedule - these are identical)

SQL Job ("Job 12")   executes @ on the following schedule: Every Weekday (Mon-Fri) on the hour

SQL Job ("Job 256") executes @ on the following schedule: Every Weekday (Mon-Fri) on the hour

So clearly this type of occurrence can and does regularly occur, with 2 of more different Jobs each running on identical schedules,

but having their own schedule, now if it was simply that this occurred to only a few schedules here and there, it's unlikely I would be writing this blog entry,

And sure some of those identical schedules may indeed even warrant their very own "dedicated" schedules,

But for the main I would speculate that a large percentage could and most IMHO should be using a "Shared" schedule as

This would be more advantageous. Why?

Well my reasoning is along the lines of being able to visualizing my overall Job Schedules,

Identifying when & which jobs ran @ a specific time or during a certain time range,

Aiding in troubleshooting, helping with capacity planning and other workload based analysis.....

Hopefully you getting the picture by now that using shared schedules can be beneficial IMHO.

So in an attempt to identify these potential schedules that are "grouped" I have coded a T-SQL script (listed below) to provide me with a report.

What is the script {attempting} to do - simply it is reporting on the existing schedules. With the following

1.        Group all related Schedules - See GrpNumber column

2.        Order by Schedule_id (first for each group) this to be the one to migrate the other's in for that group.

3.        Identify (see instruction column) on what Actions should be taken#

4.        Once reviewed the Instructions (choosing which to implement if so desired) to move certain jobs to other

           Schedules is simply executing the T-SQL command sp_attach_schedule         

           EXEC sp_attach_schedule @job_name = N'<Job Name>',@schedule_name = N'<schedule name>';

5.        Remove any schedule(s) that are now longer required.

           EXEC sp_delete_schedule (Arguments)             

Looking @ an Example:

In this example we have a group #5 with 2 schedules {id's 9 & 11}

With the 1^st schedule_id of these 2 (in ascending order) being schedule_id {9} naturally

So in this case we are using schedule_id {9} as the one we are retaining and migrating the other schedule_id(s)

for the group to this schedule, hence the instruction's "Keep This" for schedule_id {9} and schedule_id {11}

the instruction "Moving This to Schedule_id:9"

Instruction                              schedule_id  GrpNumber RowNumber     <other columns omitted>

Keep This                               9                   5                       1

Moving This to Schedule_id:9   11                  5                       2

So maybe look @ the schedule's you have on your servers and see if any "grouping" of schedules is possible

and then decide if this any sharing is appropriate to your environment - as always "IT DEPENDS" on your situation

WORD OF CAUTION: please make sure you fully understand want you are doing, and have backups in place prior to performing any actions..

But with that said I hope this can be of some benefit.

USE MDSB

GO

OVER(Partition by

,freq_relative_interval,freq_recurrence_factor,active_start_time

schedule_id,freq_type,freq_interval,freq_subday_type,freq_subday_interval,

) AS 'RowNumber'

,freq_type

,freq_subday_type

,freq_relative_interval

,active_start_time

--and schedule_id > 12 --- replication ones..Best we leave these as is ...

SELECT *

(

SELECT ROW_NUMBER() OVER(order by rownumber) AS 'GrpNumber'

,freq_relative_interval,freq_recurrence_factor,active_start_time

) AS Keeps

,freq_relative_interval,freq_recurrence_factor,active_start_time)

UNION

,Moves.schedule_id

,Keeps.freq_relative_interval,Keeps.freq_recurrence_factor,Keeps.active_start_time

(

SELECT ROW_NUMBER() OVER(order by rownumber) AS 'GrpNumber'

,freq_relative_interval,freq_recurrence_factor,active_start_time

) AS Keeps

,freq_relative_interval,freq_recurrence_factor,active_start_time)

INNER JOIN

(

SELECT ROW_NUMBER() OVER(order by rownumber) AS 'GrpNumber'

,freq_relative_interval,freq_recurrence_factor,active_start_time

) AS Moves

,freq_relative_interval,freq_recurrence_factor,active_start_time)

AND Keeps.freq_interval = Moves.freq_interval

AND Keeps.freq_subday_interval = Moves.freq_subday_interval

AND Keeps.active_start_time = Moves.active_start_time

,CTE1.schedule_id,CTE1.GrpNumber,CTE1.RowNumber

,CAST(dbo.sysschedules.active_start_time / 10000 AS VARCHAR(10))

,dbo.udf_schedule_description

(

,dbo.sysschedules.freq_interval

,dbo.sysschedules.freq_subday_interval

,dbo.sysschedules.freq_recurrence_factor

,dbo.sysschedules.active_end_date

,dbo.sysschedules.active_end_time

,dbo.sysjobs.enabled

,dbo.sysschedules.freq_interval

,dbo.sysschedules.freq_subday_interval

,dbo.sysschedules.freq_recurrence_factor

,dbo.sysschedules.active_end_date

,dbo.sysschedules.active_end_time

FROM dbo.sysjobs

INNER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id

INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

RIGHT OUTER JOIN #Schedules_CTE CTE1 ON dbo.sysjobschedules.schedule_id = CTE1.schedule_id

WHERE CTE1.RowNumber = 1

+ (Select TOP 1 CONVERT(VARCHAR(20),CTE3.schedule_id)) AS Instruction

,dbo.sysjobs.name

+ ':'

,dbo.udf_schedule_description

(

,dbo.sysschedules.freq_interval

,dbo.sysschedules.freq_subday_interval

,dbo.sysschedules.freq_recurrence_factor

,dbo.sysschedules.active_end_date

,dbo.sysschedules.active_end_time

,dbo.sysjobs.enabled

,dbo.sysschedules.freq_interval

,dbo.sysschedules.freq_subday_interval

,dbo.sysschedules.freq_recurrence_factor

,dbo.sysschedules.active_end_date

,dbo.sysschedules.active_end_time

FROM dbo.sysjobs

INNER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id

INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

INNER JOIN #Schedules_CTE CTE2 ON dbo.sysjobschedules.schedule_id = CTE2.schedule_id

right outer JOIN #Schedules_CTE CTE3 ON CTE2.GrpNumber = CTE3.GrpNumber AND CTE3.RowNumber = 1

WHERE CTE2.RowNumber > 1

order by 3 ASC, 4 ASC, 6 ASC

DROP table #Schedules_CTE