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

Are you looking for some R2 Training Resources - then this would most likely keep you busy for a while digesting all the content

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=fffaad6a-0153-4d41-b289-a3ed1d637c0d

SQL Server 2008 R2 Update for Developers Training Kit (April 2010 Update) it Contains the following

• Presentations (22)
• Demos (29)
• Hands-on Labs (18)
• Videos (35) 

Happy Training

What have I been up to this week, Well that would be telling now.

What I can say, is that I was fortunate enough to be able to attend the User groups meetings being held in London this week, but I was not content with just the 1, I also had a 2nd helping of UG fun the very next evening

With the 1st meeting of the week on Wed (Victoria) having a DBA / Dev focus, it began with a Round table & nuggets session (not the chicken MC variety but a few tasty morsels tips on SQL none the less), this followed by yours truly with a session on Service Broker (more on that shortly), then a vendor presentation from Phil Parkinson BakBone Software, refreshments break and then finally with MVP Chris Testa O'Neill on Monitoring and Controlling Resources in SQL Server (very nice)

This was followed on Thursday  (Thames Riverside) with a great BI focus - Implementing common business calcs using DAX in PowerPivot (MVP Chris Webb).. Boy I'm going to have fun learning how to use DAX & PowerPivot

So to pick up from earlier, as I mentioned I gave my session on Service Broker (it was primarily an Intro session, with a small focus on what the challenges are likely to be encountered etc..)

As it happens @ the 11th hour I was laptop less (trust me that's not the best of starts if you are due to present a UG session that week) 

One thing I did do, was then to rule out my attempt to run any demos using a borrowed laptop, so that meant I was trying to now fill those "Demo segments" with even more talking and a couple of hastily added slides

Let's just say it was not as smooth as I would have liked...(today's lesson learnt - Don't try to present with out @ least some demo's)

You will find attached the Presentation slides (zip file) I used that evening - I will in due course add the "Missing Demo's" in a short video in the very near future.

I'm sure you are now aware of the SQL UserGroup events (both in London) on Wednesday 19th & Thrusday 20th evenings, If you have never been to one of the events before then I would highly reconmend attending one or both of them.

Covering a wide range of subjects these meetings are an invaluable way to gain insights into various features from SQL experts (both presenters and attendees alike) frequently you will learn new insights and gain different perspectives on how to use those features & technologies in the real-world, not to mention a great way to network with your peers.

I will be doing a session on the Wednesday event on "Service Broker" - So if you would like to know more then simply register and I will see you later this week, for a complete listing of all the other topics and to register for these events 

http://sqlserverfaq.com/ (See Coming Event) , also don't forget to spend some time reviewing the recent blog links and other content - We have a new section "Cuppa Corner Screencasts"

To top this all off @ this weeks meetings we will be providing details for the chance to win a FREE place on the "Must See" 1-day seminar with Kimberly Tripp & Paul S. Randal on 17th June (London - Heathrow).

http://sqlblogcasts.com/blogs/testas/archive/2010/05/16/win-a-place-at-a-sql-server-masterclass-with-kimberly-tripp-and-paul-randal.aspx

For those of you who tweet, you can follow us using #sqlfaq

SQL Server User Group Session in Reading this Wednesday  (21st April 2010 6pm - 10pm)

Along with Tony Rogerson MVP, I {Neil Hambly} will be presenting @ the forthcoming User Group meeting @ Microsoft Campus, Reading

Tony will be presenting the session he gave @ SQLBits VI on Thinking Sets, Normalisation, Surrogate Keys, Referential Integrity
This is very insightful and was a very popular session.

I will be continuing my recent presentation on Indexed views @ London UG, this time i will be doing a talk-tutorial on 2 subjects in the one session, these are subjects I have been asked a lot about recently - Service Broker (An introduction to the technology) and Indexing (Types, Selectivity, Usage Analysis )

Along with round table; group discussion; refreshments and networking it promises to be a great evening - Hope to see you there

Remember you need to Register for the event, use the following link for the full agenda and to register or visit http://sqlserverfaq.com/

http://sqlserverfaq.com/events/213/Service-Broker-Intro-Guidance-Indexing-Selection-Usage-Fragmentation-etc-Normalisation-Surrogate-Keys-Locking-considerations.aspx

Recent presentation I gave @ London UG "Using Indexed Views and Computed Columns for Performance"

http://sqlblogcasts.com/blogs/neilhambly/archive/2010/03/29/ug-presentation-quot-using-indexed-views-and-computed-columns-for-performance-quot-support.aspx

Now I admit I've had mixed feelings on the certification subject previously and of a result I've not looked @ going down the MS Certification route, however with my previous experience this really hasn't hindered my progress any (Thankfully).

However as I now have a different perspective for a number of varying reasons of which I will not bore you with the details.

I will be undertaking some exams (6 of them) for accredition so right now I'm just formulating my study plans,  with my overall goal to complete during these coming months, so armed with my previous knowledge and experience's I believe this is something I can achieve with some concerted effort, Improving my SQL knowledge has always been something I have enjoyed, getting certified is just one way in which I can validate my knoweldge for me personally and others.

So Here I Come:  MCITP 2008: Database Administrator; Database Developer; Business Intelligence Developer

Wish me luck and I will blog about my experiences on my certification quest during the the coming months.

It is being held @ "The Bull" Pub @ Westfield centre (only a short walk from the venue of t the “SQL Server 2008 R2”  launch conference), commencing shortly after the conference ends.

Cheers Neil

Attached Zip file ( Word documents & PowerPoint Presentation) these provide some further explantions of the UG Presentation.

I always welcome feedback, if you have any on this material or viewed my presentation please feel free to let me know your views

Having tried to make a fun play on words to illustrate that for Standard Editions of SQL Server 2005/2008 since the releases of these Cumulative Updates:

 SQL 2005 SP3 & CU4 / SQL 2008 SP1 & CU2 we can make real use of AWE!

Since (Mid 2009) when these CU’s where released, the ability to make use of required privilege “locking-pages-in-memory” which previously was only available in Enterprise Edition, allowing us to make use of those AWE APIs for resolving working set trim issues that resulted in non-optimum performance.

So naturally this raises a few questions That I would like to find some answers for:

What impact +/- will this have on my databases performance? (Clearly that’s not an easy question to answer succinctly or with any great accuracy), I will need to find some discreet questions that will allow me to determine its impact +/- on the databases performance with this functionality.

Some questions I have are:

·         What measure(s) would best indicate the impact?

·         What Trace flag(s) are required?

I’m going to try to answer those and others during the next few days/weeks and will post my findings on this as I find them, It’s not that new (it’s been around for ~9 months now), but none the less it should be able to provide some decent improvements if its implemented correctly.

I'm been running some queries (below) to help me identify when I have had time-sensitive performance issues around Memory/CPU, I didn't want to load up additional overhead to the system (unless absolutely neccessary) using traces or profiler  - naturally we have various methods to do this Perfmon counters, DBCC, DMVs etc..

One quick way I like is to run a few DMV queries (normally back in seconds) to help me find those RECENT specific time periods when the system has been substantially changed in some way using, this is using the DMV dm_os_ring_buffers

This one helps me identify when I'm expericing Timeout Errors (1222).. modiy code to look for other error as highlight below

Sample Output

Finally one to provide some understanding of the level of memory state changes that are ocuring

Hello

This is a quick follow-up blog to the Presention I gave last night @ the London UG Meeting ( 17th March 2010 )

It was a great evening and we had a big full house (over 120 Registered for this event), due to time constraints we had I was unable to spend enough time on this topic to really give it justice or any the myriad of questions that arose form the session, I will be gathering all my material and putting a comprehensive BLOG entry on this topic in the next couple of days..

In the meantime here is the slides from last night if you wanted to again review it or if you where not @ the meeting

If you wish to contact me then please feel free to send me emails @ Neil.Hambly@hotmail.co.uk

Finally  - a quick thanks to Tony Rogerson for allowing me to be a Presenter last night (so we know who we can blame !)  and all the other presenters for thier support

Watch this space Folks more to follow soon.. 

One of the recent technical challenges that I was required to resolve was an issue with several Publication’s (Transactional) failing shortly after new triggers where added on the subscriber tables, the problem was not occurring on other identical publication’s which do not have the triggers applied. (Note: the triggers where added to perform distributed transactions - Inserts / Updates /Deletes).

Several new snapshots and profiler trace sessions later it was evident that the problem was not a one-off and was re-producible {some progress then – but issue was still unresolved}.

So my next step was to come up with a alternative solution that would resolve the issue quickly (within 4 hours!), the solution was one I had thought previously might be a better solution than the original triggers, so time to test out my theory.

I try will explain the solution clearly and the benefit’s (IMHO), but first I had also identified the following additional problem during profiler sessions

An update on the publisher database where created as replication command “Pairs”, a Delete then Insert command, this of course has performance implications creating fragmentation and blocking actions, higher database loads @ the subscriber’s etc..

Due to the large number of updates (> 10,000 / Per Hour) it was critical that the solution would be efficient as possibly (of course)

Firstly I addressed the command pair issue, by implementing the trace flag -8207 run the following @ the publisher DBCC TRACEFLAG(8207, -1) & –T8207 on the database startup properties

See the following link for additional details on this trace flag http://support.microsoft.com/kb/302341

Now the updates @ the Publisher are ‘singleton’ updates I can implement my proposed solution, The revised trigger solution is basically quite simple (best solutions are usually the simplest).

I took the code from within the 3 different triggers and modified these in the 3 replication stored procedures sp_MSins_xxx, sp_MSupd_xxx & sp_MSdel_xxx @ the subscriber

I have posted some sample code below to illustrate the replacement trigger action

Note: Modified from original and missing most of the parameters for readability

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

ALTER   procedure "sp_MSins_tablename" @c1 int,@c2 int … @c142 datetime

AS

BEGIN

insert into "dbo"."tablename"( "lID", "lTitleID"…"dtUpdated" )

values ( @c1, @c2, …@c142 )

/* Replace Trigger Operation  */

SET XACT_ABORT ON

                        DECLARE @ID INT

                        SELECT @ID = @c1 -- ID Value  -- Note this should be  = @pkc1 for the Inserts / Deletes versions

                        IF @ID IS NOT NULL

                        BEGIN

                                                IF NOT EXISTS (SELECT ID FROM DB1.dbo.tx_Hold WHERE DBKEY1 = @ID AND STATUS = 1)

                                                BEGIN DISTRIBUTED TRANSACTION

                                                                        INSERT INTO DB1.dbo.tx_Hold

 (DBKEY1, DEALTYPEID, PARTIALU, TX, TXTIMESTAMP, STATUS, RESULT1)

                                                                        VALUES

                                                                                                (@ID, 1, 2, 'I', GETDATE(),1,'') 

IF @@rowcount = 0

                                                                        BEGIN

                                                                        INSERT INTO DB1.dbo.tx_Error

                                                                                                (DBKEY1, DEALTYPEID, PARTIALU, TX, TXTIMESTAMP, STATUS, RESULT1)

                                                                        VALUES        

(@ID, 1, 2, 'I', GETDATE(),1,'')

                                                                        END

                                                COMMIT TRAN

                        END

SET XACT_ABORT OFF

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Now I have a revised ‘Working’ solution, I needed to determine it’s pro’s & con’s

Pro’s

Replication now works without the previous failures (Goal # 1 achieved) and replication stored performance is not adversely affected with the added code (Goal #2), Mgmt & Client now happy (Bonus !)

Con’s

No longer able to run commands directly on table to perform the previous the trigger actions, this is being handled by running separate statements to perform the required action’s & modifying other stored procedures to include the necessary code where required.

Summary

Using tools like profiler and your knowledge of SQL Server workings is key to understanding the problem and devising alternative solutions, reviewing BOL and other sources (forum’s, colleagues, searches) until you understand the problem

Then ‘Write’ it down and if needed brainstorm until some idea is formulated.

Finally identify the likeliest solution and perform your TESTING until you are  happy you have a potential workable solution.. that will resolve your identified problem's

Implement your solution.. RE-TEST and confirm you have resolved the problem and other's are in agreement. then you carry on attacking your other priorities and get home to down a few beers and smile smugly.

Lastly don't forget  to document your success or failures (even these are valuable in finding the solution) and keep this in a searchable folder as you never know when you or another colleague will need the info in the future.