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

Откуда: Moscow Square
Сообщений: 624
Дано:
Таблица:
CREATE TABLE [dbo].[Customer](
	[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[CustomerGuid] [uniqueidentifier] NOT NULL,
	[Username] [nvarchar](1000) NULL,
	[Email] [nvarchar](1000) NULL,
	[Password] [nvarchar](max) NULL,
	[PasswordFormatId] [int] NOT NULL,
	[PasswordSalt] [nvarchar](max) NULL,
	[AdminComment] [nvarchar](max) NULL,
	[IsTaxExempt] [bit] NOT NULL,
	[AffiliateId] [int] NOT NULL,
	[VendorId] [int] NOT NULL,
	[HasShoppingCartItems] [bit] NOT NULL,
	[Active] [bit] NOT NULL,
	[Deleted] [bit] NOT NULL,
	[IsSystemAccount] [bit] NOT NULL,
	[SystemName] [nvarchar](max) NULL,
	[LastIpAddress] [nvarchar](max) NULL,
	[CreatedOnUtc] [datetime] NOT NULL,
	[LastLoginDateUtc] [datetime] NULL,
	[LastActivityDateUtc] [datetime] NOT NULL,
	[BillingAddress_Id] [int] NULL,
	[ShippingAddress_Id] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Запрос:
declare @p__linq__0 nvarchar(4000)=N'SearchEngine'
SELECT TOP (1) 
    [Project1].[Id] AS [Id], 
    [Project1].[CustomerGuid] AS [CustomerGuid], 
    [Project1].[Username] AS [Username], 
    [Project1].[Email] AS [Email], 
    [Project1].[Password] AS [Password], 
    [Project1].[PasswordFormatId] AS [PasswordFormatId], 
    [Project1].[PasswordSalt] AS [PasswordSalt], 
    [Project1].[AdminComment] AS [AdminComment], 
    [Project1].[IsTaxExempt] AS [IsTaxExempt], 
    [Project1].[AffiliateId] AS [AffiliateId], 
    [Project1].[VendorId] AS [VendorId], 
    [Project1].[HasShoppingCartItems] AS [HasShoppingCartItems], 
    [Project1].[Active] AS [Active], 
    [Project1].[Deleted] AS [Deleted], 
    [Project1].[IsSystemAccount] AS [IsSystemAccount], 
    [Project1].[SystemName] AS [SystemName], 
    [Project1].[LastIpAddress] AS [LastIpAddress], 
    [Project1].[CreatedOnUtc] AS [CreatedOnUtc], 
    [Project1].[LastLoginDateUtc] AS [LastLoginDateUtc], 
    [Project1].[LastActivityDateUtc] AS [LastActivityDateUtc], 
    [Project1].[BillingAddress_Id] AS [BillingAddress_Id], 
    [Project1].[ShippingAddress_Id] AS [ShippingAddress_Id]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[CustomerGuid] AS [CustomerGuid], 
        [Extent1].[Username] AS [Username], 
        [Extent1].[Email] AS [Email], 
        [Extent1].[Password] AS [Password], 
        [Extent1].[PasswordFormatId] AS [PasswordFormatId], 
        [Extent1].[PasswordSalt] AS [PasswordSalt], 
        [Extent1].[AdminComment] AS [AdminComment], 
        [Extent1].[IsTaxExempt] AS [IsTaxExempt], 
        [Extent1].[AffiliateId] AS [AffiliateId], 
        [Extent1].[VendorId] AS [VendorId], 
        [Extent1].[HasShoppingCartItems] AS [HasShoppingCartItems], 
        [Extent1].[Active] AS [Active], 
        [Extent1].[Deleted] AS [Deleted], 
        [Extent1].[IsSystemAccount] AS [IsSystemAccount], 
        [Extent1].[SystemName] AS [SystemName], 
        [Extent1].[LastIpAddress] AS [LastIpAddress], 
        [Extent1].[CreatedOnUtc] AS [CreatedOnUtc], 
        [Extent1].[LastLoginDateUtc] AS [LastLoginDateUtc], 
        [Extent1].[LastActivityDateUtc] AS [LastActivityDateUtc], 
        [Extent1].[BillingAddress_Id] AS [BillingAddress_Id], 
        [Extent1].[ShippingAddress_Id] AS [ShippingAddress_Id]
        FROM [dbo].[Customer] AS [Extent1] 
        WHERE ([Extent1].[SystemName] = @p__linq__0) OR (([Extent1].[SystemName] IS NULL) AND (@p__linq__0 IS NULL))
    )  AS [Project1]
    ORDER BY [Project1].[Id] ASC

Распределение данных в таблице:
select SystemName, count(*) cnt
from Customer
group by SystemName
order by 1

SystemName	cnt
NULL	5603609
ArrHealthyCheck	1
BackgroundTask	1
SearchEngine	1
Warmup	1

По факту выборка снаружи идет только по полям где SystemName IS NOT NULL и всегда возвращается одна строка, но явно в запросе этого нет.
Индекса по SystemName нет (и не может быть из-за типа поля), но есть статистика.

Собственно проблема:
Запускаю
EXEC sp_updatestat

, в плане Clustered Index Scan, Number of Rows Read ~ 22600, запрос работает быстро (~ 100 ms).
Статистика выглядит вот так
DBCC SHOW_STATISTICS ('Customer', [_WA_Sys_00000010_36B12243]); 
Name                        Updated              Rows       Rows Sampled  Steps  Density   Average key length  String Index  Filter Expression  Unfiltered Rows
--------------------------- ---- ------------ 	----------- ------------- ------ --------- ------------------- ------------- ------------------ --------------------------
_WA_Sys_00000010_36B12243   May 20 2019  4:24PM  5584561    116167        1      0         0                   YES           NULL               5584561

All density   Average Length Columns
------------- -------------- -----------
1             0              SystemName

RANGE_HI_KEY RANGE_ROWS    EQ_ROWS       DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
------------ ------------- ------------- -------------------- --------------
NULL         0             5584561       0                    1  

Запускаю
update statistics Customer [_WA_Sys_00000010_36B12243] with fullscan

, в плане тоже Clustered Index Scan, но Numbers of Rows Read ~ 5 600 000, запрос резко замедляется (~ 2000 ms).
Статистика становится такой
DBCC SHOW_STATISTICS ('Customer', [_WA_Sys_00000010_36B12243]); 
Name                         Updated              Rows     Rows Sampled  Steps  Density  Average key length String Index Filter Expression  Unfiltered Rows
---------------------------- -------------------- -------- ------------- ------ -------- ------------------ ------------ ------------------ --------------------
_WA_Sys_00000010_36B12243    May 21 2019  1:40AM  5604477  5604477       5      0        1.67723E-05        YES          NULL               5604477

All density   Average Length Columns
------------- -------------- ---------------
0.2           1.67723E-05    SystemName

RANGE_HI_KEY     RANGE_ROWS    EQ_ROWS       DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
---------------- ------------- ------------- -------------------- --------------
NULL             0             5604473       0                    1
ArrHealthyCheck  0             1             0                    1
BackgroundTask   0             1             0                    1
SearchEngine     0             1             0                    1
Warmup           0             1             0                    1


Вопрос: почему так? Я понимаю, что sp_updatestats делает маленький семпл (в данном случае 116167), а update statics with fullscan делает полный семпл.
Но почему при этом так резко меняется Number of Rows Read, а соответственно и время выполнения запроса? Я как-то думал, что скан - он и есть скан, то есть в любом случае прочитает всё.
А так получается, что с лучшей статистикой запрос работает в разы медленнее, парадокс...
21 май 19, 09:56    [21889438]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Oblom
Но почему при этом так резко меняется Number of Rows Read, а соответственно и время выполнения запроса?
Либо
- разные запросы
- разные планы
- разные опции итератора Clustered Index Scan
- разные значения @p__linq__0
21 май 19, 10:24    [21889469]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
komrad
Member

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

покажите
select @@version
21 май 19, 10:38    [21889487]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
invm
Oblom
Но почему при этом так резко меняется Number of Rows Read, а соответственно и время выполнения запроса?
Либо
- разные запросы
- разные планы
- разные опции итератора Clustered Index Scan
- разные значения @p__linq__0


Да, планы разные, в быстром скане паралеллизм не включается, потому как Estimated Number Of Rows = 2366, а в медленном паралеллизм включается, поскольку Estimated Number Of Rows = 5600990

Запросы и значения параметров одни и те же, картина выглядит просто:
update statistics with fullscan - CPU = 100%
exec sp_updatestats - CPU = 10%

Собственно проблема и появилась после регламентного запуска update statistics with fullscan
Вопрос: почему разный семпл в статистике так сильно влияет на Estimated Number Of Rows, а главное на время выполнения запроса.

На всякий случай приложу оба плана, может кому-то будет интересно посмотреть.

К сообщению приложен файл (slow_plan.sqlplan - 51Kb) cкачать
21 май 19, 10:47    [21889498]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
И "быстрый" план
21 май 19, 10:47    [21889499]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
Oblom
И "быстрый" план


К сообщению приложен файл (fast_plan.sqlplan - 43Kb) cкачать
21 май 19, 10:48    [21889500]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
komrad
Oblom,

покажите
select @@version


Microsoft SQL Server 2014 (SP3-CU1) (KB4470220) - 12.0.6205.1 (X64)
Nov 30 2018 02:59:03
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 14393: )
21 май 19, 10:48    [21889501]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
komrad
Member

Откуда:
Сообщений: 5244
Oblom
По факту выборка снаружи идет только по полям где SystemName IS NOT NULL и всегда возвращается одна строка, но явно в запросе этого нет.
Индекса по SystemName нет (и не может быть из-за типа поля), но есть статистика.

попробуйте создать фильтрованную статистику по этому полю (is not null)
https://blog.sqlauthority.com/2015/04/07/sql-server-what-is-filtered-statistics/
21 май 19, 11:06    [21889516]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
komrad
Oblom
По факту выборка снаружи идет только по полям где SystemName IS NOT NULL и всегда возвращается одна строка, но явно в запросе этого нет.
Индекса по SystemName нет (и не может быть из-за типа поля), но есть статистика.

попробуйте создать фильтрованную статистику по этому полю (is not null)
https://blog.sqlauthority.com/2015/04/07/sql-server-what-is-filtered-statistics/


Создал:
create statistics _WA_Sys_00000010_36B12243_Filtered ON Customer(SystemName) WHERE SystemName IS NOT NULL

ситуация не поменялась, видимо фильтрованной ему мало и он опять полную использует
21 май 19, 11:34    [21889548]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Oblom,
для интереса попробуйте
OPTION(QUERYTRACEON 4138)
21 май 19, 12:04    [21889580]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
komrad
Member

Откуда:
Сообщений: 5244
Oblom
[/src]
ситуация не поменялась, видимо фильтрованной ему мало и он опять полную использует

запрос запускали с с option (recompile) ?
21 май 19, 12:07    [21889591]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1367
Oblom,

Раз используете переменную в предикате попробуйте option (recompile) или optimize for @variable= value
21 май 19, 12:08    [21889594]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
спасибо всем за подсказки с изменением запроса.

Однако поменять запрос нетривиальная задача, там коробочное решение и внутрь залезть непросто, плюс сделано всё на LINQ, и по уму надо переписывать на SP или view, чтобы иметь возможность хинтовать.
А совсем уж по уму надо переделывать логику приложения, которая сотню раз в секунду долбится в таблицу клиентов, чтобы получить одну из 4 записей с заполненным полем в таблице с 5.5 млн. строк. И просто для этих 4 пользователей сделать отдельную таблицу и кешировать её вызов минут на 5, а то и на час.
Я нашел другой выход, сменил тип столбца на nvarchar(50) и повесил туда покрывающий индекс.

Но хочется понять, почему величина семпла статистики оказывает такое разрушающее воздействие на план и время выполнения запроса. Больше не меняется ничего, только семпл по которому сделана статистика, ну и гистограмма, которая при этом получается. В "быстрой" гистограмме вообще все значения этого столбца NULL, может в этом дело...
21 май 19, 12:39    [21889633]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1367
Oblom,

кстати да, посмотрел план у вас там собственно в игру вступает row goal.
флаг предложенный товарищем TaPaK должен по сути менять вашу ситуацию с быстрым планом.


<RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="2366.81" EstimateRowsWithoutRowGoal="5.60178e+006" EstimateIO="102.576" EstimateCPU="6.16211" AvgRowSize="22210" EstimatedTotalSubtreeCost="0.0492055" TableCardinality="5.60178e+006" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
21 май 19, 13:22    [21889680]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
invm
Member

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

Ваши планы скомпилированы для @p__linq__0 = 'SearchEngine'
Т.к. в одном случае данное значение отсутствует в статистике, а в другом присутствует, получаются разные оценки стоимости.
Одна из этих оценок приводит к параллельному плану. И получается эффект, описанный тут - https://www.sql.kiwi/2012/05/parallel-row-goals-gone-rogue.html
22 май 19, 10:27    [21890348]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
а кто скажет как получилось 22600? в плане без статистики по полю?
22 май 19, 10:33    [21890358]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
До 2014 если значение выходило за границы гистограммы то вычисление селективности останавливалось, начиная с 2014, когда появилась новая модель оценки, которая используется у ТС, сервер пытается что-то с этим сделать. В частности распознает эту ситуацию, как оценка по возрастающему ключу и пытается моделировать исходя из этого предположения (можно выполнить запрос с недокументированным флагом 9489, который отключает AscendingKeyFilter, чтобы убедиться в этом).

Увидеть процесс можно, включив флаг трассировки 2363 (не документированный), либо событие xEvent query_optimizer_estimate_cardinality.

На примере тестовой таблицы с распределением как у ТС:
TF:
Plan for computation:
  CSelCalcColumnInInterval
      Column: QCOL: [Extent1].SystemName

Loaded histogram for column QCOL: [Extent1].SystemName from stats with id 2

Calculator failed. Replanning.

Plan for computation:
 CSelCalcAscendingKeyFilter(avg. freq., QCOL: [Extent1].SystemName)

Selectivity: 0.000422441

xEvent:
<CalculatorList>
  <FilterCalculator CalculatorName="CSelCalcColumnInInterval" Selectivity="-1.000" CalculatorFailed="true" TableName="[Extent1]" ColumnName="SystemName" />
  <FilterCalculator CalculatorName="CSelCalcAscendingKeyFilter" Selectivity="0.000" TableName="[Extent1]" ColumnName="SystemName" UseAverageFrequency="true" StatId="2" />
</CalculatorList>

Пытается моделировать по средней частоте, но т.к. средняя частота 1 (т.е. все значения одинаковые), то вместо этого, считает квадратный корень из числа строк (допущение модели).
select sqrt(5601780)--2366,80797700194

То, что мы видим в плане после округления.

К сообщению приложен файл. Размер - 7Kb
22 май 19, 12:10    [21890521]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
TaPaK
Member

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

спасибо!
22 май 19, 12:16    [21890528]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
Спасибо всем откликнувшимся! буду курить ссылки и пытаться осознать...
22 май 19, 12:48    [21890594]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
Oblom
Member

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

И последний вопрос, почему так время двух запросов так резко разнится? И там и там скан в запросе, и там и там по идее он должен просканировать все строки и занять это должно примерно одинаковое время...
22 май 19, 12:57    [21890607]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
TaPaK
Member

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

И последний вопрос, почему так время двух запросов так резко разнится? И там и там скан в запросе, и там и там по идее он должен просканировать все строки и занять это должно примерно одинаковое время...

про параллелизм и row goal ссылку уже дали
22 май 19, 13:20    [21890659]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизации  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
TaPaK
Oblom
SomewhereSomehow,

И последний вопрос, почему так время двух запросов так резко разнится? И там и там скан в запросе, и там и там по идее он должен просканировать все строки и занять это должно примерно одинаковое время...

про параллелизм и row goal ссылку уже дали


Всё, понял, спасибо, про параллелизм забыл.
22 май 19, 13:24    [21890667]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить