<div>
The actual processing is from stored proc.. but I put it as code format by substituting the parameter values here to illustrate, any thoughts on making this code efficient to avoid blocks/locks? 
 
&nbsp;~~~~~~~~~~~~~~~~~~~~~~ 

<pre><code class="code-100 nolinks" id="code-20-40428647-1">SET NOCOUNT ON 
 
DECLARE @JobYear varchar(4) 
SET @JobYear = '2004' 
DECLARE @PairedID char(3) 
SET @PairedID = '113' 
 
 
DECLARE @ExtractDt datetime 
SET @ExtractDt = (Select Distinct Max(dbo.PASNote.ExtractDt) 
 From dbo.PASNote 
 Where dbo.PASNote.JobYear = @JobYear) 
 
DECLARE @PPExtractDate datetime 
SET @PPExtractDate = (Select Distinct PPExtractDate 
 From dbo.PASNote 
 Where dbo.PASNote.JobYear = @JobYear 
 And ExtractDt = @ExtractDt) 
 
 Select 
 CAST(@JobYear as char(4)) 
 + '-' 
 + CAST(@JobYear+1 as char(4)) As [JobYear], 
 CAST([PairID] as int) As [FinalizedPair], 
 CAST([BCPERName] as varchar(60)) As [FinalizedBCPER], 
 CAST([UCANName] as varchar(30)) As [FinalizedUCAN], 
 CAST((SUBSTRING(Cast([PairID] as char),1,1) + '-' 
 + SUBSTRING(Cast([PairID] as char),2,2) + '-' 
 + SUBSTRING(Cast([PairID] as char),4,2) + '-' 
 + SUBSTRING(Cast([PairID] as char),6,3) + '-' 
 + SUBSTRING(Cast([PairID] as char),9,1)) as varchar(30)) As [mtPair], 
 CAST(TempTable.[PickID] as varchar(4)) PickID, 
 CASE WHEN TempTable.[PickID] IN('J2','J3','J4','J5','J6') THEN 'J2' 
 WHEN TempTable.[PickID] IN('M2','M3') THEN 'M2' 
 ELSE TempTable.[PickID] END AS [PickGroupCd], 
 CAST(' ' as varchar(60)) As [RegulatingNUA], 
 CAST(' ' as varchar(30)) As [SchedProgramCd], 
 CAST(' ' as varchar(30)) As [SchedIdCd], 
CAST(' ' AS VARCHAR(5)) AS [LeadStatusCd], 
CAST(' ' AS VARCHAR(5)) AS [PLKStatusCd], 
 CAST(0 as smallint) As [PeakedCap], 
 CAST(0 as int) As [Accumul], 
 CAST(0 as decimal(18,3)) As [PeakedMDT], 
 CAST(0 as decimal(18,5)) As [PaceAdj], 
 CAST(0 as dec(13,0)) as [PrePHCstDst], 
 CAST(0 as dec(13,3)) as [PrePHMDT], 
 CAST(0 as dec(13,0)) as [PrePTCstDst], 
 CAST(0 as dec(13,3)) as [PrePTMDT], 
 CAST(0 as dec(13,0)) as [PeakedPHForCstDst], 
 CAST(0 as dec(13,3)) as [PeakedPHForMDT], 
 CAST(0 as dec(13,0)) as [PeakedPTForCstDst], 
 CAST(0 as dec(13,3)) as [PeakedPTForMDT], 
 CAST(0 as dec(13,0)) as [PHJustForCstDst], 
 CAST(0 as dec(13,3)) as [PHJustForMDT], 
 CAST(0 as dec(13,0)) as [PTJustForCstDst], 
 CAST(0 as dec(13,3)) as [PTJustForMDT], 
 CAST(0 as dec(13,0)) as [PHPunchCstDst], 
 CAST(0 as dec(13,3)) as [PHPunchMDT], 
 CAST(0 as dec(13,0)) as [PTPunchCstDst], 
 CAST(0 as dec(13,3)) as [PTPunchMDT], 
 CAST(0 as dec(13,0)) as [BaseCstDst], 
 CAST(0 as dec(13,3)) as [BaseMDT], 
 CAST(0 as dec(13,0)) as [AdvCstDst], 
 CAST(0 as dec(13,3)) as [AdvMDT], 
 CAST([UCANName] as varchar(30)) AS [UCANName], 
 Cast(@ExtractDt As datetime) As [ExtractDt], 
 Cast(@PPExtractDate as datetime) AS [PPExtractDate] 
 FROM dbo.CI_NUA 
 INNER JOIN 
 (SELECT CAST('DX' AS VARCHAR(10)) AS [NUACTGY], 
 CAST(TBLSC.[PickID] AS VARCHAR(4)) PickID 
 FROM 
 (SELECT CAST('A1' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('A2' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('A3' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('B1' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('C1' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('D1' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('D2' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('E1' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('E2' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('F1' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('G1' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('H1' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('I1' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('J1' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('K1' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('L1' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('L2' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('M1' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('N1' AS VARCHAR(4)) PickID 
 UNION ALL 
 SELECT CAST('O1' AS VARCHAR(4)) PickID 
 ) AS TBLSC 
 ) AS TempTable 
 ON dbo.CI_NUA.[BCPERAbbr] = TempTable.[NUACTGY] 
 AND dbo.CI_NUA.[JobYearFK] = @JobYear 
 WHERE [BCPERAbbr] = 'DX' AND PairID = @PairedID 
 AND TempTable.[PickID] NOT IN 
 (SELECT DISTINCT [ReportPickID] 
 FROM dbo.PASNote 
 WHERE GroupPair = @PairedID AND ExtractDt = @ExtractDt 
 AND [JobYear] = @JobYear) 
-- 
-- 
Union All 
-- 
-- Select Note report lines from table dbo.PASNote 
-- 
 Select 
 CAST(JobYear as char(4)) + '-' + 
 CAST((CAST(JobYear as dec(4,0)) + 1) as char(4)) As [JobYear], 
 CAST(GroupPair as int) As [FinalizedPair], 
 CAST(NUA.[BCPERName] as varchar(60)) As [FinalizedBCPER], 
 CAST(NUA.[UCANName] as varchar(30)) As [FinalizedUCAN], 
 CAST((SUBSTRING(Cast([GroupPair] as char),1,1) + '-' 
 + SUBSTRING(Cast([GroupPair] as char),2,2) + '-' 
 + SUBSTRING(Cast([GroupPair] as char),4,2) + '-' 
 + SUBSTRING(Cast([GroupPair] as char),6,3) + '-' 
 + SUBSTRING(Cast([GroupPair] as char),9,1)) as varchar(30)) As [mtPair], 
 CAST(ReportPickID as varchar(4)) PickID, 
 CASE WHEN ReportPickID IN('J2','J3','J4','J5','J6') THEN 'J2' 
 WHEN ReportPickID IN('M2','M3') THEN 'M2' 
 ELSE ReportPickID END AS [PickGroupCd], 
 CAST(RegulatingBCPERName as varchar(60)) As [RegulatingNUA], 
 CAST(IsNull(SchedProgramCd,' ') as varchar(30)) As [SchedProgramCd], 
 CAST(IsNull(SchedIdCd,' ') as varchar(30)) As [SchedIdCd], 
 CAST([LeadStatusCd] AS VARCHAR(5)) AS [LeadStatusCd], 
CAST([PLKStatusCd] AS VARCHAR(5)) AS [PLKStatusCd], 
 CAST(IsNull(PeakedCap,0) as smallint) As [PeakedCap], 
 CAST(IsNull(Accumul,0) as int) As [Accumul], 
 CAST(IsNull(PeakedMDT1,0) as decimal(18,3)) As [PeakedMDT], 
 CAST(IsNull(DistrictPaceAdj,0) as decimal(18,5)) As [PaceAdj], 
 CAST(IsNull(PrePHCstDst,0) as dec(13,0)) as [PrePHCstDst], 
 CAST(IsNull(PrePHMDT,0) as dec(13,3)) as [PrePHMDT], 
 CAST(IsNull(PrePTCstDst,0) as dec(13,0)) as [PrePTCstDst], 
 CAST(IsNull(PrePTMDT,0) as dec(13,3)) as [PrePTMDT], 
 CAST((CAST(IsNull(PHJustForCstDst,0) as dec(13,0)) + CAST(IsNull(PHPunchCstDst,0) as dec(13,0))) as dec(13,0)) as [PeakedPHForCstDst], 
 CAST((CAST(IsNull(PHJustForMDT,0) as dec(13,3)) + CAST(IsNull(PHPunchMDT,0) as dec(13,3))) as dec(13,3)) as [PeakedPHForMDT], 
 CAST((CAST(IsNull(PTJustForCstDst,0) as dec(13,0)) + CAST(IsNull(PTPunchCstDst,0) as dec(13,0))) as dec(13,0)) as [PeakedPTForCstDst], 
 CAST((CAST(IsNull(PTJustForMDT,0) as dec(13,3)) + CAST(IsNull(PTPunchMDT,0) as dec(13,3))) as dec(13,3)) as [PeakedPTForMDT], 
 CAST(IsNull(PHJustForCstDst,0) as dec(13,0)) as [PHJustForCstDst], 
 CAST(IsNull(PHJustForMDT,0) as dec(13,3)) as [PHJustForMDT], 
 CAST(IsNull(PTJustForCstDst,0) as dec(13,0)) as [PTJustForCstDst], 
 CAST(IsNull(PTJustForMDT,0) as dec(13,3)) as [PTJustForMDT], 
 CAST(IsNull(PHPunchCstDst,0) as dec(13,0)) as [PHPunchCstDst], 
 CAST(IsNull(PHPunchMDT,0) as dec(13,3)) as [PHPunchMDT], 
 CAST(IsNull(PTPunchCstDst,0) as dec(13,0)) as [PTPunchCstDst], 
 CAST(IsNull(PTPunchMDT,0) as dec(13,3)) as [PTPunchMDT], 
 CAST(IsNull(BaseCstDst,0) as dec(13,0)) as [BaseCstDst], 
 CAST(IsNull(BaseMDT,0) as dec(13,3)) as [BaseMDT], 
 CAST(IsNull(AdvCstDst,0) as dec(13,0)) as [AdvCstDst], 
 CAST(IsNull(AdvMDT,0) as dec(13,3)) as [AdvMDT], 
 CAST(NUA.UCANName as varchar(30)) AS [UCANName], 
 Cast(ExtractDt As datetime) As [ExtractDt], 
 Cast(PPExtractDate As datetime) As [PPExtractDate] 
 FROM dbo.PASNote 
 Inner Join dbo.CI_NUA NUA 
 On dbo.PASNote.GroupPair = NUA.PairID 
 AND NUA.[JobYearFK] = @JobYear 
 WHERE 
 JobYear = @JobYear and 
 GroupPair = @PairedID AND 
 ExtractDt = @ExtractDt 
 
 Order by FinalizedPair, 
 PickID,RegulatingNUA, 
 PeakedCap,SchedIdCd 
 
SET NOCOUNT OFF
</code></pre>
</div>

<div>
What causes locks are long running queries. How long usually this query takes to run?
</div>

<div>
right now it just ran in 3 seconds.. &nbsp;it has run scheduled for many months.. it was just yesterday that this has misbehaved (that we saw).. 
 
what was puzzling was generally in sysprocesses, the blocked id is different than spid.. but here spid was same as blocked id and also several lines...
</div>

<div>
thank you. I will keep an eye on it. 
 
Paul, appreciate your note. 
 
do you have an mock tsql example to show how bad performance can deteriorate by adding a distinct?
</div>

<div>
<div class="content wysiwyg-content">
This query runs fine every day scheduled.. but yesterday, it was blocked by itself in sysprocesses.. when I did select * from master..sysprocesses where blocked&lt;&gt;0 I saw 12 lines all for this one process running this, and all with same spid and same blocked column data. 
 
is it because of excessive UNION/CAST etc. CAn you suggest?
</div>
</div>

This query runs fine every day scheduled.. but yesterday, it was blocked by itself in sysprocesses.. when I did select * from master..sysprocesses where blocked<>0 I saw 12 lines all for this one process running this, and all with same spid and same blocked column data.

is it because of excessive UNION/CAST etc. CAn you suggest?

can lot of CASTs/Union or ISNULL possibly cause blocks?

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.