query taking too much time in sql server

Question

I am running the following update command on sql server 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 contains records around 1 millions while table2 contains millions(huge table). every time I run the query, it goes to suspend mode from dm_exec_requests wait type show cxpacket so i use maxdop 1 hint to use no parallelism.

After that I am getting PAGEIOLATCH_SH wait type. I opened activity monitor to see whats going on back there. So its showing me buffer memory is full upto 13 GB(RAM of my machine is 15 GB only).

So please let me know, Do I have to optimize the query or I need something else.

EDIT : index on table2

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

index on table1

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

EDIT 2:

This is what I have got in query execution plan:

 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

EDIT:3

One more thing I want to add, after doing lot of search. I find out some queries which is giving me the following output why above query going in suspend mode due to pageiolatch_sh

Object  Type       Index       Index_Type   buffer_pages     buffer_mb
TABLE2  USER_TABLE             HEAP         1319249      10306

Please post the execution plan for your query, that will help answer your question. — Tony, Sep 16 '14 at 07:04
You don't *have to* optimise the query if you're 100 % happy with it. ;) Question: Do you have any indexes defined on the columns that appear in the `JOIN` and `WHERE` clauses? If so, could you perhaps add their definition to your question? — stakx - no longer contributing, Sep 16 '14 at 07:04
@stakx do we really need to use index here because we are talking about 10GB big table i.e. table2 . Do you think optimizer going to use index there? — Aamir, Sep 16 '14 at 07:10
@Tony please tell me how i can get execution plan in textual format. because i tried in SSMS but it showed me graphical plan. — Aamir, Sep 16 '14 at 07:12
@Aamir: A 10 GB table is fairly large. I would be surprised if the query optimizer *didn't* use any suitable indexes. But it doesn't matter what *I* think: Post the execution plan (and perhaps your index definitions), and that will go a much longer way explaining why your query performs badly than my guesses. — stakx - no longer contributing, Sep 16 '14 at 07:13
A 10g table is peanuts. Seriously. If you want a large table come to my company and look at the terabyte level tables we deal with. Indices are critical because otherwise you talk table scan and that then means that unless EVERYTHING is in memory - you hit your very likely super slow disc subsystem because somehow I Do not think you run that on a really speed optimized SSD setup. Query plan, and disc latency during query please ;) — TomTom, Sep 16 '14 at 07:25
@TomTom please check the edits. Its a bit unstructured but hope you can help me with this. — Aamir, Sep 16 '14 at 07:38
The most expensive operator in that query plan is probably the `sort distinct` but I don't understand why it's there. — Nick.Mc, Sep 16 '14 at 07:52

score 0 · Answer 1 · edited May 23 '17 at 12:29

you didn't tell us if the query ever get to completion in a reasonable time span.
if this is the case then you should not modify it (read one of the many articles about premature optimization ^^).

a first improvement is an index on the foreign key in table2; you deal with million of rows and one of the linked fields is not indexed. did you evaluate the impact of such an index on your application? is it possible to create that index on your system without affecting daily operation?

depending on the number of rows resulting from the WHERE filters applied you may try to go with a subquery:

update a
set a.proc_flag='CC'
from abc.dbo.table1 a with (NOLOCK)
inner join (
  select [only the fields you need] from xyz.dbo.table2 
  dt >= dateadd(D,-(180),cast(GETDATE() as date))
  and position in ('AD',...,'avb')
) b on a.id = b.id
where a.proc_flag ='C';

if the number of rows from table2 is greatly reduced by the filter this solution may be an improvement.
the same may be true for table1 also; should the filter on proc_flag rule out half of the rows moving the filter into a subquery may give some improvements.
the above are only guesses because of the query optimizer but the changes are so small that may be worth a try...

query taking too much time in sql server

1 Answers1