Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 375
Добрый день
Есть две таблицы: inputtable с большим количеством (около 100) полей и записей
и таблица Tasks (5 полей) 7 записей
таблицы связаны по inputtable.Taskid =Tasks.Id

На таблице inputtable есть некластерный индекс IX_TaskId построенный по полю inputtable.TaskId с включенными в него полями InsertDate,DeclTypeNx
tOrderNx является первичным ключом inputtable

Делаю запрос
Select TAsks.name, tOrderNX,InsertDate,DeclTypeNx from inputtable		
JOIN Tasks  ON Tasks.id = inputtable.TaskId

Получаю следующий план выполнения:
Картинка с другого сайта.

Вопрос: В индекс IX_TaskId я включил поля (InsertDate,DeclTypeNx) используемые данным запросом, но сервер почему то упорно пытается использовать keylookup и кластерный индекс для их получения. Почему так?

К сообщению приложен файл. Размер - 54Kb
29 мар 17, 12:21    [20345625]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 942
Алексей,
потому что в выборке поле, которого в индексе нет...
29 мар 17, 12:23    [20345646]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Алексей,

tOrderNX в какой таблице? и индекс можно не пересказывать, а показывать создание
29 мар 17, 12:24    [20345658]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 375
В догонку
Если пишу запрос
Select TAsks.name, tOrderNX,InsertDate,DeclTypeNx from inputtable WITH(INDEX(IX_TaskId))
										JOIN Tasks  ON Tasks.id = inputtable.TaskId


Все равно используется предыдущий план выполнения
29 мар 17, 12:31    [20345707]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 375
TaPaK,



Руслан Дамирович,

Про какое поле идет речь?
29 мар 17, 12:32    [20345713]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 375
TaPaK,

inputtable
29 мар 17, 12:32    [20345714]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Алексей
TaPaK,

inputtable
вы уже ответили на свой вопрос?
29 мар 17, 12:32    [20345719]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
Алексей
В индекс IX_TaskId я включил поля (InsertDate,DeclTypeNx) используемые данным запросом
Либо таки не включили или включили не все. Либо tOrderNx не ключ кластерного.
29 мар 17, 12:33    [20345727]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 375
TaPaK,

tOrderNx PK inputtable

Показывать создание так?
ALTER TABLE [dbo].[inputtable] ADD  CONSTRAINT [PK_inputtable] PRIMARY KEY CLUSTERED 
(
	[tOrderNx] ASC
)
29 мар 17, 12:34    [20345735]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Алексей,

давайте выясним: а при чём здесь PK и вообще остальные индексы?
29 мар 17, 12:34    [20345739]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 942
Алексей,

Еще раз повторяю, ты указываешь в SELECT поля, которых нет в индексе, и считаешь, что сервер должен отказаться от поиска по кластерному индексу?

Выкладывай CREATE TABLE и все индексы - можно будет предметно помогать, а не на кофейной гуще гадать.
29 мар 17, 12:36    [20345751]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Если вы хотите что бы лезло в PK так вон оно и лезет лукапом, если хотите чтобы работал запрос полностью на вашем некластерном, так добавьте в INCLUDE недостающее поле
29 мар 17, 12:36    [20345753]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 375
TaPaK,
на таблице inputtable создан индекс
CREATE NONCLUSTERED INDEX [IX_TaskId] ON [dbo].[inputtable]
(
	[TaskId] ASC
)
INCLUDE ( 	[InsertDate],
	[DeclTypeNx],
	[u_owner])

Для того чтобы попытаться избавиться от KeyLookup на плане выполнения.
А PK Потому что Руслан Домирович наверное имел ввиду что он не включен в некласттерный индекс, но этого и не требуется( он там уже есть)
Как то так.....
29 мар 17, 12:39    [20345777]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Алексей,

беда прям... tOrderNx ДЭ,
29 мар 17, 12:40    [20345785]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 375
Таблицы
CREATE TABLE [dbo].[inputtable](
	[tOrderNx] [int] IDENTITY(1,1) NOT NULL,
	[InsertDate] [datetime] NULL CONSTRAINT [col_InsertDate]  DEFAULT (getdate()),
...
	[TaskId] [int] NULL,
 CONSTRAINT [PK_inputtable] PRIMARY KEY CLUSTERED 
(
	[tOrderNx] ASC
)


Таблица 2
CREATE TABLE [dbo].[Tasks](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](255) NULL,
	[typeTask] [nvarchar](20) NULL,
	[dateTask] [datetime] NULL,
	[dpBegin] [datetime] NULL,
	[dpEnd] [datetime] NULL,
	[TaskState] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)

В порядке эксперимента добавил поле, все равно лезет за этими полями через KeyLookup
CREATE NONCLUSTERED INDEX [IX_TaskId] ON [dbo].[inputtable]
(
	[TaskId] ASC
)
INCLUDE ( 	[InsertDate],
	[DeclTypeNx],
	[u_owner],
	[tOrderNx])


Версия сервера
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
Aug 19 2014 12:21:34
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
29 мар 17, 12:44    [20345819]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Алексей,

актуальный план файлом, моя вам не верит
29 мар 17, 12:54    [20345902]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 375
TaPaK,

+
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.6000.34" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1088.45" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="3.58447" StatementText="Select TAsks.name, tOrderNX,InsertDate,DeclTypeNx from inputtable WITH(INDEX(IX_TaskId))		&#xD;&#xA;										JOIN Tasks  ON Tasks.id = inputtable.TaskId" StatementType="SELECT" QueryHash="0xB923E4F2F9121BA8" QueryPlanHash="0xC44AD2AF23B2B528">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" MemoryGrant="1184" CachedPlanSize="32" CompileTime="3" CompileCPU="3" CompileMemory="264">
            <RelOp AvgRowSize="282" EstimateCPU="0.00454971" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1088.45" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="3.58447">
              <OutputList>
                <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[inputtable]" Column="tOrderNx" />
                <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[inputtable]" Column="InsertDate" />
                <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[inputtable]" Column="DeclTypeNx" />
                <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[Tasks]" Column="name" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="45728" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <NestedLoops Optimized="true" WithUnorderedPrefetch="true">
                <OuterReferences>
                  <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[inputtable]" Column="tOrderNx" />
                  <ColumnReference Column="Expr1006" />
                </OuterReferences>
                <RelOp AvgRowSize="270" EstimateCPU="0.00454971" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1088.45" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0142581">
                  <OutputList>
                    <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[inputtable]" Column="tOrderNx" />
                    <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[Tasks]" Column="name" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="45728" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <NestedLoops Optimized="false">
                    <OuterReferences>
                      <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[Tasks]" Column="id" />
                    </OuterReferences>
                    <RelOp AvgRowSize="270" EstimateCPU="0.0001647" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032897" TableCardinality="7">
                      <OutputList>
                        <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[Tasks]" Column="id" />
                        <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[Tasks]" Column="name" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="7" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[Tasks]" Column="id" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[Tasks]" Column="name" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[RossAccr_Stand]" Schema="[dbo]" Table="[Tasks]" Index="[PK__Tasks__3213E83F30592A6F]" IndexKind="Clustered" />
                      </IndexScan>
                    </RelOp>
                    <RelOp AvgRowSize="11" EstimateCPU="0.000328042" EstimateIO="0.003125" EstimateRebinds="5.4375" EstimateRewinds="0.5625" EstimateRows="155.492" LogicalOp="Index Seek" NodeId="5" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00641865" TableCardinality="511423">
                      <OutputList>
                        <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[inputtable]" Column="tOrderNx" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="45728" ActualEndOfScans="7" ActualExecutions="7" />
                      </RunTimeInformation>
                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[inputtable]" Column="tOrderNx" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[RossAccr_Stand]" Schema="[dbo]" Table="[inputtable]" Index="[IX_TaskId]" IndexKind="NonClustered" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[inputtable]" Column="TaskId" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[RossAccr_Stand].[dbo].[Tasks].[id]">
                                    <Identifier>
                                      <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[Tasks]" Column="id" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
                <RelOp AvgRowSize="19" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="1087.45" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="7" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="3.56566" TableCardinality="511423">
                  <OutputList>
                    <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[inputtable]" Column="InsertDate" />
                    <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[inputtable]" Column="DeclTypeNx" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="45728" ActualEndOfScans="0" ActualExecutions="45728" />
                  </RunTimeInformation>
                  <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[inputtable]" Column="InsertDate" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[inputtable]" Column="DeclTypeNx" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[RossAccr_Stand]" Schema="[dbo]" Table="[inputtable]" Index="[PK_inputtable]" TableReferenceId="-1" IndexKind="Clustered" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <Prefix ScanType="EQ">
                            <RangeColumns>
                              <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[inputtable]" Column="tOrderNx" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="[RossAccr_Stand].[dbo].[inputtable].[tOrderNx]">
                                <Identifier>
                                  <ColumnReference Database="[RossAccr_Stand]" Schema="[dbo]" Table="[inputtable]" Column="tOrderNx" />
                                </Identifier>
                              </ScalarOperator>
                            </RangeExpressions>
                          </Prefix>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
              </NestedLoops>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>


К сообщению приложен файл. Размер - 22Kb


Сообщение было отредактировано: 29 мар 17, 12:58
29 мар 17, 12:57    [20345932]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 375
Причем, понимает нормально

Select TAsks.name, tOrderNX from inputtable	
										JOIN Tasks  ON Tasks.id = inputtable.TaskId

Но как только обращаюсь к include полям, все возвращается на keylookup
по ходу он их не понимает

К сообщению приложен файл. Размер - 17Kb
29 мар 17, 13:00    [20345957]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Алексей,

в вашем некластерном есть только tOrderNX. Что и где вы создаёте уточните у себя
29 мар 17, 13:03    [20345984]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 375
TaPaK,

CREATE NONCLUSTERED INDEX [IX_TaskId] ON [dbo].[inputtable]
(
	[TaskId] ASC
)
INCLUDE ( 	[InsertDate],
	[DeclTypeNx],
	[u_owner],
	[tOrderNx])


А include столбцы?
29 мар 17, 13:10    [20346036]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Возможно, со статистикой проблемы. Судя по плану, он из некластерного индекса берёт только tOrderNX. Остальные поля предпочитает брать из кластерного. Видимо, считал, что так быстрее. Предполагалось одна строка. По факту 46 тыс.

Я создал у себя эти таблицы, забил тестовыми данными. У меня всё берёт из некластерного.

Идеи:

1. Во-первых
UPDATE STATISTICS inputtable(IX_TaskId) WITH FULLSCAN;
2. Во-вторых
Select TAsks.name, tOrderNX,InsertDate,DeclTypeNx from inputtable WITH(INDEX(IX_TaskId))	
JOIN Tasks  ON Tasks.id = inputtable.TaskId
OPTION(RECOMPILE);
29 мар 17, 13:27    [20346138]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Алексей
TaPaK,

CREATE NONCLUSTERED INDEX [IX_TaskId] ON [dbo].[inputtable]
(
	[TaskId] ASC
)
INCLUDE ( 	[InsertDate],
	[DeclTypeNx],
	[u_owner],
	[tOrderNx])


А include столбцы?
А запрос компилировали после этого? Или старый план остался?
29 мар 17, 13:29    [20346148]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 375
Dmitry V. Liseev,

Спасибо, похоже на то. Все заработало.
29 мар 17, 13:29    [20346149]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Алексей
Dmitry V. Liseev,

Спасибо, похоже на то. Все заработало.
Если что-то оптимизируете, в отладочных целях всегда добавляйте OPTION(RECOMPILE)
29 мар 17, 13:32    [20346161]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 375
Dmitry V. Liseev,

Спасибо за помощь
29 мар 17, 13:40    [20346209]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить