запрос занимает слишком много времени на сервере sql

Я запускаю следующую команду обновления на 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 содержит около 1 миллиона записей, а table2 - миллионы (огромная таблица). каждый раз, когда я запускаю запрос, он переходит в режим приостановки из dm_exec_requests типа ожидания show cxpacket, поэтому я использую maxdop 1 намек не использовать параллелизм.

После этого я получаю тип ожидания PAGEIOLATCH_SH. Я открыл монитор активности, чтобы посмотреть, что там происходит. Таким образом, он показывает мне, что буферная память заполнена до 13 ГБ (ОЗУ моей машины всего 15 ГБ).

Пожалуйста, дайте мне знать, нужно ли мне оптимизировать запрос или мне нужно что-то еще.

РЕДАКТИРОВАТЬ: индекс на 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

ИЗМЕНИТЬ 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

sql sql-server performance wait

Aamir    16.09.2014    источник

comment

Опубликуйте, пожалуйста, план выполнения вашего запроса, который поможет ответить на ваш вопрос.   -   Tony    16.09.2014

comment

Вам не нужно оптимизировать запрос, если он вас на 100% доволен. ;) Вопрос: У вас есть какие-либо индексы, определенные для столбцов, которые появляются в предложениях JOIN и WHERE? Если да, не могли бы вы добавить их определение к своему вопросу?   -   stakx - no longer contributing    16.09.2014

comment

@stakx нам действительно нужно использовать здесь index, потому что мы говорим о большой таблице 10 ГБ, то есть table2. Как вы думаете, оптимизатор будет использовать там индекс?   -   Aamir    16.09.2014

comment

@ Тони, скажите, пожалуйста, как я могу получить план выполнения в текстовом формате. потому что я пробовал использовать SSMS, но он показал мне графический план.   -   Aamir    16.09.2014

comment

@Aamir: Таблица размером 10 ГБ довольно большая. Я был бы удивлен, если бы оптимизатор запросов не использовал подходящие индексы. Но не имеет значения, что думаю я: опубликуйте план выполнения (и, возможно, определения ваших индексов), и это будет гораздо более долгим путем, объясняя, почему ваш запрос работает хуже, чем я предполагаю.   -   stakx - no longer contributing    16.09.2014

comment

Стол 10г - это арахис. Шутки в сторону. Если вам нужен большой стол, приходите в мою компанию и посмотрите на таблицы уровней терабайт, с которыми мы имеем дело. Индексы имеют решающее значение, потому что в противном случае вы говорите о сканировании таблиц, а это значит, что если ВСЕ не находится в памяти - вы попадете в свою очень вероятно сверхмедленную дисковую подсистему, потому что почему-то я не думаю, что вы запускаете это на действительно оптимизированной по скорости настройке SSD. План запроса и задержка диска при запросе, пожалуйста;)   -   TomTom    16.09.2014

comment

@TomTom, пожалуйста, проверьте правки. Это немного неструктурировано, но надеюсь, что вы можете мне с этим помочь.   -   Aamir    16.09.2014

comment

Самым дорогим оператором в этом плане запроса, вероятно, является sort distinct, но я не понимаю, почему он там.   -   Nick.McDermaid    16.09.2014

Ответы (1)

arrow_upward
0
arrow_downward

вы не сообщили нам, будет ли завершен запрос в разумный промежуток времени.
в этом случае вам не следует изменять его (прочтите одну из многих статей о преждевременная оптимизация ^^).

Первое улучшение - индекс по внешнему ключу в table2; вы имеете дело с миллионом строк, и одно из связанных полей не индексируется. вы оценили влияние такого индекса на ваше приложение? Можно ли создать этот индекс в вашей системе, не влияя на повседневную работу?

в зависимости от количества строк, полученных в результате примененных фильтров WHERE, вы можете попробовать выполнить подзапрос:

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';

если количество строк из таблицы 2 значительно уменьшено фильтром, это решение может быть улучшением.
то же самое может быть верно и для таблицы 1; если фильтр на proc_flag исключит половину строк, перемещение фильтра в подзапрос может дать некоторые улучшения.
вышесказанное является лишь предположением из-за оптимизатор запросов, но изменения настолько малы, что, возможно, стоит попробовать ...

Paolo    16.09.2014

запрос занимает слишком много времени на сервере sql

Ответы (1)

Похожие вопросы