August 2010 - Posts

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

Posted by NeilHambly | with no comments
HelloFor those who have been paying attention we had a new SQL Server User-Group (Kent) hold its inaugural Meeting in Maidstone @ a Pub "Style & Winch".. Strangely enough on the same day they were shooting some film footage for "Jack the Ripper" this new User-Group is organised by Dave Ballantyne.. Who did a very nice job of it IMHO, so with an excellent Turnout (~ 30) which filled our meeting room to the rafters.

 

Allan Mitchell went up first (I was running "fashionable" late it seems) with his session on StreamInsight (most excellent).. Followed by a short interlude for drink and food refreshment..(ALWAYS a winner in my book)Then it was my turn to End the event with my session on "Replication - Best Practices" .. Opps.. Slight hitch on that, so I substituted with one of my presentations from ~ 6 months ago on "Using Indexed Views & Computed Columns for Performanceā€¯. This seemed to go down well {Phew}

 

I have posted the Presentation slides here and will do a follow-up post on Demos & Q&A as well... the Replication Presentation will be also posted soon (once I have recreated it) and is due for another outing later this year.Also for those going to SQLBIts 7  ... look out for my Session on DAC-PAC (Saturday)

 

Posted by NeilHambly | 1 comment(s)

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 1st 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

 

WITH Schedules_CTE

(RowNumber,schedule_id,freq_type,freq_interval,freq_subday_type,freq_subday_interval,freq_relative_interval,freq_recurrence_factor,active_start_time)

AS

(

SELECT ROW_NUMBER()

OVER(Partition by

freq_type,freq_interval,freq_subday_type,freq_subday_interval

,freq_relative_interval,freq_recurrence_factor,active_start_time

order by

schedule_id,freq_type,freq_interval,freq_subday_type,freq_subday_interval,

freq_relative_interval,freq_recurrence_factor,active_start_time

) AS 'RowNumber'

,schedule_id

,freq_type

,freq_interval

,freq_subday_type

,freq_subday_interval

,freq_relative_interval

,freq_recurrence_factor

,active_start_time

from msdb..sysschedules WHERE freq_type >= 4

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

)

SELECT *

INTO #Schedules_CTE FROM

(

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

,RowNumber,schedule_id,freq_type,freq_interval,freq_subday_type,freq_subday_interval

,freq_relative_interval,freq_recurrence_factor,active_start_time

FROM Schedules_CTE WHERE RowNumber = 1

) AS Keeps

(GrpNumber,RowNumber,schedule_id,freq_type,freq_interval,freq_subday_type,freq_subday_interval

,freq_relative_interval,freq_recurrence_factor,active_start_time)

UNION

SELECT Keeps.GrpNumber, Moves.RowNumber

,Moves.schedule_id

,Keeps.freq_type,Keeps.freq_interval,Keeps.freq_subday_type,Keeps.freq_subday_interval

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

FROM

(

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

,RowNumber,schedule_id,freq_type,freq_interval,freq_subday_type,freq_subday_interval

,freq_relative_interval,freq_recurrence_factor,active_start_time

FROM Schedules_CTE WHERE RowNumber = 1

) AS Keeps

(GrpNumber,RowNumber,schedule_id,freq_type,freq_interval,freq_subday_type,freq_subday_interval

,freq_relative_interval,freq_recurrence_factor,active_start_time)

INNER JOIN

(

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

,RowNumber,schedule_id,freq_type,freq_interval,freq_subday_type,freq_subday_interval

,freq_relative_interval,freq_recurrence_factor,active_start_time

FROM Schedules_CTE WHERE RowNumber > 1

) AS Moves

(GrpNumber,RowNumber,schedule_id,freq_type,freq_interval,freq_subday_type,freq_subday_interval

,freq_relative_interval,freq_recurrence_factor,active_start_time)

ON Keeps.freq_type = Moves.freq_type

AND Keeps.freq_interval = Moves.freq_interval

AND Keeps.freq_subday_type = Moves.freq_subday_type

AND Keeps.freq_subday_interval = Moves.freq_subday_interval

AND Keeps.freq_relative_interval = Moves.freq_relative_interval AND Keeps.freq_recurrence_factor = Moves.freq_recurrence_factor

AND Keeps.active_start_time = Moves.active_start_time

----------------------------------------------------------------------------

SELECT 'Keep This' AS Instruction

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

,dbo.sysjobs.name

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

+ ':' + RIGHT('00' + CAST(dbo.sysschedules.active_start_time % 10000 / 100 AS VARCHAR(10)), 2) AS active_start_time

,dbo.udf_schedule_description

(

dbo.sysschedules.freq_type

,dbo.sysschedules.freq_interval

,dbo.sysschedules.freq_subday_type

,dbo.sysschedules.freq_subday_interval

,dbo.sysschedules.freq_relative_interval

,dbo.sysschedules.freq_recurrence_factor

,dbo.sysschedules.active_start_date

,dbo.sysschedules.active_end_date

,dbo.sysschedules.active_start_time

,dbo.sysschedules.active_end_time

) AS ScheduleDscr

,dbo.sysjobs.enabled

,dbo.sysschedules.freq_type

,dbo.sysschedules.freq_interval

,dbo.sysschedules.freq_subday_type

,dbo.sysschedules.freq_subday_interval

,dbo.sysschedules.freq_relative_interval

,dbo.sysschedules.freq_recurrence_factor

,dbo.sysschedules.active_start_date

,dbo.sysschedules.active_end_date

,dbo.sysschedules.active_start_time

,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

UNION

SELECT 'Moving This to Schedule_id: '

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

,CTE2.schedule_id,CTE2.GrpNumber,CTE2.RowNumber

,dbo.sysjobs.name

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

+ ':'

+ RIGHT('00' + CAST(dbo.sysschedules.active_start_time % 10000 / 100 AS VARCHAR(10)), 2) AS active_start_time

,dbo.udf_schedule_description

(

dbo.sysschedules.freq_type

,dbo.sysschedules.freq_interval

,dbo.sysschedules.freq_subday_type

,dbo.sysschedules.freq_subday_interval

,dbo.sysschedules.freq_relative_interval

,dbo.sysschedules.freq_recurrence_factor

,dbo.sysschedules.active_start_date

,dbo.sysschedules.active_end_date

,dbo.sysschedules.active_start_time

,dbo.sysschedules.active_end_time

) AS ScheduleDscr

,dbo.sysjobs.enabled

,dbo.sysschedules.freq_type

,dbo.sysschedules.freq_interval

,dbo.sysschedules.freq_subday_type

,dbo.sysschedules.freq_subday_interval

,dbo.sysschedules.freq_relative_interval

,dbo.sysschedules.freq_recurrence_factor

,dbo.sysschedules.active_start_date

,dbo.sysschedules.active_end_date

,dbo.sysschedules.active_start_time

,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

Posted by NeilHambly | with no comments