Sunday, September 16, 2012

 

Find Max Value of Multiple Columns

Nothing new, but a nice trick to find the maximum value of a number of columns within a row, due to Louis Davidson (via Simon Sabin). It uses the fact that a sub-query can reference columns from the main rowset.

select 
WorkOrderId,
(select max(DateValue)
from (select StartDate as DateValue
union all
select EndDate
union all
select DueDate
union all
select ModifiedDate) as DateCols) as MaxDate,
StartDate,
EndDate,
DueDate,
ModifiedDate
from
Production.WorkOrder




Saturday, September 01, 2012

 

SQL Server Agent Job History Statistics

It’s sometimes useful to know how long on average each SQL Server Agent Job takes and what the variation in running time is. You might want to increase how much Agent job history that SQL Server retains (the defaults are quite low).

-- Agent job history over all runs in retained in history
declare @tmp_sp_help_jobhistory table
(
instance_id int null,
job_id uniqueidentifier null,
job_name sysname null,
step_id int null,
step_name sysname null,
sql_message_id int null,
sql_severity int null,
message nvarchar(4000) null,
run_status int null,
run_date int null,
run_time int null,
run_duration int null,
operator_emailed sysname null,
operator_netsent sysname null,
operator_paged sysname null,
retries_attempted int null,
server sysname null
)

insert into @tmp_sp_help_jobhistory
exec msdb.dbo.sp_help_jobhistory @mode='FULL'

;with cteRuns as
(
SELECT
tshj.job_name AS [JobName],
convert(datetime,
stuff(stuff(cast(tshj.run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' +
stuff(stuff(substring(cast(1000000 + tshj.run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'),
120) AS [RunDate],
( run_duration % 100 + ((run_duration / 100) % 100) * 60 + ((run_duration / 10000) % 100) * 3600 +
((run_duration / 1000000) % 100) * 86400 )
AS [RunDurationSeconds]
FROM @tmp_sp_help_jobhistory as tshj
where step_ID = 0 and run_status = 1
)
, cteAggRuns as
(
SELECT
JobName,
MAX(RunDate) AS LastRunDate,
AVG(RunDurationSeconds) AS [AverageRunDurationSeconds],
Min(RunDurationSeconds) AS [MinDurationAverageSeconds],
Max(RunDurationSeconds) AS [MaxRunDurationSeconds],
COUNT(jobname) as NumRunsInHistory,
sqrt(var(RunDurationSeconds)) AS [stdRunDurationSeconds]
FROM cteRuns
group by JobName

)
select
JobName,
LastRunDate,
AverageRunDurationSeconds,
MinDurationAverageSeconds,
MaxRunDurationSeconds,
NumRunsInHistory,
stdRunDurationSeconds
from cteAggRuns
ORDER BY JobName




Similarly you might want to get this information for just the last 5 runs say (for instance, when you’ve altered jobs):

declare @tmp_sp_help_jobhistory table
(
instance_id int null,
job_id uniqueidentifier null,
job_name sysname null,
step_id int null,
step_name sysname null,
sql_message_id int null,
sql_severity int null,
message nvarchar(4000) null,
run_status int null,
run_date int null,
run_time int null,
run_duration int null,
operator_emailed sysname null,
operator_netsent sysname null,
operator_paged sysname null,
retries_attempted int null,
server sysname null
)

insert into @tmp_sp_help_jobhistory
exec msdb.dbo.sp_help_jobhistory @mode='FULL'

;with cteRuns as
(
SELECT
job_name AS [JobName],
( run_duration % 100 + ((run_duration / 100) % 100) * 60 + ((run_duration / 10000) % 100) * 3600 +
((run_duration / 1000000) % 100) * 86400 )
AS [RunDurationSeconds],
convert(datetime,
stuff(stuff(cast(run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' +
stuff(stuff(substring(cast(1000000 + run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'),
120) AS [RunDate]
FROM @tmp_sp_help_jobhistory
where step_ID = 0 and run_status = 1
)
, cteLast5Runs as
(
SELECT
ROW_NUMBER() over (partition by JobName order by RunDate DESC) as rownum,
JobName,
RunDurationSeconds,
RunDate
FROM cteRuns
)
SELECT
JobName,
AVG(RunDurationSeconds) AS [AverageRunDurationSeconds],
Min(RunDurationSeconds) AS [MinDurationAverageSeconds],
Max(RunDurationSeconds) AS [MaxRunDurationSeconds],
COUNT(jobname) as NumRunsInHistory,
sqrt(var(RunDurationSeconds)) AS [stdRunDurationSeconds]
FROM cteLast5Runs
where rownum between 1 AND 5
group by JobName
ORDER BY JobName



 

How to enable Code Analysis in Visual Studio 2010 Professional?

If you only have the Pro version of VS2010, you can use FxCop Integrator or you can Download FxCop 1.36 and add following command into the post-build event:

    "$(ProgramFiles)\Microsoft FxCop 1.36\FxCopCmd.exe" /c /p:"$(ProjectDir)\FxCop$(ConfigurationName).FxCop" /consolexsl:"$(ProgramFiles)\Microsoft FxCop 1.36\Xml\VSConsoleOutput.xsl"





as mentioned here: http://stackoverflow.com/questions/2833608/how-to-enable-code-analysis-in-visual-studio-2010-professional


    

Powered by Blogger