You are here: Home > 2009 > June
Archive | June, 2009

Average Daily Job Runtime

by on June 20, 2009

Here’s a query I found useful today– this week we moved many of our production datamart servers to SQL 2K5 SP3 CU4, and today among the course of other issues I wanted to take a look at my job runtimes to see if they might be noticeably slower or faster than prior runs. I often am in a similar situation after deploying significant changes to our codebase.

Since most of my processing runs in SQL agent jobs, looking at average runtime per day is a pretty convenient index of performance. However, the load in processing varies by day of week, so it’s frequently useful to check activity for only a certain day of the week.

This script allows for both. I usually want to tweak the conditions, so I don’t set them in variables at the top, I edit them within the query itself each time:

use msdb;

select
	d.jobname
	,d.servername
	, avgDurationMinutes=avg(d.durationMinutes)
	, daydate=convert(char(10),startdatetime,101)
from (
	select
		jobname=j.name
		,servername=server
		,startdatetime=
			CONVERT (DATETIME, RTRIM(run_date))
			+ (
				run_time * 9
				+ run_time % 10000 * 6
				+ run_time % 100 * 10
			) / 216e4
		, durationMinutes=
				(CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),1,3) AS INT) * 60 * 60
				 + CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),4,2) AS INT) * 60
				 + CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),6,2) AS INT)
				)/60.

		,enddatetime =
		dateadd
			(ss,
				(CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),1,3) AS INT) * 60 * 60
				 + CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),4,2) AS INT) * 60
				 + CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),6,2) AS INT)
				)
			,
			(CONVERT (DATETIME, RTRIM(run_date))
			+ (
				run_time * 9
				+ run_time % 10000 * 6
				+ run_time % 100 * 10
			) / 216e4 )
			)
		, retries_attempted
	from sysjobs j (nolock)
	join sysjobhistory h  on
		h.job_id = j.job_id
		and h.step_id = 0 -- look only at the job outcome step for the total job runtime
	where
		j.name in ('<strong>JobName</strong>')  -- Set the jobname here

) d
where
	datepart(dw,startdatetime)=7 -- Set  your day of week here if desired. 7=Saturday
group by
	d.jobname
	,servername
	,convert(char(10),startdatetime,101)
order by
	d.jobname
	,servername
	,cast(convert(char(10),startdatetime,101)as datetime) desc
Comments { 0 }

Replication Undelivered Commands: Monitoring and Reporting

by on June 7, 2009

This post is to share a script I’ve been working on periodically over the last couple of months to monitor and report on replication latency at the distributor. I use this in monitoring transactional replication with a stand-alone distributor.

The basic purpose for the script is:

  • Check the number of undelivered commands for all active publications
  • Log the data found to a table (which is collected on a centralized server so the history can be displayed graphically in Reporting Services 2008– that code not in this post)
  • When the number of undelivered commands is  higher than allowed, fail the job and send an email with a table highlighting the publications that are behind
  • Accept a parameter of dynamic length to allow specifying publications that should not be monitored as “production” (ie, do not alert in monitoring). Our repl servers are not SQL 2008 yet, so I use an XML wad rather than a TVP. I do love TVP’s for this type of thing though.
  • And be able to accomplish all these tasks from a non-system database.  (No custom monitoring code in system or application dbs!)

So this script shows not only how to check for undelivered commands, but also how to build a quick and easy HTML table to summarize data in emails. I always really like having as much data as possible about the state of the system in an email which is requesting investigation. (For monitoring tie in/SQL job failures are written to the event log which are then picked up.)

Note: there are some email addresses at the bottom of the sproc which I should have parameterized.  I am leaving this way because everyone should read the sproc thoroughly before installing it anywhere ;)

The table for Logging

I pull all results to a centralized server, so this table stays small

Continue Reading →

Comments { 1 }

Backups – Using SQL Agent Tokens to Set the Date, and Why to go MultiFile

by on June 1, 2009

My life is a bit easier since I learned how to use SQL agent tokens. They are particularly nice for setting date and timestamps on backup files. Unfortunately they only work in the context of executing agent jobs.

We had some backups at work which were running quite slowly, and my colleague Gina reminded me of something I once learned, but had somehow forgotten: backups streamed to multiple files are much faster than those written to single files. This is true even when the files are sitting on the same drive.

We’ve switched over the large backups from the existing process, where they wrote a single file to local disk and then robocopied out to a remote location, to the new process where the backup is streamed directly across the network to the backup location using multiple files. The total job runtime is always lower, and in most cases the time running the backup step itself is slower. I am finding that even when the backup process time itself is slightly longer, I perfer this setup because we don’t need to execute batch files stored on the operating system (with its related security worries), nor do we have to worry about allowing memory space for the copy process to run in outside of sql server. (Along with everything else that uses up that memory space.)

Here is a sample of the modified backup commands we’re using— this one is from an 11GB SQL 2008 database where I’m using five files for the compressed backup. The total backup size across the file files is 5.5GB and it completes across the network (within a datacenter) in ~10 minutes.

BACKUP DATABASE MyDB
	TO DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_1of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_2of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_3of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_4of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_5of5.DMP'
WITH COMPRESSION
Comments { 0 }

Management Studio 2008- What I Like

by on June 1, 2009

There’s some pretty cool things about SSMS 2008– I am really pleased that execution plans open graphically by default now, and it’s easy to view the XML on demand. I had gotten pretty fast at saving things as .sqlplan, but it was a bit of a drag.

But my very favorite thing is really simple. I’m not sure if it even made it into the ‘feature lists’ anywhere. It’s this:

  • When you are running a query in a tab, the top of the tab has the note as ‘Executing’ until it completes.

Whomever got in this feature has my sincere and heartfelt thanks. This is incredibly useful to me. I frequently need to run manual commands to verify data to troubleshoot things that shouldn’t be automated, and it’s really nice to be able to move to another tab and periodically glance at the tab header to see when it completes. No more clicking back to  it, or running sp_who2 if I happen to be on the same server.

Comments { 0 }