September 2012 – Mitch Wheat

Find Max Value of Multiple Columns

September 16, 2012 by admin

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

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/white-space: pre;/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

SQL Server Agent Job History Statistics

September 1, 2012 by admin

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?

September 1, 2012 by admin

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:

    "(ProjectDir)\FxCop(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