The SQLTeam.com Weblogs

Keep track of all your jobs schedules

Peter Larsson — Fri, 10 Oct 2008 15:07:08 GMT

This is a piece of code I use to create a resultset from and display in Outlook calendar.

CREATE PROCEDURE dbo.uspGetScheduleTimes

(

@startDate DATETIME,

@endDate DATETIME

)

SET NOCOUNT ON

-- Create a tally table. If you already have one of your own please use that instead.

CREATE TABLE #tallyNumbers

(

num SMALLINT PRIMARY KEY CLUSTERED

)

DECLARE @index SMALLINT

SET @index = 1

WHILE @index <= 8640

BEGIN

INSERT #tallyNumbers

(

num

)

VALUES (

@index

)

SET @index = @index + 1

END

-- Create a staging table for jobschedules

CREATE TABLE #jobSchedules

(

rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

serverName SYSNAME NOT NULL,

jobName SYSNAME NOT NULL,

jobDescription NVARCHAR(512) NOT NULL,

scheduleName SYSNAME NOT NULL,

scheduleID INT NOT NULL,

categoryName SYSNAME NOT NULL,

freq_type INT NOT NULL,

freq_interval INT NOT NULL,

freq_subday_type INT NOT NULL,

freq_subday_interval INT NOT NULL,

freq_relative_interval INT NOT NULL,

freq_recurrence_factor INT NOT NULL,

startDate DATETIME NOT NULL,

startTime DATETIME NOT NULL,

endDate DATETIME NOT NULL,

endTime DATETIME NOT NULL,

jobEnabled INT NOT NULL,

scheduleEnabled INT NOT NULL

)

-- Popoulate the staging table for JobSchedules with SQL Server 2000

INSERT #jobSchedules

(

serverName,

jobName,

jobDescription,

scheduleName,

scheduleID,

categoryName,

freq_type,

freq_interval,

freq_subday_type,

freq_subday_interval,

freq_relative_interval,

freq_recurrence_factor,

startDate,

startTime,

endDate,

endTime,

jobEnabled,

scheduleEnabled

)

SELECT sj.originating_server,

sj.name,

COALESCE(sj.description, ''),

sjs.name,

sjs.schedule_id,

sc.name,

sjs.freq_type,

sjs.freq_interval,

sjs.freq_subday_type,

sjs.freq_subday_interval,

sjs.freq_relative_interval,

sjs.freq_recurrence_factor,

COALESCE(STR(sjs.active_start_date, 8), CONVERT(CHAR(8), GETDATE(), 112)),

STUFF(STUFF(REPLACE(STR(sjs.active_start_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':'),

STR(sjs.active_end_date, 8),

STUFF(STUFF(REPLACE(STR(sjs.active_end_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':'),

sj.enabled,

sjs.enabled

FROM msdb..sysjobschedules AS sjs

INNER JOIN msdb..sysjobs AS sj ON sj.job_id = sjs.job_id

INNER JOIN msdb..syscategories AS sc ON sc.category_id = sj.category_id

WHERE sjs.freq_type IN (1, 4, 8, 16, 32)

ORDER BY sj.originating_server,

sj.name,

sjs.name

-- Popoulate the staging table for JobSchedules with SQL Server 2005 and SQL Server 2008

INSERT #JobSchedules

(

serverName,

jobName,

jobDescription,

scheduleName,

scheduleID,

categoryName,

freq_type,

freq_interval,

freq_subday_type,

freq_subday_interval,

freq_relative_interval,

freq_recurrence_factor,

startDate,

startTime,

endDate,

endTime,

jobEnabled,

scheduleEnabled

)

SELECT srv.srvname,

sj.name,

COALESCE(sj.description, ''),

ss.name,

ss.schedule_id,

sc.name,

ss.freq_type,

ss.freq_interval,

ss.freq_subday_type,

ss.freq_subday_interval,

ss.freq_relative_interval,

ss.freq_recurrence_factor,

COALESCE(STR(ss.active_start_date, 8), CONVERT(CHAR(8), GETDATE(), 112)),

STUFF(STUFF(REPLACE(STR(ss.active_start_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':'),

STR(ss.active_end_date, 8),

STUFF(STUFF(REPLACE(STR(ss.active_end_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':'),

sj.enabled,

ss.enabled

FROM msdb..sysschedules AS ss

INNER JOIN msdb..sysjobschedules AS sjs ON sjs.schedule_id = ss.schedule_id

INNER JOIN msdb..sysjobs AS sj ON sj.job_id = sjs.job_id

INNER JOIN sys.sysservers AS srv ON srv.srvid = sj.originating_server_id

INNER JOIN msdb..syscategories AS sc ON sc.category_id = sj.category_id

WHERE ss.freq_type IN(1, 4, 8, 16, 32)

ORDER BY srv.srvname,

sj.name,

ss.name

-- Deal with first, second, third, fourth and last occurence

DECLARE @tempStart DATETIME,

@tempEnd DATETIME

SELECT @tempStart = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @startDate), '19000101'),

@TempEnd = DATEADD(MONTH, DATEDIFF(MONTH, '18991231', @endDate), '18991231')

CREATE TABLE #dayInformation

(

infoDate DATETIME PRIMARY KEY CLUSTERED,

weekdayName VARCHAR(9) NOT NULL,

statusCode INT NOT NULL,

lastDay TINYINT DEFAULT 0

)

WHILE @tempStart <= @tempEnd

BEGIN

INSERT #dayInformation

(

infoDate,

weekdayName,

statusCode

)

SELECT @tempStart,

DATENAME(WEEKDAY, @tempStart),

CASE

WHEN DATEPART(DAY, @tempStart) BETWEEN 1 AND 7 THEN 1

WHEN DATEPART(DAY, @tempStart) BETWEEN 8 AND 14 THEN 2

WHEN DATEPART(DAY, @tempStart) BETWEEN 15 AND 21 THEN 4

WHEN DATEPART(DAY, @tempStart) BETWEEN 22 AND 28 THEN 8

ELSE 0

END

SET @tempStart = DATEADD(DAY, 1, @tempStart)

END

UPDATE di

SET di.statusCode = di.statusCode + 16

FROM #dayInformation AS di

INNER JOIN (

SELECT DATEDIFF(MONTH, '19000101', infoDate) AS theMonth,

DATEPART(DAY, MAX(infoDate)) - 6 AS theDay

FROM #dayInformation

GROUP BY DATEDIFF(MONTH, '19000101', infoDate)

) AS x ON x.theMonth = DATEDIFF(MONTH, '19000101', di.infoDate)

WHERE DATEPART(DAY, di.infoDate) >= x.theDay

UPDATE di

SET di.lastDay = 16

FROM #dayInformation AS di

INNER JOIN (

SELECT DATEDIFF(MONTH, '19000101', infoDate) AS theMonth,

MAX(infoDate) AS theDay

FROM #dayInformation

GROUP BY DATEDIFF(MONTH, '19000101', infoDate)

) AS x ON x.theMonth = DATEDIFF(MONTH, '19000101', di.infoDate)

WHERE di.infoDate = x.theDay

UPDATE #dayInformation

SET lastDay = DATEPART(DAY, infoDate)

WHERE DATEPART(DAY, infoDate) BETWEEN 1 AND 4

-- Stage all individual schedule times

CREATE TABLE #scheduleTimes

(

rowID INT NOT NULL,

infoDate DATETIME NOT NULL,

startTime DATETIME NOT NULL,

endTime DATETIME NOT NULL,

waitSeconds INT DEFAULT 0

)

CREATE CLUSTERED INDEX IX_rowID ON #scheduleTimes(rowID)

-- Insert one time only schedules

INSERT #scheduleTimes

(

rowID,

infoDate,

startTime,

endTime

)

SELECT rowID,

startDate,

startTime,

endTime

FROM #jobSchedules

WHERE freq_type = 1

AND startDate >= @StartDate

AND startDate <= @EndDate

-- Insert daily schedules

INSERT #scheduleTimes

(

rowID,

infoDate,

startTime,

endTime,

waitSeconds

)

SELECT js.rowID,

di.infoDate,

js.startTime,

js.endTime,

CASE js.freq_subday_type

WHEN 1 THEN 0

WHEN 2 THEN js.freq_subday_interval

WHEN 4 THEN 60 * js.freq_subday_interval

WHEN 8 THEN 3600 * js.freq_subday_interval

END

FROM #jobSchedules AS js

INNER JOIN #dayInformation AS di ON di.infoDate >= @startDate

AND di.infoDate <= @endDate

WHERE js.freq_type = 4

AND DATEDIFF(DAY, js.startDate, di.infoDate) % js.freq_interval = 0

-- Insert weekly schedules

INSERT #scheduleTimes

(

rowID,

infoDate,

startTime,

endTime,

waitSeconds

)

SELECT js.rowID,

di.infoDate,

js.startTime,

js.endTime,

CASE js.freq_subday_type

WHEN 1 THEN 0

WHEN 2 THEN js.freq_subday_interval

WHEN 4 THEN 60 * js.freq_subday_interval

WHEN 8 THEN 3600 * js.freq_subday_interval

END

FROM #jobSchedules AS js

INNER JOIN #dayInformation AS di ON di.infoDate >= @startDate

AND di.infoDate <= @endDate

WHERE js.freq_type = 8

AND 1 = CASE

WHEN js.freq_interval & 1 = 1 AND di.weekdayName = 'Sunday' THEN 1

WHEN js.freq_interval & 2 = 2 AND di.weekdayName = 'Monday' THEN 1

WHEN js.freq_interval & 4 = 4 AND di.weekdayName = 'Tuesday' THEN 1

WHEN js.freq_interval & 8 = 8 AND di.weekdayName = 'Wednesday' THEN 1

WHEN js.freq_interval & 16 = 16 AND di.weekdayName = 'Thursday' THEN 1

WHEN js.freq_interval & 32 = 32 AND di.weekdayName = 'Friday' THEN 1

WHEN js.freq_interval & 64 = 64 AND di.weekdayName = 'Saturday' THEN 1

ELSE 0

END

AND(DATEDIFF(DAY, js.startDate, di.infoDate) / 7) % js.freq_recurrence_factor = 0

-- Insert monthly schedules

INSERT #scheduleTimes

(

rowID,

infoDate,

startTime,

endTime,

waitSeconds

)

SELECT js.rowID,

di.infoDate,

js.startTime,

js.endTime,

CASE js.freq_subday_type

WHEN 1 THEN 0

WHEN 2 THEN js.freq_subday_interval

WHEN 4 THEN 60 * js.freq_subday_interval

WHEN 8 THEN 3600 * js.freq_subday_interval

END

FROM #jobSchedules AS js

INNER JOIN #dayInformation AS di ON di.infoDate >= @startDate

AND di.infoDate <= @endDate

WHERE js.freq_type = 16

AND DATEPART(DAY, di.infoDate) = js.freq_interval

AND DATEDIFF(MONTH, js.startDate, di.infoDate) % js.freq_recurrence_factor = 0

-- Insert monthly relative schedules

INSERT #scheduleTimes

(

rowID,

infoDate,

startTime,

endTime,

waitSeconds

)

SELECT js.rowID,

di.infoDate,

js.startTime,

js.endTime,

CASE js.freq_subday_type

WHEN 1 THEN 0

WHEN 2 THEN js.freq_subday_interval

WHEN 4 THEN 60 * js.freq_subday_interval

WHEN 8 THEN 3600 * js.freq_subday_interval

END

FROM #jobSchedules AS js

INNER JOIN #dayInformation AS di ON di.infoDate >= @startDate

AND di.infoDate <= @endDate

WHERE js.freq_type = 32

AND 1 = CASE

WHEN js.freq_interval = 1 AND di.weekdayName = 'Sunday' THEN 1

WHEN js.freq_interval = 2 AND di.weekdayName = 'Monday' THEN 1

WHEN js.freq_interval = 3 AND di.weekdayName = 'Tuesday' THEN 1

WHEN js.freq_interval = 4 AND di.weekdayName = 'Wednesday' THEN 1

WHEN js.freq_interval = 5 AND di.weekdayName = 'Thursday' THEN 1

WHEN js.freq_interval = 6 AND di.weekdayName = 'Friday' THEN 1

WHEN js.freq_interval = 7 AND di.weekdayName = 'Saturday' THEN 1

WHEN js.freq_interval = 8 AND js.freq_relative_interval = di.lastDay THEN 1

WHEN js.freq_interval = 9 AND di.weekdayName NOT IN('Sunday', 'Saturday') THEN 1

WHEN js.freq_interval = 10 AND di.weekdayName IN('Sunday', 'Saturday') THEN 1

ELSE 0

END

AND di.statusCode & js.freq_relative_interval = js.freq_relative_interval

AND DATEDIFF(MONTH, js.startDate, di.infoDate) % js.freq_recurrence_factor = 0

-- Get the daily recurring schedule times

INSERT #scheduleTimes

(

rowID,

infoDate,

startTime,

endTime,

waitSeconds

)

SELECT st.rowID,

st.infoDate,

DATEADD(SECOND, tn.num * st.waitSeconds, st.startTime),

st.endTime,

st.waitSeconds

FROM #scheduleTimes AS st

CROSS JOIN #tallyNumbers AS tn

WHERE tn.num * st.waitSeconds <= DATEDIFF(SECOND, st.startTime, st.endTime)

AND st.waitSeconds > 0

-- Present the result

SELECT js.scheduleID,

js.serverName,

js.jobName,

js.jobDescription,

js.scheduleName,

js.categoryName,

st.infoDate,

st.startTime,

st.endTime,

js.jobEnabled,

js.scheduleEnabled

FROM #scheduleTimes AS st

INNER JOIN #jobSchedules AS js ON js.rowID = st.rowID

-- Clean up

DROP TABLE #jobSchedules,

#dayInformation,

#scheduleTimes,

#tallyNumbers

Code Cleanup at Philly Code Camp 2008.3

Travis Laborde — Fri, 10 Oct 2008 08:13:53 GMT

I'll be giving a talk tomorrow at the Philly.net Code Camp!

In this talk we will look at code that works but could be improved.

We will discuss why it needs improvement and work through several examples of doing exactly that using patterns, practices, and tools along the way.

An emphasis will be placed on the Single Responsibility Principle, Dependency Injection, Inversion of Control, and the Service Locator Pattern.

Capturing the text of a prepared SQL statement run with sp_execute

Bill Graziano — Fri, 10 Oct 2008 06:41:04 GMT

Many times when running a trace against SQL Server you'll see RPC:Completed events that look like this:

exec sp_execute 66,4,4

Unfortunately this statement was the one causing me a performance problem and I had no idea what it was doing. The 66 is the number of the statement to execute and the two 4's are the parameters passed to the statement. The 66 is specific to a connection -- meaning the SQL is prepared for only that session. I talked about the sequence of statements that are part of preparing and un-preparing SQL in Examining SQL Server Trace Files.

Somewhere earlier there was an sp_prepare statement issued for statement number 66. Unfortunately I didn't start my trace until it was long gone. I posted this question on the newsgroup and got an answer back from Erland that worked. I captured both the CacheHit event and the RPC:Completed event. The CacheHit event right before the sp_execute had the text of the query in it.

I found it helpful to filter my trace for just a single SPID. Otherwise there were just too many events all jumbled together.

The Meaning of the Microsoft MVP Award

Derek Comingore — Fri, 10 Oct 2008 00:55:08 GMT

I have been working with Microsoft SQL Server for close to ten years now if you include my college days (yes, my college used SQL Server 7.0 for learning RDBMS purposes). I have literally built my entire career around Microsoft SQL Server and have used the product for development, administration, and now for building DW/BI solutions. Around the 2002 timeframe I became aware of this mysterious elite group of folks called Microsoft MVPs. I perceived that MVPs were the best of the best and thus by winning the award you would prove you were one of the best. On October 1st, 2008 I was informed of being awarded a SQL Server MVP and within just eight days of winning I believe I have grasped the award’s true meaning.

MVPs are technical community leaders that are highly proficient in their area of expertise and most importantly continuously give back to their respective communities. The Microsoft MVP tagline is ‘Independent Experts. Real World Answers’. To me, it is the giving dimension of the award that is most important. I believe that if someone was good (but not great) yet he or she continuously shared the knowledge they did have with their respective communities they would have a much better chance at winning the award as opposed to someone who is very strong technically yet never helps others in the community. Like most things in life, only once you’ve been there do you truly understand a context’s meaning. Finally, I give thanks to the Microsoft MVP program and my MVP lead for the award.

Derek Comingore

SQL Server MVP

Excerpt from The Compression Session

Peter Larsson — Thu, 09 Oct 2008 06:39:43 GMT

This tuesday I had the opportunity to meet Kalen Delaney and hear her talk about the new compression algorithms in SQL Server 2008.
For those of you that never have met Kalen, I can tell she is a sweet lady, knowledgable and interesting to listen to.

With SQL Server 2005 SP2, the new VARDECIMAL datatype arrived, with a few stored procedures to calculate eventual savings. This turned out to be a subset for the ROW and PAGE compressions available in SQL Server 2008, Enterprise Edition. You can have ROW compression only if you want, but if you choose PAGE compression you also get ROW compression.

Example: See this PAGE records (sizes are shown without the overhead for storing varchar data).

           Col1      Col2
Record1    ABCD      CHOCO   4+5 bytes = 9 bytes
Record2    ABCRD     CHALK   5+5 bytes = 10 bytes
Record3    ABCDE     CHORD   5+5 bytes = 10 bytes
Record4    DE        CHOOSE 2+6 bytes = 8 bytes
Record5    QWERTY    DE      5+2 bytes = 7 bytes
                                       = 45 bytes

What happens first is that every page gets a new type of record, an anchor record. The anchor record is the longest most frequent data for every column.

Col1 Col2
AncRec ABCDE CHO 5+3 bytes = 8 bytes

Record1   4         3CO      1+3 bytes = 4 bytes
Record2   3RD       2ALK     3+4 bytes = 7 bytes
Record3   5         3RD      1+3 bytes = 4 bytes
Record4   0DE       3OSE     3+4 bytes = 7 bytes
Record5   0QWERTY   0DE      7+3 bytes = 10 bytes
                                       = 40 bytes

As you can see, the ROW compression step looks at the complete page and looks for the longest common data and puts that in an anchor record. Then the first byte of every record and columns get a value for how many characters of the prefix that match. With a better example I could have shown that the savings could be as much as 80 or 90 percent.

What happens next? SQL Server 2008 then tries to apply a dictionary algorithm. It looks at all the new values for the records in the page and creates a dictionary (up to about 300 items for now).

Dict D1 = 0DE, D2 = 3RD

Col1 Col2
AncRec ABCDE CHO 5+3 bytes = 8 bytes

Record1   4         3CO      1+3 bytes = 4 bytes
Record2   D2        2ALK     1+4 bytes = 5 bytes
Record3   5         D2       1+1 bytes = 2 bytes
Record4   D1        3OSE     1+4 bytes = 5 bytes
Record5   0QWERTY   D1       7+1 bytes = 8 bytes
                                       = 32 bytes

As you can see, the space has gone from 45 bytes down to 32 bytes, almost 25% savings! The tradeoff, of course, is a small overhead of CPU usage to compress and decompress the data on disk. In memory the pages are always decompressed.
There is also a threshold. Pages are always compressed but later evaluated if the savings are big enough to stay compressed. If the savings are not big enough, the page stays uncompressed, even if you have choosen compression.
And the compression only occurs when a page is full. When a pagesplit occurs, the new page inherits same anchor record.

This is the essentials of Karen's Compression Session, a new chapter in her next book!

The new BACKUP compressions later applies a kind of ZIP compression which also deals with suffixes, so the savings on backups would be even more!