1

I have a query that executes very often on our system and there is no option to change the query text. Are there any suggestions on what could be done? Any help would be appreciated. The query:

SELECT TOP 100 A126.DocID FROM [Документы] A126 WITH ( NOLOCK ) LEFT JOIN [Функция] A362JTA126_1 WITH ( NOLOCK ) ON A362JTA126_1.DocID=A126.[ТипПроцесса] WHERE EXISTS (   SELECT A127.[Index]   FROM [Документы_Позиции] A127 WITH ( NOLOCK )   LEFT  JOIN   [Процесс] A144JTA127_1 WITH ( NOLOCK )   ON A144JTA127_1.DocID=A127.[ПроцессОткуда]
      LEFT JOIN
      [Процесс] A144JTA127_2 WITH ( NOLOCK )
      ON A144JTA127_2.DocID=A127.[ПроцессКуда]   WHERE A127.LinkDetail=A126.DocID
        AND (A127.[ПроцессОткуда]=4627332
             OR A144JTA127_1.GroupPath LIKE '43.59.806386.4627332.%'
             OR A127.[ПроцессКуда]=4627332
                OR A144JTA127_2.GroupPath LIKE '43.59.806386.4627332.%') )   AND A126.Deleted=0
      AND A126.[ТипПроцесса]<>267
      AND A126.isGroup=0   AND (A126.[ТипПроцесса]=275
       OR A362JTA126_1.GroupPath LIKE '275.%'
       OR A126.[ТипПроцесса]=580
          OR A362JTA126_1.GroupPath LIKE '580.%')   AND A126.[ДатаНачала]>='20180401 00:00:00:0'
      AND A126.[ДатаНачала]<'20180701 00:00:00:0'
      AND A126.[ТипПроцесса]=654
      AND NOT A126.[Завершен]=1
      AND A126.Deleted=0
      AND (A126.DocID IN (272761,  811833,  273092,  273083,  552289,  273081,  889812)
           OR A126.GroupPath LIKE '273080.272761.%'
              OR A126.GroupPath LIKE '273080.811833.%'
              OR A126.GroupPath LIKE '273080.273092.%'
              OR A126.GroupPath LIKE '273080.273083.%'
              OR A126.GroupPath LIKE '273080.552289.%'
              OR A126.GroupPath LIKE '273080.273081.%'
              OR A126.GroupPath LIKE '273080.889812.%'
           OR A126.DocID IN (273080, 273080, 273080, 273080, 273080, 273080, 273080))
          AND A126.[НашеПредприятие]<>13140
              AND NOT(A126.DocID IN (273134, 273135, 273144, 273145, 683891, 478043, 2419647)
                      OR A126.GroupPath LIKE '273080.273134.%'
                         OR A126.GroupPath LIKE '273080.273135.%'
                         OR A126.GroupPath LIKE '273080.273144.%'
                         OR A126.GroupPath LIKE '273080.273145.%'
                         OR A126.GroupPath LIKE '273080.683891.%'
                         OR A126.GroupPath LIKE '273080.478043.%'
                         OR A126.GroupPath LIKE '273080.2419647.%')
      AND (A126.DocID<215403978
           OR A126.DocID IS NULL) ORDER BY A126.DocID DESC;

The actual query plan is here. Tables are:

CREATE TABLE [dbo].[Документы](
    [DocID] [int] NOT NULL,
    [ExtID] [varchar](14) NOT NULL,
    [Deleted] [int] NOT NULL,
    [ModifyDate] [datetime] NOT NULL,
    [GroupDoc] [int] NOT NULL,
    [isGroup] [int] NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    [CreateUser] [varchar](40) NOT NULL,
    [UpdateDate] [datetime] NOT NULL,
    [UpdateUser] [varchar](40) NOT NULL,
    [CreateCode] [varchar](10) NOT NULL,
    [UpdateCode] [varchar](10) NOT NULL,
    [GroupPath] [varchar](850) NOT NULL,
    [Version] [int] NOT NULL,
    [КодФилиала] [varchar](80) NOT NULL,
    [УровеньКонфиденциальности] [int] NOT NULL,
    [ЭлементСБ] [bit] NOT NULL,
    [ЗамокЭлементаСБ] [bit] NOT NULL,
    [КодПодсистемы] [int] NOT NULL,
    [Код] [varchar](80) NOT NULL,
    [Имя] [varchar](300) NOT NULL,
    [ТипПроцесса] [int] NOT NULL,
    [Замок] [bit] NOT NULL,
    [НеОтображаетсяВДереве] [bit] NOT NULL,
    [Завершен] [bit] NOT NULL,
    [Комментарий] [varchar](8000) NOT NULL,
    [ДатаНачала] [datetime] NOT NULL,
    [ДатаВыверки] [datetime] NOT NULL,
    [ПечатныйНомер] [varchar](80) NOT NULL,
    [НеЯвляетсяАналитикой] [bit] NOT NULL,
    [НеПорождаетПроводки] [bit] NOT NULL,
    [ОбщСтатус] [int] NOT NULL,
    [НашеПредприятие] [int] NOT NULL,
    [НашеПодразделение] [int] NOT NULL,
    [Инициатор] [int] NOT NULL,
    [Менеджер] [int] NOT NULL,
    [Исполнитель] [int] NOT NULL,
    [Проверяющий] [int] NOT NULL,
    [Контрагент] [int] NOT NULL,
    [ПредставительКонтрагента] [int] NOT NULL,
    [Посредник] [int] NOT NULL,
    [ПредставительПосредника] [int] NOT NULL,
    [Ответственный] [int] NOT NULL,
    [Местоположение] [int] NOT NULL,
    [Заказ] [int] NOT NULL,
    [Основание] [int] NOT NULL,
    [РеквизитСозданияОснования] [int] NOT NULL,
    [ОтключитьОкругления] [bit] NOT NULL,
    [ПроцессМенеджер] [int] NOT NULL,
    [ПроцессПодразделение] [int] NOT NULL,
    [ОбщПроцессОткуда] [int] NOT NULL,
    [ОбщПроцессКуда] [int] NOT NULL,
    [СуммаКолОткуда] [float] NOT NULL,
    [СуммаКолКуда] [float] NOT NULL,
    [ОбщЕдИзмОткуда] [int] NOT NULL,
    [ОбщЕдИзмКуда] [int] NOT NULL,
    [СуммаКол2Откуда] [float] NOT NULL,
    [СуммаКол2Куда] [float] NOT NULL,
    [ОбщЕдИзм2Откуда] [int] NOT NULL,
    [ОбщЕдИзм2Куда] [int] NOT NULL,
    [ЭтоПереоценка] [bit] NOT NULL,
    [СозданАвтоматическиДляПереоценки] [bit] NOT NULL,
    [РезервДатаНачала] [datetime] NOT NULL,
    [РезервДатаОкончания] [datetime] NOT NULL,
    [ВидРезервирования] [int] NOT NULL,
    [БанковскиеРеквизиты] [int] NOT NULL,
    [РеквизитыБанкаПосредника] [int] NOT NULL,
    [ПроцессЗатрат] [int] NOT NULL,
    [ВалютаПроцесса] [int] NOT NULL,
    [ПланСрокЗавершения] [datetime] NOT NULL,
    [СрокГодности] [datetime] NOT NULL,
    [ВидКурсаРасчетов] [int] NOT NULL,
    [ДатаКурсаРасчетов] [datetime] NOT NULL,
    [ПроцентОтклонКурса] [float] NOT NULL,
    [ЗнакОтклонения] [int] NOT NULL,
    [ФиксированныйКурс] [float] NOT NULL,
    [ЭтоПартия] [bit] NOT NULL,
    [ЭтоЗаказ] [bit] NOT NULL,
    [ЭтоГТД] [bit] NOT NULL,
    [ЭтоСерийныйНомер] [bit] NOT NULL,
    [ЭтоСпецификация] [bit] NOT NULL,
    [ИзделиеПоСпецификации] [int] NOT NULL,
    [Спецификация] [int] NOT NULL,
    [ПереданВБухгалтерию] [bit] NOT NULL,
    [СтранаПроисхождения] [int] NOT NULL,
    [ЭтоПрайс] [bit] NOT NULL,
    [ЭтоЖесткийРезерв] [bit] NOT NULL,
    [Номер1] [varchar](80) NOT NULL,
    [Номер2] [varchar](80) NOT NULL,
    [Номер3] [varchar](80) NOT NULL,
    [Номер4] [varchar](80) NOT NULL,
    [Номер5] [varchar](80) NOT NULL,
    [Дата1] [datetime] NOT NULL,
    [Дата2] [datetime] NOT NULL,
    [Дата3] [datetime] NOT NULL,
    [Дата4] [datetime] NOT NULL,
    [Дата5] [datetime] NOT NULL,
    [Число1] [float] NOT NULL,
    [Число2] [float] NOT NULL,
    [Объект1] [int] NOT NULL,
    [Объект2] [int] NOT NULL,
    [Объект3] [int] NOT NULL,
    [Объект4] [int] NOT NULL,
    [Объект5] [int] NOT NULL,
    [БалСтатья] [int] NOT NULL,
    [СостояниеПроцесса] [int] NOT NULL,
    [Важность] [int] NOT NULL,
    [КемУстановленПриоритет] [int] NOT NULL,
    [ТМЦ] [int] NOT NULL,
    [ОтключитьАвторезервирование] [bit] NOT NULL,
    [ИзменяетСостояниеДляПроцесса] [int] NOT NULL,
    [ИзСостояния] [int] NOT NULL,
    [ВСостояние] [int] NOT NULL,
    [СпособХранения] [int] NOT NULL,
    [ПриложенныйФайл] [image] NULL,
    [ИмяПриложенногоФайла] [varchar](250) NOT NULL,
    [РазмерПриложенногоФайла] [int] NOT NULL,
    [ДатаПриложенногоФайла] [datetime] NOT NULL,
    [АбсолютноеИмяФайла] [varchar](850) NOT NULL,
    [ПриложенныйФайлОбщий] [int] NOT NULL,
    [ВариантУчета] [int] NOT NULL,
    [ОснованиеЗаявка] [int] NOT NULL,
    [ОснованиеРезерв] [int] NOT NULL,
    [ЗаказРезерв] [int] NOT NULL,
    [Категория] [int] NOT NULL,
    [ШтрихКод] [varchar](80) NOT NULL,
    [ПриоритетРасстановкиПартий] [int] NOT NULL,
    [Флаг1] [bit] NOT NULL,
    [Флаг2] [bit] NOT NULL,
    [Флаг3] [bit] NOT NULL,
    [Цвет] [int] NOT NULL,
    [пка_ТипОплаты] [int] NOT NULL,
    [пка_ТипДоставки] [int] NOT NULL,
    [пка_ТранспортнаяКомпания] [int] NOT NULL,
    [СпособКонтакта] [int] NOT NULL,
    [ТипСобытия] [int] NOT NULL,
    [ТипДопИнфо] [int] NOT NULL,
    [РекламныйИсточник] [int] NOT NULL,
    [ДокументыВозвращены] [bit] NOT NULL,
    [НабратьК] [datetime] NOT NULL,
    [ЗабратьВозврат] [bit] NOT NULL,
    [ЗабратьДолг] [bit] NOT NULL,
    [Наборщик] [int] NOT NULL,
    [ВремяСборкиС] [datetime] NOT NULL,
    [ВремяСборкиПо] [datetime] NOT NULL,
    [Отгрузил] [int] NOT NULL,
    [НачалоПребыванияВЗонеОтгрузки] [datetime] NOT NULL,
    [ОкончаниеПребыванияВЗонеОтгрузки] [datetime] NOT NULL,
    [Маршрут] [int] NOT NULL,
    [ВодительЭкспедитор] [int] NOT NULL,
    [Перевозчик] [int] NOT NULL,
    [КомментарийСклада] [text] NULL,
    [КомментарийВодителя] [text] NULL,
    [ТипОтгрузки] [int] NOT NULL,
    [ТипОплаты] [int] NOT NULL,
    [ФормаОплаты] [int] NOT NULL,
    [АдресДоставки] [int] NOT NULL,
    [ВремяДоставки] [datetime] NOT NULL,
    [АдресПерегруза] [varchar](250) NOT NULL,
    [КонтактПерегруза] [varchar](250) NOT NULL,
    [Прайс] [int] NOT NULL,
    [ВидДеятельности] [int] NOT NULL,
    [СБК] [int] NOT NULL,
    [СБК_CF] [int] NOT NULL,
    [ПравилоCF] [int] NOT NULL,
    [ПравилоPL] [int] NOT NULL,
    [МаршрутС] [int] NOT NULL,
    [Выезд] [int] NOT NULL,
    [АдресаДоставки] [int] NOT NULL,
    [КонечныйАдрес] [int] NOT NULL,
    [Автомобиль] [int] NOT NULL,
    [ВремяИзмененияСостояния] [datetime] NOT NULL,
    [ИзменившийСостояние] [varchar](80) NOT NULL,
    [НомерЗаказа] [int] NOT NULL,
    [ПерепечататьДокументы] [bit] NOT NULL,
    [СуммаЛимита] [float] NOT NULL,
    [ДатаДействияЛимита] [datetime] NOT NULL,
    [ТипОтгрузкиСсылка] [int] NOT NULL,
    [ВыгружатьВ1С] [bit] NOT NULL,
    [Номер6] [varchar](80) NOT NULL,
    [Номер7] [varchar](80) NOT NULL,
    [Дата6] [datetime] NOT NULL,
    [Дата7] [datetime] NOT NULL,
    [Флаг4] [bit] NOT NULL,
    [Флаг5] [bit] NOT NULL,
    [Флаг6] [bit] NOT NULL,
    [ОбщийВес] [float] NOT NULL,
    [ОбщийОбъем] [float] NOT NULL,
    [Число3] [float] NOT NULL,
    [ЗаказПроцесс] [int] NOT NULL,
    [ОснованиеПроцесс] [int] NOT NULL,
    [Раскрыта] [bit] NOT NULL,
    [Название] [varchar](250) NOT NULL,
    [МенеджерСМ] [int] NOT NULL,
    [Флаг7] [bit] NOT NULL,
    [Флаг8] [bit] NOT NULL,
    [Флаг9] [bit] NOT NULL,
    [Флаг10] [bit] NOT NULL,
    [РаботаСНДС] [bit] NOT NULL,
    [ПроцессНДС] [int] NOT NULL,
    [ПроцессНДС2] [int] NOT NULL,
    [ОснованиеВозврата] [int] NOT NULL,
    [AttachmentID] [int] NOT NULL,
    [СостояниеОтгрузки] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [DocID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END


CREATE TABLE [dbo].[Функция](
    [DocID] [int] NOT NULL,
    [ExtID] [varchar](80) NOT NULL,
    [Deleted] [int] NOT NULL,
    [ModifyDate] [datetime] NOT NULL,
    [GroupDoc] [int] NOT NULL,
    [isGroup] [int] NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    [CreateUser] [varchar](40) NOT NULL,
    [UpdateDate] [datetime] NOT NULL,
    [UpdateUser] [varchar](40) NOT NULL,
    [CreateCode] [varchar](10) NOT NULL,
    [UpdateCode] [varchar](10) NOT NULL,
    [GroupPath] [varchar](850) NOT NULL,
    [Version] [int] NOT NULL,
    [КодФилиала] [varchar](80) NOT NULL,
    [УровеньКонфиденциальности] [int] NOT NULL,
    [ЭлементСБ] [bit] NOT NULL,
    [ЗамокЭлементаСБ] [bit] NOT NULL,
    [КодПодсистемы] [int] NOT NULL,
    [Код] [varchar](80) NOT NULL,
    [Имя] [varchar](300) NOT NULL,
    [КлассЗаписи] [varchar](250) NOT NULL,
    [ТекстКомментария] [text] NULL,
    [МогутБытьТипичнымНапрДеятельности] [bit] NOT NULL,
    [МетодикаПартионногоУчета] [int] NOT NULL,
    [РазрезыРасстановкиПартий] [varchar](250) NOT NULL,
    [УсловиеРасстановкиПартий] [varchar](8000) NOT NULL,
    [ИндивидуальнаянастройкаПриоритетаПартии] [bit] NOT NULL,
    [ПолеПриоритетаПартии] [varchar](250) NOT NULL,
    [КомментарийУсловиеРасстановкиПартий] [varchar](250) NOT NULL,
    [ИнвентаризацияПоСотрудникам] [bit] NOT NULL,
    [ИнвентаризацияПоПартиям] [bit] NOT NULL,
    [ИнвентаризацияПоЕдИзм] [bit] NOT NULL,
    [ИнвентаризацияПоСБК] [bit] NOT NULL,
    [ИнвентаризацияПоСубъектам] [bit] NOT NULL,
    [ИнвентаризацияПоМодификациям] [bit] NOT NULL,
    [АвтоБалансировка] [bit] NOT NULL,
    [НадпПроцесс] [varchar](250) NOT NULL,
    [ПрефиксПроцесса] [varchar](10) NOT NULL,
    [ПостфиксПроцесса] [varchar](10) NOT NULL,
    [ВысотаВерхнегоФрейма] [float] NOT NULL,
    [ВерхнееПредставление] [int] NOT NULL,
    [МастерСоздания] [int] NOT NULL,
    [ПоказыватьНавигатор] [bit] NOT NULL,
    [ПоказыватьКнопкуОК] [bit] NOT NULL,
    [ПоказыватьКнопкуОтмена] [bit] NOT NULL,
    [ПоказыватьКнопкуЗакрыть] [bit] NOT NULL,
    [ПоказыватьКнопкуПрименить] [bit] NOT NULL,
    [ПоказыватьКнопкуПомощь] [bit] NOT NULL,
    [РеквизитПериод] [int] NOT NULL,
    [РеквизитДатаНачала] [int] NOT NULL,
    [РеквизитДатаОкончания] [int] NOT NULL,
    [ОтображатьВЗакладкеПроцесса] [int] NOT NULL,
    [БухСчет] [varchar](100) NOT NULL,
    [БухСчетФункцииИмеетПриоритет] [bit] NOT NULL,
    [ВалютаПроцессаИмеетПриоритет] [bit] NOT NULL,
    [ОткрыватьПроцессВОтдельномОкне] [int] NOT NULL,
    [ПодбиратьОкно] [bit] NOT NULL,
    [СохранятьПередПечатью] [bit] NOT NULL,
    [НеФиксироватьИсториюПереходов] [bit] NOT NULL,
    [НеОбновлятьИсториюИзмененияВажности] [bit] NOT NULL,
    [ЕдИзмДляИнвентаризации] [int] NOT NULL,
    [ПоддерживаетсяШтрихСканер] [bit] NOT NULL,
    [БратьНастройкиИзДругогоТипаПроцесса] [bit] NOT NULL,
    [ТипПроцессаДляНастроек] [int] NOT NULL,
    [НеФиксироватьНулевуюНормЦену] [bit] NOT NULL,
    [ТемаПомощи] [varchar](250) NOT NULL,
    [ОткрыватьКартотекуТиповПроцессовБезИерархии] [bit] NOT NULL,
    [ДляСозданияПроцессаИспользоватьПодфункции] [bit] NOT NULL,
    [ТиповаяОперация] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [DocID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END

CREATE TABLE [dbo].[Документы_Позиции](
    [LinkDetail] [int] NOT NULL,
    [Index] [int] NOT NULL,
    [LinkMaster] [int] NOT NULL,
    [ТипДвижения] [int] NOT NULL,
    [Статус] [int] NOT NULL,
    [Комментарий] [varchar](8000) NOT NULL,
    [ПроцессОткуда] [int] NOT NULL,
    [ПроцессКуда] [int] NOT NULL,
    [РесурсОткуда] [int] NOT NULL,
    [РесурсКуда] [int] NOT NULL,
    [КоличОткуда] [float] NOT NULL,
    [КоличКуда] [float] NOT NULL,
    [Колич2Откуда] [float] NOT NULL,
    [Колич2Куда] [float] NOT NULL,
    [ЕдИзмОткуда] [int] NOT NULL,
    [ЕдИзмКуда] [int] NOT NULL,
    [ЕдИзм2Откуда] [int] NOT NULL,
    [ЕдИзм2Куда] [int] NOT NULL,
    [ПроцессПапкаОткуда] [int] NOT NULL,
    [ПроцессПапкаКуда] [int] NOT NULL,
    [СубъектОткуда] [int] NOT NULL,
    [СубъектКуда] [int] NOT NULL,
    [МодификацияОткуда] [int] NOT NULL,
    [МодификацияКуда] [int] NOT NULL,
    [СерНомерОткуда] [int] NOT NULL,
    [СерНомерКуда] [int] NOT NULL,
    [СерийныйНомер] [int] NOT NULL,
    [СтавкаНДС] [varchar](5) NOT NULL,
    [СуммаНДС] [float] NOT NULL,
    [ВалютаНДС] [int] NOT NULL,
    [СуммаАкциз] [float] NOT NULL,
    [СотрудникОткуда] [int] NOT NULL,
    [СотрудникКуда] [int] NOT NULL,
    [ДатаДвижения] [datetime] NOT NULL,
    [УчЦенаОткуда] [int] NOT NULL,
    [УчЦенаКуда] [int] NOT NULL,
    [УчЦена2Откуда] [int] NOT NULL,
    [УчЦена2Куда] [int] NOT NULL,
    [УчСтоимостьОткуда] [float] NOT NULL,
    [УчСтоимостьКуда] [float] NOT NULL,
    [УчВалюта] [int] NOT NULL,
    [УчСтоимость2Откуда] [float] NOT NULL,
    [УчСтоимость2Куда] [float] NOT NULL,
    [УчВалюта2] [int] NOT NULL,
    [УчСуммаИзДокумента] [bit] NOT NULL,
    [УчСуммаИзДокумента2] [bit] NOT NULL,
    [ФильтрРесурсовДляРазнесенияЗатрат] [varchar](850) NOT NULL,
    [Партия] [int] NOT NULL,
    [ПартияВручную] [bit] NOT NULL,
    [ЦФО] [int] NOT NULL,
    [СБК] [int] NOT NULL,
    [ВидДеятельности] [int] NOT NULL,
    [ГТД] [int] NOT NULL,
    [ВидРезерва] [int] NOT NULL,
    [ДрайверЗатрат] [int] NOT NULL,
    [ПозицияРазнесена] [int] NOT NULL,
    [БалСтатьяОткуда] [int] NOT NULL,
    [БалСтатьяКуда] [int] NOT NULL,
    [обРесурсОткуда] [int] NOT NULL,
    [обРесурсКуда] [int] NOT NULL,
    [обМестоположение] [int] NOT NULL,
    [ПозицияРезерв] [int] NOT NULL,
    [ПроцессПапкаКудаРезерв] [int] NOT NULL,
    [ПроцессПапкаОткудаРезерв] [int] NOT NULL,
    [ЗаказРезерв] [int] NOT NULL,
    [Заказ] [int] NOT NULL,
    [ПартияАвтоматически] [bit] NOT NULL,
    [КодЗаписиСРазнесеннымиЗатратами] [varchar](850) NOT NULL,
    [ЗаписьСРазнесеннымиЗатратами] [int] NOT NULL,
    [ОтгрузкаОткуда] [int] NOT NULL,
    [ОтгрузкаКуда] [int] NOT NULL,
    [ДокументРезерва] [int] NOT NULL,
    [СуммаКомиссии] [float] NOT NULL,
    [ЕдИзмКомиссии] [int] NOT NULL,
    [Брэнд] [int] NOT NULL,
    [НомерМеста] [int] NOT NULL,
    [ПредприятиеОткуда] [int] NOT NULL,
    [ПредприятиеКуда] [int] NOT NULL,
    [Состояние] [int] NOT NULL,
    [ДатаУстановкиСкидки] [datetime] NOT NULL,
    [ЗаказПроцесс] [int] NOT NULL,
    [ПозФлаг1] [bit] NOT NULL,
    [ПозФлаг2] [bit] NOT NULL,
    [Поз_Число1] [float] NOT NULL,
    [Поз_Число2] [float] NOT NULL,
    [ТоварноеНаправление] [int] NOT NULL,
    [ДопПризнак] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [LinkDetail] ASC,
    [Index] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END


CREATE TABLE [dbo].[Процесс](
    [DocID] [int] NOT NULL,
    [ExtID] [varchar](14) NOT NULL,
    [Deleted] [int] NOT NULL,
    [ModifyDate] [datetime] NOT NULL,
    [GroupDoc] [int] NOT NULL,
    [isGroup] [int] NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    [CreateUser] [varchar](40) NOT NULL,
    [UpdateDate] [datetime] NOT NULL,
    [UpdateUser] [varchar](40) NOT NULL,
    [CreateCode] [varchar](10) NOT NULL,
    [UpdateCode] [varchar](10) NOT NULL,
    [GroupPath] [varchar](850) NOT NULL,
    [Version] [int] NOT NULL,
    [КодФилиала] [varchar](80) NOT NULL,
    [УровеньКонфиденциальности] [int] NOT NULL,
    [ЭлементСБ] [bit] NOT NULL,
    [ЗамокЭлементаСБ] [bit] NOT NULL,
    [КодПодсистемы] [int] NOT NULL,
    [Код] [varchar](80) NOT NULL,
    [Имя] [varchar](300) NOT NULL,
    [ТипПроцесса] [int] NOT NULL,
    [Замок] [bit] NOT NULL,
    [НеОтображаетсяВДереве] [bit] NOT NULL,
    [Завершен] [bit] NOT NULL,
    [Комментарий] [varchar](8000) NOT NULL,
    [ДатаНачала] [datetime] NOT NULL,
    [ДатаВыверки] [datetime] NOT NULL,
    [ПечатныйНомер] [varchar](80) NOT NULL,
    [НеЯвляетсяАналитикой] [bit] NOT NULL,
    [НеПорождаетПроводки] [bit] NOT NULL,
    [ОбщСтатус] [int] NOT NULL,
    [НашеПредприятие] [int] NOT NULL,
    [НашеПодразделение] [int] NOT NULL,
    [Инициатор] [int] NOT NULL,
    [Менеджер] [int] NOT NULL,
    [Исполнитель] [int] NOT NULL,
    [Проверяющий] [int] NOT NULL,
    [Контрагент] [int] NOT NULL,
    [ПредставительКонтрагента] [int] NOT NULL,
    [Посредник] [int] NOT NULL,
    [ПредставительПосредника] [int] NOT NULL,
    [Ответственный] [int] NOT NULL,
    [Местоположение] [int] NOT NULL,
    [Заказ] [int] NOT NULL,
    [Основание] [int] NOT NULL,
    [РеквизитСозданияОснования] [int] NOT NULL,
    [ОтключитьОкругления] [bit] NOT NULL,
    [ПроцессМенеджер] [int] NOT NULL,
    [ПроцессПодразделение] [int] NOT NULL,
    [ОбщПроцессОткуда] [int] NOT NULL,
    [ОбщПроцессКуда] [int] NOT NULL,
    [СуммаКолОткуда] [float] NOT NULL,
    [СуммаКолКуда] [float] NOT NULL,
    [ОбщЕдИзмОткуда] [int] NOT NULL,
    [ОбщЕдИзмКуда] [int] NOT NULL,
    [СуммаКол2Откуда] [float] NOT NULL,
    [СуммаКол2Куда] [float] NOT NULL,
    [ОбщЕдИзм2Откуда] [int] NOT NULL,
    [ОбщЕдИзм2Куда] [int] NOT NULL,
    [ЭтоПереоценка] [bit] NOT NULL,
    [СозданАвтоматическиДляПереоценки] [bit] NOT NULL,
    [РезервДатаНачала] [datetime] NOT NULL,
    [РезервДатаОкончания] [datetime] NOT NULL,
    [ВидРезервирования] [int] NOT NULL,
    [БанковскиеРеквизиты] [int] NOT NULL,
    [РеквизитыБанкаПосредника] [int] NOT NULL,
    [ПроцессЗатрат] [int] NOT NULL,
    [ВалютаПроцесса] [int] NOT NULL,
    [ПланСрокЗавершения] [datetime] NOT NULL,
    [СрокГодности] [datetime] NOT NULL,
    [ВидКурсаРасчетов] [int] NOT NULL,
    [ДатаКурсаРасчетов] [datetime] NOT NULL,
    [ПроцентОтклонКурса] [float] NOT NULL,
    [ЗнакОтклонения] [int] NOT NULL,
    [ФиксированныйКурс] [float] NOT NULL,
    [ЭтоПартия] [bit] NOT NULL,
    [ЭтоЗаказ] [bit] NOT NULL,
    [ЭтоГТД] [bit] NOT NULL,
    [ЭтоСерийныйНомер] [bit] NOT NULL,
    [ЭтоСпецификация] [bit] NOT NULL,
    [ИзделиеПоСпецификации] [int] NOT NULL,
    [Спецификация] [int] NOT NULL,
    [ПереданВБухгалтерию] [bit] NOT NULL,
    [СтранаПроисхождения] [int] NOT NULL,
    [ЭтоПрайс] [bit] NOT NULL,
    [ЭтоЖесткийРезерв] [bit] NOT NULL,
    [Номер1] [varchar](80) NOT NULL,
    [Номер2] [varchar](80) NOT NULL,
    [Номер3] [varchar](80) NOT NULL,
    [Номер4] [varchar](80) NOT NULL,
    [Номер5] [varchar](80) NOT NULL,
    [Дата1] [datetime] NOT NULL,
    [Дата2] [datetime] NOT NULL,
    [Дата3] [datetime] NOT NULL,
    [Дата4] [datetime] NOT NULL,
    [Дата5] [datetime] NOT NULL,
    [Число1] [float] NOT NULL,
    [Число2] [float] NOT NULL,
    [Объект1] [int] NOT NULL,
    [Объект2] [int] NOT NULL,
    [Объект3] [int] NOT NULL,
    [Объект4] [int] NOT NULL,
    [Объект5] [int] NOT NULL,
    [БалСтатья] [int] NOT NULL,
    [СостояниеПроцесса] [int] NOT NULL,
    [Важность] [int] NOT NULL,
    [КемУстановленПриоритет] [int] NOT NULL,
    [ТМЦ] [int] NOT NULL,
    [ОтключитьАвторезервирование] [bit] NOT NULL,
    [ИзменяетСостояниеДляПроцесса] [int] NOT NULL,
    [ИзСостояния] [int] NOT NULL,
    [ВСостояние] [int] NOT NULL,
    [СпособХранения] [int] NOT NULL,
    [ПриложенныйФайл] [image] NULL,
    [ИмяПриложенногоФайла] [varchar](250) NOT NULL,
    [РазмерПриложенногоФайла] [int] NOT NULL,
    [ДатаПриложенногоФайла] [datetime] NOT NULL,
    [АбсолютноеИмяФайла] [varchar](850) NOT NULL,
    [ПриложенныйФайлОбщий] [int] NOT NULL,
    [ВариантУчета] [int] NOT NULL,
    [ОснованиеЗаявка] [int] NOT NULL,
    [ОснованиеРезерв] [int] NOT NULL,
    [ЗаказРезерв] [int] NOT NULL,
    [Категория] [int] NOT NULL,
    [ШтрихКод] [varchar](80) NOT NULL,
    [ПриоритетРасстановкиПартий] [int] NOT NULL,
    [Флаг1] [bit] NOT NULL,
    [Флаг2] [bit] NOT NULL,
    [Флаг3] [bit] NOT NULL,
    [Цвет] [int] NOT NULL,
    [пка_ТипОплаты] [int] NOT NULL,
    [пка_ТипДоставки] [int] NOT NULL,
    [пка_ТранспортнаяКомпания] [int] NOT NULL,
    [СпособКонтакта] [int] NOT NULL,
    [ТипСобытия] [int] NOT NULL,
    [ТипДопИнфо] [int] NOT NULL,
    [РекламныйИсточник] [int] NOT NULL,
    [ДокументыВозвращены] [bit] NOT NULL,
    [НабратьК] [datetime] NOT NULL,
    [ЗабратьВозврат] [bit] NOT NULL,
    [ЗабратьДолг] [bit] NOT NULL,
    [Наборщик] [int] NOT NULL,
    [ВремяСборкиС] [datetime] NOT NULL,
    [ВремяСборкиПо] [datetime] NOT NULL,
    [Отгрузил] [int] NOT NULL,
    [НачалоПребыванияВЗонеОтгрузки] [datetime] NOT NULL,
    [ОкончаниеПребыванияВЗонеОтгрузки] [datetime] NOT NULL,
    [Маршрут] [int] NOT NULL,
    [ВодительЭкспедитор] [int] NOT NULL,
    [Перевозчик] [int] NOT NULL,
    [КомментарийСклада] [text] NULL,
    [КомментарийВодителя] [text] NULL,
    [ТипОтгрузки] [int] NOT NULL,
    [ТипОплаты] [int] NOT NULL,
    [ФормаОплаты] [int] NOT NULL,
    [АдресДоставки] [int] NOT NULL,
    [ВремяДоставки] [datetime] NOT NULL,
    [АдресПерегруза] [varchar](250) NOT NULL,
    [КонтактПерегруза] [varchar](250) NOT NULL,
    [Прайс] [int] NOT NULL,
    [ВидДеятельности] [int] NOT NULL,
    [СБК] [int] NOT NULL,
    [СБК_CF] [int] NOT NULL,
    [ПравилоCF] [int] NOT NULL,
    [ПравилоPL] [int] NOT NULL,
    [МаршрутС] [int] NOT NULL,
    [Выезд] [int] NOT NULL,
    [АдресаДоставки] [int] NOT NULL,
    [КонечныйАдрес] [int] NOT NULL,
    [Автомобиль] [int] NOT NULL,
    [ВремяИзмененияСостояния] [datetime] NOT NULL,
    [ИзменившийСостояние] [varchar](80) NOT NULL,
    [НомерЗаказа] [int] NOT NULL,
    [ПерепечататьДокументы] [bit] NOT NULL,
    [СуммаЛимита] [float] NOT NULL,
    [ДатаДействияЛимита] [datetime] NOT NULL,
    [ТипОтгрузкиСсылка] [int] NOT NULL,
    [ВыгружатьВ1С] [bit] NOT NULL,
    [Номер6] [varchar](80) NOT NULL,
    [Номер7] [varchar](80) NOT NULL,
    [Дата6] [datetime] NOT NULL,
    [Дата7] [datetime] NOT NULL,
    [Флаг4] [bit] NOT NULL,
    [Флаг5] [bit] NOT NULL,
    [Флаг6] [bit] NOT NULL,
    [ОбщийВес] [float] NOT NULL,
    [ОбщийОбъем] [float] NOT NULL,
    [Число3] [float] NOT NULL,
    [Раскрыта] [bit] NOT NULL,
    [Название] [varchar](250) NOT NULL,
    [ЗаказПроцесс] [int] NOT NULL,
    [ОснованиеПроцесс] [int] NOT NULL,
    [МенеджерСМ] [int] NOT NULL,
    [Флаг7] [bit] NOT NULL,
    [Флаг8] [bit] NOT NULL,
    [Флаг9] [bit] NOT NULL,
    [Флаг10] [bit] NOT NULL,
    [РаботаСНДС] [bit] NOT NULL,
    [ПроцессНДС] [int] NOT NULL,
    [ПроцессНДС2] [int] NOT NULL,
    [ОснованиеВозврата] [int] NOT NULL,
    [AttachmentID] [int] NOT NULL,
    [СостояниеОтгрузки] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [DocID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END

IO and time execution characteristics:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(100 rows affected)
Table 'Документы'. Scan count 18, logical reads 2017, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Функция'. Scan count 0, logical reads 250, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Процесс'. Scan count 0, logical reads 609048, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Документы_Позиции'. Scan count 3805, logical reads 27249, physical reads 0, read-ahead reads 109, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 514 ms,  elapsed time = 155 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
  • How does it run if you add OPTION(QUERYTRACEON 4138) to the end? – Erik Darling Jun 7 '18 at 12:28
  • @sp_BlitzErik, the plan changed: brentozar.com/pastetheplan/?id=HyRUaoLlm CPU and elapsed times are: CPU time = 375 ms, elapsed time = 446 ms with option CPU time = 720 ms, elapsed time = 184 ms without option – George K Jun 7 '18 at 13:05
  • 2
    Okay, so dumb question: How much faster than 155ms do you want to make this thing? – Erik Darling Jun 7 '18 at 13:12
  • @sp_BlitzErik, well if there is something that can be done, I'd prefer to try. If not - okay then... – George K Jun 7 '18 at 13:20
  • 1
    @GeorgeK at the end of query just before ; – Kin Shah Jun 7 '18 at 14:56
0

You can try to eliminate the Sort costing 21% by adding a Non Clustered index to [Документы_Позиции] that will extract all required data in the order that will match it appropriately against [Процесс] A144JTA127_1

CREATE NONCLUSTERED INDEX idx ON [Документы_Позиции]([ПроцессОткуда],[ПроцессКуда])INCLUDE([Index]) 

Look at this article by Erik Darling for an explanation of where the expensive sort is coming from.

  • Well, adding the index did not have any effect on execution time... – George K Jun 8 '18 at 13:31
  • @GeorgeK did it alter the IO reads on 'Документы_Позиции'. Also are you still getting a Clustered Index Seek? I was hoping for an Index Seek, are there any columns I've missed to make it a covering Index? – pacreely Jun 8 '18 at 13:42
  • @GeorgeK my apologies, I missed a column when I was reading the query. I've updated the proposed Index with the extra column. – pacreely Jun 8 '18 at 13:57
  • @GeorgeK in theory you shouldn't have to add [LinkDetail] to the index, but if the Optimiser still does the Clustered Index Seek then it's worth adding it to the NonClustered index. – pacreely Jun 8 '18 at 14:33
0

In given query, all the parameters are Hard coded and only TOP 100 is require so it execute very fast. Also query Plan look fine because of this reason.

If same query is executed using Parameter then only its true performance will be reflected.

Then only very Honest Index Tuning can be done on it.

there is no option to change the query text.

As per user query cannot be change. IMO, Query change is badly needed here.

I think in this condition Filtered Index is safe bet.

Since this query is very important and isDeleted=0 contain maximum records. Instead of Creating Index on [БЕРГ].[dbo].[Документы].[Deleted] change the same index to Filtered Index i.e. isDeleted=0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.