Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос недопонимаю немного план и статистику чтений  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Есть такой вот запрос
select  TOP(1) PachkaNum from zakaz.tbZakazDetail order by ID_ZakazDetail desc

Мне непонятно вот что, план показывает что идет Clustered Index Scan, почему же скан, если по идее все отсортировано и можно взять нужную страницу.

Для этого запроса
Таблица "tbZakazDetail". Число просмотров 1, логических чтений 2, физических чтений 0,
упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

 DBCC DROPCLEANBUFFERS
 select  TOP(10) PachkaNum from zakaz.tbZakazDetail order by ID_ZakazDetail desc

Для него
Таблица "tbZakazDetail". Число просмотров 1, логических чтений 4, физических чтений 1,
упреждающих чтений 498, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

sp_spaceused 'zakaz.tbZakazDetail'

reserved data index_size
tbZakazDetail 16793 4760 KB 3984 KB 520 KB 256 KB

В итоге в этом запросе мы что читаем практически всю таблицу(или всю)?

И будет ли этот запрос
select  TOP(1) PachkaNum from zakaz.tbZakazDetail order by ID_ZakazDetail desc

в дальнейшем давать давать мало чтений страниц, когда таблица станет ну например хотя бы 1ГБ.

Или в другом направлении мыслить.(Немного сумбурно, но отнеситесь с пониманием)
20 июн 12, 14:49    [12746283]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18328
Структуру таблички и количество записей в ней.
20 июн 12, 14:57    [12746354]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Так забыл план скинуть
C Top(1)

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.5057.00">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="select  TOP(1) PachkaNum from zakaz.tbZakazDetail order by ID_ZakazDetail desc" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0032832" StatementEstRows="1" StatementOptmLevel="TRIVIAL">
          <StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="12" CompileTime="111" CompileCPU="3" CompileMemory="152">
            <RelOp NodeId="0" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
              <OutputList>
                <ColumnReference Database="[ReferenceBook]" Schema="[zakaz]" Table="[tbZakazDetail]" Column="PachkaNum" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <Top RowCount="0" IsPercent="0" WithTies="0">
                <TopExpression>
                  <ScalarOperator ScalarString="(1)">
                    <Const ConstValue="(1)" />
                  </ScalarOperator>
                </TopExpression>
                <RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimateIO="0.371273" EstimateCPU="0.0186293" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0032831" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[ReferenceBook]" Schema="[zakaz]" Table="[tbZakazDetail]" Column="ID_ZakazDetail" />
                    <ColumnReference Database="[ReferenceBook]" Schema="[zakaz]" Table="[tbZakazDetail]" Column="PachkaNum" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
                  </RunTimeInformation>
                  <IndexScan Ordered="1" ScanDirection="BACKWARD" ForcedIndex="0" NoExpandHint="0">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[ReferenceBook]" Schema="[zakaz]" Table="[tbZakazDetail]" Column="ID_ZakazDetail" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[ReferenceBook]" Schema="[zakaz]" Table="[tbZakazDetail]" Column="PachkaNum" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[ReferenceBook]" Schema="[zakaz]" Table="[tbZakazDetail]" Index="[PK_tbZakazDetail_1]" />
                  </IndexScan>
                </RelOp>
              </Top>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>


Структура
USE [ReferenceBook]
GO
/****** Объект:  Table [zakaz].[tbZakazDetail]    Дата сценария: 06/20/2012 14:59:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [zakaz].[tbZakazDetail](
	[ID_ZakazDetail] [int] IDENTITY(1,1) NOT NULL,
	[Position] [smallint] NOT NULL,
	[ProductForm] [int] NOT NULL,
	[Thickness] [decimal](8, 3) NOT NULL,
	[Width] [decimal](6, 2) NULL,
	[Length] [varchar](32) NULL,
	[AdditionalSpecific] [varchar](2048) NULL,
	[ZakazDateTime] [smalldatetime] NULL,
	[ManufactureDate] [smalldatetime] NULL,
	[CostInValuta] [nsi].[IzhMoney] NOT NULL,
	[PachkaNum] [int] NULL,
	[ZakPotrSpecific] [varchar](32) NULL,
	[DogovorNum] [varchar](32) NULL,
	[Nomenclature] [int] NULL,
	[ID_ZakazHeader_FK] [int] NOT NULL,
	[ID_MetalMark_FK] [int] NOT NULL,
	[ID_Profile_FK] [int] NULL,
	[ID_FondHolder_FK] [int] NOT NULL CONSTRAINT [DF_tbZakazDetail_ID_CodeFond_FK]  DEFAULT ((0)),
	[ID_PredprConsumer_FK] [bigint] NULL,
	[ID_PredprConsumer_error] [bigint] NULL,
	[ID_Station_FK] [int] NOT NULL,
	[ID_Ceh_FK] [int] NOT NULL,
	[ID_Stan_FK] [int] NOT NULL,
	[ID_Sklad_FK] [int] NULL,
	[ID_TechCard_FK] [int] NULL,
	[ID_UslPostav_FK] [int] NULL,
	[ID_TechHar_FK] [int] NULL,
	[ID_ShifrNazn_FK] [int] NULL,
	[ID_Valuta_FK] [int] NOT NULL,
	[ID_ShipmentMode_FK] [int] NOT NULL,
	[ID_PeredelNazn_FK] [int] NULL,
	[ID_TerOrgan_FK] [int] NULL,
	[RowCreateDate] [datetime] NOT NULL CONSTRAINT [DF_tbZakazDetail_RowCreateDate]  DEFAULT (getdate()),
	[RowCreateBy] [varchar](32) NOT NULL CONSTRAINT [DF_tbZakazDetail_RowCreateBy]  DEFAULT (suser_name()),
	[RowLastUpdateDate] [datetime] NULL CONSTRAINT [DF_tbZakazDetail_RowLastUpdateDate]  DEFAULT (getdate()),
	[RowLastUpdateBy] [varchar](32) NULL CONSTRAINT [DF_tbZakazDetail_RowLastUpdateBy]  DEFAULT (suser_name()),
	[ID_PredprConsumer_FK_compute]  AS (coalesce([ID_PredprConsumer_FK],[ID_PredprConsumer_error])) PERSISTED,
 CONSTRAINT [PK_tbZakazDetail_1] PRIMARY KEY CLUSTERED 
(
	[ID_ZakazDetail] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

/****** Объект:  Index [index_RowCreateDate_tbZakazDetail]    Дата сценария: 06/20/2012 14:59:52 ******/
CREATE NONCLUSTERED INDEX [index_RowCreateDate_tbZakazDetail] ON [zakaz].[tbZakazDetail] 
(
	[RowCreateDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/****** Объект:  Index [uniq_tbZakazDetail]    Дата сценария: 06/20/2012 14:59:52 ******/
CREATE UNIQUE NONCLUSTERED INDEX [uniq_tbZakazDetail] ON [zakaz].[tbZakazDetail] 
(
	[ID_ZakazHeader_FK] ASC,
	[Position] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'remark', @value=N'ID_Valuta_FK не PK из другой таблицы' , @level0type=N'SCHEMA',@level0name=N'zakaz', @level1type=N'TABLE',@level1name=N'tbZakazDetail'
GO
ALTER TABLE [zakaz].[tbZakazDetail]  WITH CHECK ADD  CONSTRAINT [FK_tbZakazDetail_tbFondHolder] FOREIGN KEY([ID_FondHolder_FK])
REFERENCES [nsi].[tbFondHolder] ([ID_FondHolder])
GO
ALTER TABLE [zakaz].[tbZakazDetail] CHECK CONSTRAINT [FK_tbZakazDetail_tbFondHolder]
GO
ALTER TABLE [zakaz].[tbZakazDetail]  WITH CHECK ADD  CONSTRAINT [FK_tbZakazDetail_tbPredpr] FOREIGN KEY([ID_PredprConsumer_FK])
REFERENCES [predpr].[tbPredpr] ([ID_Predpr])
GO
ALTER TABLE [zakaz].[tbZakazDetail] CHECK CONSTRAINT [FK_tbZakazDetail_tbPredpr]
GO
ALTER TABLE [zakaz].[tbZakazDetail]  WITH CHECK ADD  CONSTRAINT [FK_tbZakazDetail_tbProfile] FOREIGN KEY([ID_Profile_FK])
REFERENCES [nsi].[tbProfile] ([ID_Profile])
GO
ALTER TABLE [zakaz].[tbZakazDetail] CHECK CONSTRAINT [FK_tbZakazDetail_tbProfile]
GO
ALTER TABLE [zakaz].[tbZakazDetail]  WITH CHECK ADD  CONSTRAINT [FK_tbZakazDetail_tbRailRoadStation] FOREIGN KEY([ID_Station_FK])
REFERENCES [nsi].[tbRailRoadStation] ([ID_Station])
GO
ALTER TABLE [zakaz].[tbZakazDetail] CHECK CONSTRAINT [FK_tbZakazDetail_tbRailRoadStation]
GO
ALTER TABLE [zakaz].[tbZakazDetail]  WITH CHECK ADD  CONSTRAINT [FK_tbZakazDetail_tbValuta] FOREIGN KEY([ID_Valuta_FK])
REFERENCES [nsi].[tbValuta] ([ID_Valuta])
GO
ALTER TABLE [zakaz].[tbZakazDetail] CHECK CONSTRAINT [FK_tbZakazDetail_tbValuta]
GO
ALTER TABLE [zakaz].[tbZakazDetail]  WITH CHECK ADD  CONSTRAINT [FK_tbZakazDetail_tbZakazHeader] FOREIGN KEY([ID_ZakazHeader_FK])
REFERENCES [zakaz].[tbZakazHeader] ([ID_ZakazHeader])
GO
ALTER TABLE [zakaz].[tbZakazDetail] CHECK CONSTRAINT [FK_tbZakazDetail_tbZakazHeader]

Количество строк
16793
20 июн 12, 15:03    [12746405]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
Glory
Member

Откуда:
Сообщений: 104751
hosTuk
что идет Clustered Index Scan, почему же скан, если по идее все отсортировано и можно взять нужную страницу.


<IndexScan Ordered="1" ScanDirection="BACKWARD" ForcedIndex="0" NoExpandHint="0">

А как еще назвать операцию чтения индекса, которая начинается с его первой страницы ?
20 июн 12, 15:14    [12746496]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
а план запроса с top 10 ? И вообще статистику обновить по этой таблице.
20 июн 12, 15:16    [12746514]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Glory
hosTuk
что идет Clustered Index Scan, почему же скан, если по идее все отсортировано и можно взять нужную страницу.


<IndexScan Ordered="1" ScanDirection="BACKWARD" ForcedIndex="0" NoExpandHint="0">

А как еще назвать операцию чтения индекса, которая начинается с его первой страницы ?

Точно, направления сортировки в запросе и индексе не совпадают
20 июн 12, 15:20    [12746539]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
Glory
Member

Откуда:
Сообщений: 104751
Мистер Хенки
Точно, направления сортировки в запросе и индексе не совпадают

Все там совпадает
Сервер намерем сканировать, т.е. читать страницы кластернего индекса, но в обратном порядке. Читать он будет столько страниц, сколько нужно для получения указанных записей - EstimateRows="1"
20 июн 12, 15:27    [12746603]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Glory
Мистер Хенки
Точно, направления сортировки в запросе и индексе не совпадают

Все там совпадает
Сервер намерем сканировать, т.е. читать страницы кластернего индекса, но в обратном порядке. Читать он будет столько страниц, сколько нужно для получения указанных записей - EstimateRows="1"

а, понял
20 июн 12, 15:42    [12746759]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Glory,

Т.е. запрос с Тоp(10) читает всю таблицу? В итоге получается, что данные должны лежать рядом и не должны читаться все страницы таблицы. Конечно возможно фрагментация, но
select * from sys.dm_db_index_physical_stats(db_id('ReferenceBook'),object_id(N'zakaz.tbZakazDetail'),null, null, null)


avg_fragmentation_in_percent=0

Для верхнего и для нижнего уровня кластерного индекса, в чем подвох
20 июн 12, 15:47    [12746794]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Точнее так
select * from sys.dm_db_index_physical_stats(db_id('ReferenceBook'),object_id(N'zakaz.tbZakazDetail'),null, null, 'DETAILED')
20 июн 12, 15:50    [12746821]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
Glory
Member

Откуда:
Сообщений: 104751
hosTuk
Т.е. запрос с Тоp(10) читает всю таблицу?

Почему всею то ? У вас 10 записей занимают всю страницы что ли ?
20 июн 12, 15:50    [12746822]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Glory,

set statistics io on
 DBCC DROPCLEANBUFFERS
go
select  TOP(10) PachkaNum from zakaz.tbZakazDetail order by ID_ZakazDetail desc


Результаты IO
Таблица "tbZakazDetail". Число просмотров 1, логических чтений 4, физических чтений 1,
упреждающих чтений 498, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.


sp_spaceused для таблицы колонка data дает 3984 KB, т.е. 3984 / 8 = 498 страницы

Если ничего не напутал, читаем всю таблицу.
20 июн 12, 15:55    [12746882]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
Glory
Member

Откуда:
Сообщений: 104751
hosTuk
Если ничего не напутал, читаем всю таблицу.

Так это упреждающие чтения.
логических чтений то 4
20 июн 12, 15:58    [12746914]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Glory,

Но откуда-то он их взял и прочитал(с диска всю таблицу и в кеш, зачем??? или это для будущей оптимизации вдруг что, непонятно...)
20 июн 12, 16:01    [12746955]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Либо мне не ясен смысл
BOL
упреждающих чтений
Число страниц, помещенных в кэш для запроса.
20 июн 12, 16:03    [12746969]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
hosTuk
Glory,

Но откуда-то он их взял и прочитал(с диска всю таблицу и в кеш, зачем??? или это для будущей оптимизации вдруг что, непонятно...)


Reading Pages ( ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_0evalplan/html/fb83d250-4252-4322-b57c-3c72de8407c8.htm )
The Database Engine supports a performance optimization mechanism called read-ahead. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. This allows computation and I/O to overlap, taking full advantage of both the CPU and the disk.
20 июн 12, 16:03    [12746971]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Гавриленко Сергей Алексеевич,

Ок, спасибо, все понятно
20 июн 12, 16:20    [12747101]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
hosTuk
Member

Откуда:
Сообщений: 455
Хитро одним словом
20 июн 12, 16:20    [12747104]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
Glory
Member

Откуда:
Сообщений: 104751
hosTuk
Хитро одним словом

1. DBCC DROPCLEANBUFFERS - образовалось куча свободного кэша
2. Приходит запрос с последовательным чтением страниц индекса
20 июн 12, 16:24    [12747147]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
hosTuk,

На самом деле, сколько будет сканировать запрос зависит от реального распределения данных. Он может просканировать как всю таблицу, так и совсем немного. Алексей Эксаревский из MS Tiger Development говорил на эту тему на 24 hours of PASS, в последнем примере. Как раз этот пример мне показался наиболее интересным, по этому я разобрался и написал на эту тему заметку, которую выложу в новом блоге, как только перенесу туда все старые заметки. Есть даже способ посмотреть в плане сколько реально строк решил просканировать сервер, при помощи очередного недокументированного флага трассировки. И это оценочное количество может сильно отличаться от реального в зависимости от распределения и приводить к действительным тормозам, при этом направление скана играет не последнюю роль.
Так что, если будет интересно, гляньте.
20 июн 12, 19:38    [12748569]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Ну вот, выложил.
Если еще актуально, гляньте, может ваш случай.
3 июл 12, 21:04    [12812370]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
пардон
правильная ссылка
3 июл 12, 21:42    [12812502]     Ответить | Цитировать Сообщить модератору
 Re: Запрос недопонимаю немного план и статистику чтений  [new]
hosTuk
Member

Откуда:
Сообщений: 455
SomewhereSomehow,

Ок, занес в закладки, похожие слова нашел:), спасибо
6 июл 12, 12:41    [12827971]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить