заявката отнема твърде много време в sql сървъра

Изпълнявам следната команда за актуализиране на sql сървър 2012.

update a
set a.proc_flag='CC'
from abc.dbo.table1 a with (NOLOCK)
inner join xyz.dbo.table2 b with (NOLOCK)
on a.id = b.id
where a.proc_flag ='C'
and b.dt >= dateadd(D,-(180),cast(GETDATE() as date))
and b.position in 
(
'AD',
'BUE',
'DS',
'HP',
'IFER',
'MER',
'NT',
'NNG',
'TON',
'GNUM',
'ERNA',
'BL', 
'DPO', 
'DEM', 
'HU',
'ILL',
'IKT',
'LB',
'OM',
'GE',
'RF',
'sd',
'avb');

table1 съдържа записи около 1 милион, докато table2 съдържа милиони (огромна таблица). всеки път, когато стартирам заявката, тя преминава в режим на спиране от dm_exec_requests изчакване тип показване на cxpacket така че използвам maxdop 1 намек да не се използва паралелизъм.

След това получавам PAGEIOLATCH_SH тип изчакване. Отворих монитора на активността, за да видя какво става там. Така че ми показва, че буферната памет е пълна до 13 GB (RAM на моята машина е само 15 GB).

Така че, моля, уведомете ме, трябва ли да оптимизирам заявката или имам нужда от нещо друго.

РЕДАКТИРАНЕ: индекс на таблица2

1-    CREATE NONCLUSTERED INDEX [IDX_DT] ON [dbo].[table2]
(
    [dt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

2 - CREATE NONCLUSTERED INDEX [IDX_SERVER_DT] ON [dbo].[table2]
    (
        [server_id] ASC,
        [dt] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

индекс на таблица1

CREATE NONCLUSTERED INDEX [ID] ON [dbo].[table1]
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

РЕДАКТИРАНЕ 2:

Ето какво имам в плана за изпълнение на заявката:

 StmtText   StmtId  NodeId  Parent  PhysicalOp  LogicalOp   Argument    DefinedValues   EstimateRows    EstimateIO  EstimateCPU AvgRowSize  TotalSubtreeCost    OutputList  Warnings    Type    Parallel    EstimateExecutions
update a
set a.proc_flag='CF'
from abc.dbo.table1 a with (NOLOCK)
inner join xyz.dbo.table2 b with (NOLOCK)
on a.id = b.id
where a.proc_flag ='C'
and b.dt >= dateadd(D,-(cast('180' as int)),cast(GETDATE() as date))
and b.position in 
(
'ADC',
'BUSINE',
'DNC',
'HUNGUP',
'INXFER',
'MNXFER',
'NCOMIT',
'NONENG',
'TRITON',
'WRGNUM',
'XFERNA',
'BL', 
'DISPO', 
'DONEM', 
'HU',
'INCALL',
'INKT',
'LB',
'NONCOM',
'PLEDGE',
'REF',
'SALE',
'XFER') 1   1   0   NULL    NULL    1   NULL    1   NULL    NULL    NULL    0.03451508  NULL    NULL    UPDATE  0   NULL
  |--Table Update(OBJECT:([abc].[dbo].[table1] AS [a]), SET:([abc].[dbo].[table1].[proc_flag] as [a].[proc_flag] = [Expr1006])) 1   2   1   Table Update    Update  OBJECT:([abc].[dbo].[table1] AS [a]), SET:([abc].[dbo].[table1].[proc_flag] as [a].[proc_flag] = [Expr1006])    NULL    1   0.01    1E-06   9   0.03451508  NULL    NULL    PLAN_ROW    0   1
       |--Compute Scalar(DEFINE:([Expr1006]='CF'))  1   3   2   Compute Scalar  Compute Scalar  DEFINE:([Expr1006]='CF')    [Expr1006]='CF' 1   0   1E-07   20  0.02451408  [Bmk1000], [Expr1006]   NULL    PLAN_ROW    0   1
            |--Top(ROWCOUNT est 0)  1   4   3   Top Top TOP EXPRESSION:((0))    NULL    1   0   1E-07   15  0.02451398  [Bmk1000]   NULL    PLAN_ROW    0   1
                 |--Sort(DISTINCT ORDER BY:([Bmk1000] ASC)) 1   5   4   Sort    Distinct Sort   DISTINCT ORDER BY:([Bmk1000] ASC)   NULL    1   0.01126126  0.000100015 15  0.02451388  [Bmk1000]   NULL    PLAN_ROW    0   1
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000])) 1   6   5   Nested Loops    Inner Join  OUTER REFERENCES:([Bmk1000])    NULL    1   0   4.18E-06    20  0.01315212  [Bmk1000]   NULL    PLAN_ROW    0   1
                           |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[id])) 1   7   6   Nested Loops    Inner Join  OUTER REFERENCES:([b].[id]) NULL    1   0   4.18E-06    15  0.00986484  [Bmk1000]   NULL    PLAN_ROW    0   1
                           |    |--Filter(WHERE:([xyz].[dbo].[table2].[position] as [b].[position]='ADC' OR [xyz].[dbo].[table2].[position] as [b].[position]='BL' OR [xyz].[dbo].[table2].[position] as [b].[position]='BUSINE' OR [xyz].[dbo].[table2].[position] as [b].[position]='DISPO' OR [xyz].[dbo].[table2].[position] as [b].[position]='DNC' OR [xyz].[dbo].[table2].[position] as [b].[position]='DONEM' OR [xyz].[dbo].[table2].[position] as [b].[position]='HU' OR [xyz].[dbo].[table2].[position] as [b].[position]='HUNGUP' OR [xyz].[dbo].[table2].[position] as [b].[position]='INCALL' OR [xyz].[dbo].[table2].[position] as [b].[position]='INKT' OR [xyz].[dbo].[table2].[position] as [b].[position]='INXFER' OR [xyz].[dbo].[table2].[position] as [b].[position]='LB' OR [xyz].[dbo].[table2].[position] as [b].[position]='MNXFER' OR [xyz].[dbo].[table2].[position] as [b].[position]='NCOMIT' OR [xyz].[dbo].[table2].[position] as [b].[position]='NONCOM' OR [xyz].[dbo].[table2].[position] as [b].[position]='NONENG' OR [xyz].[dbo].[table2].[position] as [b].[position]='PLEDGE' OR [xyz].[dbo].[table2].[position] as [b].[position]='REF' OR [xyz].[dbo].[table2].[position] as [b].[position]='SALE' OR [xyz].[dbo].[table2].[position] as [b].[position]='TRITON' OR [xyz].[dbo].[table2].[position] as [b].[position]='WRGNUM' OR [xyz].[dbo].[table2].[position] as [b].[position]='XFER' OR [xyz].[dbo].[table2].[position] as [b].[position]='XFERNA'))    1   8   7   Filter  Filter  WHERE:([xyz].[dbo].[table2].[position] as [b].[position]='ADC' OR [xyz].[dbo].[table2].[position] as [b].[position]='BL' OR [xyz].[dbo].[table2].[position] as [b].[position]='BUSINE' OR [xyz].[dbo].[table2].[position] as [b].[position]='DISPO' OR [xyz].[dbo].[table2].[position] as [b].[position]='DNC' OR [xyz].[dbo].[table2].[position] as [b].[position]='DONEM' OR [xyz].[dbo].[table2].[position] as [b].[position]='HU' OR [xyz].[dbo].[table2].[position] as [b].[position]='HUNGUP' OR [xyz].[dbo].[table2].[position] as [b].[position]='INCALL' OR [xyz].[dbo].[table2].[position] as [b].[position]='INKT' OR [xyz].[dbo].[table2].[position] as [b].[position]='INXFER' OR [xyz].[dbo].[table2].[position] as [b].[position]='LB' OR [xyz].[dbo].[table2].[position] as [b].[position]='MNXFER' OR [xyz].[dbo].[table2].[position] as [b].[position]='NCOMIT' OR [xyz].[dbo].[table2].[position] as [b].[position]='NONCOM' OR [xyz].[dbo].[table2].[position] as [b].[position]='NONENG' OR [xyz].[dbo].[table2].[position] as [b].[position]='PLEDGE' OR [xyz].[dbo].[table2].[position] as [b].[position]='REF' OR [xyz].[dbo].[table2].[position] as [b].[position]='SALE' OR [xyz].[dbo].[table2].[position] as [b].[position]='TRITON' OR [xyz].[dbo].[table2].[position] as [b].[position]='WRGNUM' OR [xyz].[dbo].[table2].[position] as [b].[position]='XFER' OR [xyz].[dbo].[table2].[position] as [b].[position]='XFERNA')   NULL    1   0   7.18E-06    20  0.00657756  [b].[id]    NULL    PLAN_ROW    0   1
                           |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1003]) OPTIMIZED)    1   9   8   Nested Loops    Inner Join  OUTER REFERENCES:([Bmk1003]) OPTIMIZED  NULL    1   0   4.18E-06    24  0.00657038  [b].[id], [b].[position]    NULL    PLAN_ROW    0   1
                           |    |         |--Compute Scalar(DEFINE:([Expr1018]=BmkToPage([Bmk1003])))   1   11  9   Compute Scalar  Compute Scalar  DEFINE:([Expr1018]=BmkToPage([Bmk1003]))    [Expr1018]=BmkToPage([Bmk1003]) 1   0.003125    0.0001581   15  0.0032831   [Bmk1003], [Expr1018]   NULL    PLAN_ROW    0   1
                           |    |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1016], [Expr1017], [Expr1015]))   1   12  11  Nested Loops    Inner Join  OUTER REFERENCES:([Expr1016], [Expr1017], [Expr1015])   NULL    1   0.003125    0.0001581   15  0.0032831   [Bmk1003]   NULL    PLAN_ROW    0   1
                           |    |         |         |--Compute Scalar(DEFINE:(([Expr1016],[Expr1017],[Expr1015])=GetRangeWithMismatchedTypes(dateadd(day,(-180),CONVERT(date,getdate(),0)),NULL,(22)))) 1   13  12  Compute Scalar  Compute Scalar  DEFINE:(([Expr1016],[Expr1017],[Expr1015])=GetRangeWithMismatchedTypes(dateadd(day,(-180),CONVERT(date,getdate(),0)),NULL,(22)))    ([Expr1016],[Expr1017],[Expr1015])=GetRangeWithMismatchedTypes(dateadd(day,(-180),CONVERT(date,getdate(),0)),NULL,(22)) 1   0   0   27  0   [Expr1016], [Expr1017], [Expr1015]  NULL    PLAN_ROW    0   1
                           |    |         |         |    |--Constant Scan   1   14  13  Constant Scan   Constant Scan   NULL    NULL    1   0   0   0   0   NULL    NULL    PLAN_ROW    0   1
                           |    |         |         |--Index Seek(OBJECT:([xyz].[dbo].[table2].[IDX_DTANSWERED] AS [b]), SEEK:([b].[dt] > [Expr1016] AND [b].[dt] < [Expr1017]) ORDERED FORWARD)    1   18  12  Index Seek  Index Seek  OBJECT:([xyz].[dbo].[table2].[IDX_DTANSWERED] AS [b]), SEEK:([b].[dt] > [Expr1016] AND [b].[dt] < [Expr1017]) ORDERED FORWARD   [Bmk1003]   1   0.003125    0.0001581   15  0.0032831   [Bmk1003]   NULL    PLAN_ROW    0   1
                           |    |         |--RID Lookup(OBJECT:([xyz].[dbo].[table2] AS [b]), SEEK:([Bmk1003]=[Bmk1003]) LOOKUP ORDERED FORWARD)    1   23  9   RID Lookup  RID Lookup  OBJECT:([xyz].[dbo].[table2] AS [b]), SEEK:([Bmk1003]=[Bmk1003]) LOOKUP ORDERED FORWARD [b].[id], [b].[position]    1   0.003125    0.0001581   28  0.0032831   [b].[id], [b].[position]    NULL    PLAN_ROW    0   1
                           |    |--Index Seek(OBJECT:([abc].[dbo].[table1].[IDX_PHONE] AS [a]), SEEK:([a].[id]=[xyz].[dbo].[table2].[id] as [b].[id]) ORDERED FORWARD)  1   30  7   Index Seek  Index Seek  OBJECT:([abc].[dbo].[table1].[IDX_PHONE] AS [a]), SEEK:([a].[id]=[xyz].[dbo].[table2].[id] as [b].[id]) ORDERED FORWARD [Bmk1000]   1   0.003125    0.0001581   15  0.0032831   [Bmk1000]   NULL    PLAN_ROW    0   1
                           |--RID Lookup(OBJECT:([abc].[dbo].[table1] AS [a]), SEEK:([Bmk1000]=[Bmk1000]),  WHERE:([abc].[dbo].[table1].[proc_flag] as [a].[proc_flag]='C') LOOKUP ORDERED FORWARD) 1   32  6   RID Lookup  RID Lookup  OBJECT:([abc].[dbo].[table1] AS [a]), SEEK:([Bmk1000]=[Bmk1000]),  WHERE:([abc].[dbo].[table1].[proc_flag] as [a].[proc_flag]='C') LOOKUP ORDERED FORWARD   NULL    1   0.003125    0.0001581   12  0.0032831   NULL    NULL    PLAN_ROW    0   1

РЕДАКТИРАНЕ: 3

Още нещо, което искам да добавя след много търсене. Откривам някои заявки, които ми дават следния резултат защо горната заявка преминава в режим на спиране поради pageiolatch_sh

Object  Type       Index       Index_Type   buffer_pages     buffer_mb
TABLE2  USER_TABLE             HEAP         1319249      10306