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

Откуда:
Сообщений: 769
Microsoft SQL Server 2014 - 12.0.4100.1 (X64)
Apr 20 2015 17:29:27
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

DBCC SHOW_STATISTICS ("dbo.ClicksHistory", PK_ClicksHistory)


Name Updated RowsRows SampledStepsDensityAverage key lengthString IndexFilter ExpressionUnfiltered Rows
PK_ClicksHistoryAug 25 2015 1:00PM704107270410721214NO NULL7041072

All densityAverage LengthColumns
1.420238E-074ClickHistoryID

RANGE_HI_KEYRANGE_ROWSEQ_ROWSDISTINCT_RANGE_ROWSAVG_RANGE_ROWS
18176075680101
1817839961329661329661
1818396047327671327671
1819340104327671327671
1821301896327671327671
1825675672327671327671
1829573878327671327671
1832648888491511491511
1834435582327671327671
1835072626327671327671
18418022016729574167295741
18418022020101


Всего 12 шагов у гистограммы и при этом такое "странное" распределение.
Можно ли на это как-то повлиять?
25 авг 15, 14:46    [18065051]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
UPDATE STATISTICS ... WITH FULLSCAN


если на выходе тоже самое, тогда не лечится никак ибо правда
25 авг 15, 14:56    [18065155]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
http://sqlmag.com/blog/how-many-steps-should-histogram-have
25 авг 15, 14:59    [18065176]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
sti
Member

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

не лечится, это уже после
UPDATE STATISTICS ... WITH FULLSCAN
25 авг 15, 15:04    [18065214]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
sti
Member

Откуда:
Сообщений: 769
invm
http://sqlmag.com/blog/how-many-steps-should-histogram-have

Там говорится об объединении шагов гистограммы, что понятно и логично. В моем примере однако разница в RANGE_ROWS от 32767 до 6729574. Назвать это несущественным язык не поворачивается (
25 авг 15, 15:10    [18065246]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Так а что не так?
1841802201 - 1835072626 - 1 = 6729574.
Арифметика сходится.
Уверен, что
SELECT COUNT(*) FROM dbo.ClicksHistory WHERE ClickHistoryID BETWEEN 1835072626 + 1 AND 1841802201 - 1
покажет то же самое.
25 авг 15, 16:06    [18065778]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
У меня вон какое распределение есть. Так что ничего криминального в этом нет. Правда, почему SQL Server строит именно такую гистограмму, сказать не могу. Sorry.
RANGE_HI_KEYRANGE_ROWSEQ_ROWSDISTINCT_RANGE_ROWSAVG_RANGE_ROWS
10101
43794358143581
473731913191
551776717671
567112711271
113105631156311
1145412711271
213199855198551
2244412711271
37323148651148651
373240101
25 авг 15, 16:12    [18065822]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
o-o
Guest
Jovanny
Так а что не так?
1841802201 - 1835072626 - 1 = 6729574.
Арифметика сходится.

да просто странно, что может 200 шагов построить, но не хочет.
первые часто-часто просчитал, на оставшиеся значения забил -- на БОЛьШИНСТВО значений...
у меня 2008 R2, шаги тоже не все использует, т.е. есть таблица в 27млн строк, там и то 193 шага.
но "распределение шагов" равномерное, а у ТС явно перекошенное
25 авг 15, 16:47    [18066164]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Мы тут с коллегой обсуждали.. а можно сделать ребилд индекса и показать после статистику. Просто интересно.
25 авг 15, 16:56    [18066225]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
o-o
Guest
граждане, все это нормально,
до меня дошло + подтверждается специально заполненной таблицей чисел.
да, он "схлопывает" похожие интервалы,
и именно в таблице ТС данные так распределились, что в первых строках есть дыры определенной частоты,
а в последних 6729574 строках значения ключа идут подряд.
вот таким запросом можно оценить "дыры"
(поле f. примерно каждый f-ый ключ присутствует в данном интервале)
[кустарное производство, прошу не считать за истину в последней инстанции]:
declare @t table (RANGE_HI_KEY bigint,	RANGE_ROWS int);
insert into @t values	
(1817607568,	0),	
(1817839961,	32966)	,
(1818396047,	32767)	,
(1819340104,	32767)	,
(1821301896,	32767)	,
(1825675672,	32767)	,
(1829573878,	32767)	,
(1832648888,	49151)	,
(1834435582,	32767)	,
(1835072626,	32767)	,
(1841802201,	6729574	),
(1841802202,	0	)	

select RANGE_HI_KEY, 
       lag(RANGE_HI_KEY) over(order by RANGE_HI_KEY) as prev,
       RANGE_HI_KEY - lag(RANGE_HI_KEY) over(order by RANGE_HI_KEY) - 1 as diff,
       RANGE_ROWS,
       (RANGE_HI_KEY - lag(RANGE_HI_KEY) over(order by RANGE_HI_KEY) - 1) / nullif(RANGE_ROWS, 0) as f
from @t

RANGE_HI_KEY prev diff RANGE_ROWS f
1817607568 NULLNULL 0 NULL
1817839961 1817607568 232392 32966 7
1818396047 1817839961 556085 32767 16
1819340104 1818396047 944056 32767 28
1821301896 1819340104 1961791 32767 59
1825675672 1821301896 4373775 32767 133
1829573878 1825675672 3898205 32767 118
1832648888 1829573878 3075009 49151 62
1834435582 1832648888 1786693 32767 54
1835072626 1834435582 637043 32767 19
1841802201 1835072626 6729574 6729574 1
1841802202 1841802201 0 0 NULL

мы имеем, сколько у нас есть строк между соседними значениями шагов гистограммы,
исключая сами эти значения, это RANGE_ROWS.
мы можем посчитать разность между самими значениями, исключая их самих, это diff.
самое простое diff оказалось для значений ключа от 1835072626 до 1841802201, это 6729574.
имеем 6729574 строк и 6729574 различных значений ключа, --> они просто идут подряд,
про что и написал Jovanny.
вот их и схлопнули в 1 интервал.
не было бы других значений, интервал вообще был бы один.
но в начальных строках есть дыры:
в первом интервале 1817607568..1817839961
присутстсвует примерно каждое 7-ое значение: 1817607568, 1817607568 + 7, ...
во втором примерно каждое 16-ое, типа 1817839961, 1817839961 + 16, ...
все это может проверить ТС, сделав выборку из каждого интервала.

а себе я присваиваю сегодня тройного жирафа
Картинка с другого сайта.Картинка с другого сайта.Картинка с другого сайта.
25 авг 15, 22:00    [18067731]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
sti
Member

Откуда:
Сообщений: 769
o-o
граждане, все это нормально

Да, действительно. Я почему-то исходил из того, что статистика пытается строить интервалы с более менее равным числом записей в них. Она же строит, похоже, интервалы более менее равные по ширине. Тоже имеет смысл.

Спасибо тройному жирафу )
Теперь, правда, не могу понять как они умудрились такое получить на столбце с IDENTITY... Записи не удаляются, упавших транзакций - 7 штук... Однако.

Но вот другой индекс их этой же таблицы, статистика по которому меня не радует:
RANGE_HI_KEYRANGE_ROWSEQ_ROWSDISTINCT_RANGE_ROWSAVG_RANGE_ROWS
0x00000562D52225696CAC69917245103D0101
0xFFFFFB7E36BA90B3A59AA2AD250281D86893762168937621
0xFFFFFD8FAA2EF6406E871DFB9063D3670101
26 авг 15, 10:12    [18068706]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
sti
Но вот другой индекс их этой же таблицы, статистика по которому меня не радует
Я вам уже давал ссылку на статью, в которой объясняется как схлопываются интервалы гистограммы и почему можно так делать. Очевидно, вы не читали или читали невнимательно.
26 авг 15, 10:21    [18068750]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
sti
Member

Откуда:
Сообщений: 769
invm
sti
Но вот другой индекс их этой же таблицы, статистика по которому меня не радует
Я вам уже давал ссылку на статью, в которой объясняется как схлопываются интервалы гистограммы и почему можно так делать. Очевидно, вы не читали или читали невнимательно.

Мне понятно, что в зависимости от данных в таблице и коэффициентов в алгоритме схлопывания вполне может получиться такая ситуация, когда в гистограмме остался фактически один шаг. Винить тут некого, значит такие данные в таблице. Что не исключает факта, что меня это не радует )
26 авг 15, 12:39    [18069741]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
o-o
Guest
sti
Мне понятно, что в зависимости от данных в таблице и коэффициентов в алгоритме схлопывания вполне может получиться такая ситуация, когда в гистограмме остался фактически один шаг. Винить тут некого, значит такие данные в таблице. Что не исключает факта, что меня это не радует )

а почему вас это расстраивает?
ну идут у вас подряд, зачем лепить 200 шагов, когда и 1 хватит?
ну пусть не идут подряд, пусть каждое 3-е значение, хоть какое,
лишь бы равномерное распределение было, но ведь построю я 2 шага или 200,
никакой новой информации не появится, а место потратится.
спрашивается, зачем?
26 авг 15, 12:46    [18069773]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
sti
Member

Откуда:
Сообщений: 769
o-o
sti
Мне понятно, что в зависимости от данных в таблице и коэффициентов в алгоритме схлопывания вполне может получиться такая ситуация, когда в гистограмме остался фактически один шаг. Винить тут некого, значит такие данные в таблице. Что не исключает факта, что меня это не радует )

а почему вас это расстраивает?
ну идут у вас подряд, зачем лепить 200 шагов, когда и 1 хватит?
ну пусть не идут подряд, пусть каждое 3-е значение, хоть какое,
лишь бы равномерное распределение было, но ведь построю я 2 шага или 200,
никакой новой информации не появится, а место потратится.
спрашивается, зачем?


Не радует != расстраивает )

Во втором случае тип поля binary(16). Что в этом случае значит идут подряд?

Далее оффтопик, вообще то. Но раз спросили...

Индекс этот используется в одном запросе типа:

UPDATE CH
SET
...
FROM Source 
JOIN dbo.ClicksHistory CH 
		ON CH.Hash = Source.Hash
WHERE
...


В плане Index Seek + Key Lookup. Что опять же нормально, поскольку обновляются практически все поля таблицы CH, а покрывающий индекс считается что дорого. Ну ОК. Estimated Number Of Rows по этому индексу всегда 1. Хм, но тоже вроде не трагедия.

Далее сам не наблюдал, только со слов. Периодически дело это тормозило и лечили его с помощью UPDATE STATISTICS по всей таблице. Потом поставили UPDATE STATISTICS в джобе ежечасно и наступило счастье. Почему - хотелось бы понять )))
26 авг 15, 14:22    [18070475]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
sti
Member

Откуда:
Сообщений: 769
o-o
ну идут у вас подряд


Пробую интереса ради:
+ данные

CREATE TABLE [dbo].[TestS](
	[ID] [int] NOT NULL,
	[CID] [varchar](20) NULL
) 

GO

CREATE CLUSTERED INDEX [PK_TestS] ON [dbo].[TestS]
(
	[ID] ASC
)

GO

insert into dbo.tests (ID) 
select row_number() over (order by ID) rn from dbo.Data-- 301759 строк
GO

update dbo.TestS set CID = RIGHT('00000' + cast(ID as varchar(19)),6)
go

CREATE UNIQUE NONCLUSTERED INDEX [UQ_TestS_CID] ON [dbo].[TestS]
(
	[CID] ASC
)
GO

UPDATE STATISTICS dbo.TestS [PK_TestS] with fullscan
go 
UPDATE STATISTICS dbo.TestS [UQ_TestS_CID] with fullscan
go 

DBCC SHOW_STATISTICS ("dbo.TestS", [PK_TestS]) WITH HISTOGRAM
DBCC SHOW_STATISTICS ("dbo.TestS", [UQ_TestS_CID]) WITH HISTOGRAM


+ гистограммы

RANGE_HI_KEYRANGE_ROWSEQ_ROWSDISTINCT_RANGE_ROWSAVG_RANGE_ROWS
10101
30176030175813017581
3017610101


RANGE_HI_KEYRANGE_ROWSEQ_ROWSDISTINCT_RANGE_ROWSAVG_RANGE_ROWS
0000010101
0012241222112221
0063445119151191
0078801535115351
0099282047120471
0119762047120471
0140242047120471
0155601535115351
0186323071130711
0201681535115351
0222162047120471
0237521535115351
0258002047120471
0273361535115351
0293842047120471
0304081023110231
0345044095140951
0360401535115351
0370641023110231
0386001535115351
0396241023110231
0411601535115351
0452564095140951
0467921535115351
0483281535115351
0498641535115351
0514001535115351
0529361535115351
0560083071130711
0575441535115351
0590801535115351
0606161535115351
0647124095140951
0662481535115351
0672721023110231
0688081535115351
0698321023110231
0708561023110231
0729042047120471
0744401535115351
0759761535115351
0775121535115351
0795602047120471
0805841023110231
0836563071130711
0851921535115351
0882643071130711
0903122047120471
0995289215192151
1005521023110231
1097689215192151
1118162047120471
1189847167171671
1200081023110231
1292249215192151
1312722047120471
1394648191181911
1404881023110231
1486808191181911
1507282047120471
1589208191181911
1609682047120471
1630162047120471
1691606143161431
1701841023110231
1783768191181911
1804242047120471
1896409215192151
1911761535115351
1927121535115351
1942481535115351
1957841535115351
1973201535115351
1988561535115351
2003921535115351
2019281535115351
2050003071130711
2065361535115351
2080721535115351
2096081535115351
2111441535115351
2126801535115351
2142161535115351
2157521535115351
2172881535115351
2188241535115351
2203601535115351
2218961535115351
2234321535115351
2249681535115351
2280403071130711
2295761535115351
2316242047120471
2398168191181911
2418642047120471
2459604095140951
2480082047120471
2500562047120471
2582488191181911
2602962047120471
2684888191181911
2705362047120471
2787288191181911
2807762047120471
2889688191181911
2910162047120471
2992088191181911
3012562047120471
30176150415041

26 авг 15, 14:38    [18070561]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
sti
UPDATE CH
SET
...
FROM Source 
JOIN dbo.ClicksHistory CH 
		ON CH.Hash = Source.Hash
WHERE
...

Потом поставили UPDATE STATISTICS в джобе ежечасно и наступило счастье. Почему - хотелось бы понять )))


палите весь запрос. дело не в статистике :). он параметры проwедуры/функции не использует?
26 авг 15, 14:39    [18070568]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
o-o
Guest
про пример:
в случае цифр сообразил и схлопнул,
в случае строк не умеет/не реализовали -- не знаю,
но я за их алгоритм ответственности не несу :)

про binary(16):
а что в where?
26 авг 15, 14:54    [18070667]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
sti
Member

Откуда:
Сообщений: 769
o-o
про пример:
в случае цифр сообразил и схлопнул,
в случае строк не умеет/не реализовали -- не знаю,

вот-вот. Явно алгоритм зависит от типа данных.

Вот такой запрос:
UPDATE CH
SET
...
	FROM Source WITH (NOLOCK)
	JOIN Affilinet.DBO.ClicksHistory CH WITH (NOLOCK)
		ON CH.Hash = Source.Hash
	WHERE 1=1
	AND Source.StatsYearMon = @StatsYearMon
	AND	Source.[Day] = @Day
	AND Source.ClicksConfirmDate IS NULL
	AND (@PartUpdate Is NULL OR Source.PartUpdate=@PartUpdate);

В Source таблице индекс подходящий есть и используется на ура.
26 авг 15, 15:06    [18070736]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
sti
Вот такой запрос
Статистика ни при чем - у вас проблема из-за parameters sniffing.
Лечение "в лоб": добавить option(recompile)
26 авг 15, 15:25    [18070888]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
sti
Member

Откуда:
Сообщений: 769
invm
sti
Вот такой запрос
Статистика ни при чем - у вас проблема из-за parameters sniffing.
Лечение "в лоб": добавить option(recompile)

Я, лично, тоже склонен считать что проблема в этом и обновление статистики помогает просто потому, что вынуждает план построить заново.

Но, "склонен считать" это, увы, не аргумент.
26 авг 15, 15:55    [18071158]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
sti
o-o
про пример:
в случае цифр сообразил и схлопнул,
в случае строк не умеет/не реализовали -- не знаю,

вот-вот. Явно алгоритм зависит от типа данных.

Вот такой запрос:
UPDATE CH
SET
...
	FROM Source WITH (NOLOCK)
	JOIN Affilinet.DBO.ClicksHistory CH WITH (NOLOCK)
		ON CH.Hash = Source.Hash
	WHERE 1=1
	AND Source.StatsYearMon = @StatsYearMon
	AND	Source.[Day] = @Day
	AND Source.ClicksConfirmDate IS NULL
	AND (@PartUpdate Is NULL OR Source.PartUpdate=@PartUpdate);

В Source таблице индекс подходящий есть и используется на ура.


если поможет лечение в лоб с recompile, то можно сделать не в лоб, вероятно причиной является "универсальный" запрос с AND (@PartUpdate Is NULL OR Source.PartUpdate=@PartUpdate). тогда, можно разбить запрос на вызов одной из двух процедур:

create procedure p1 as
UPDATE CH
SET
...
	FROM Source WITH (NOLOCK)
	JOIN Affilinet.DBO.ClicksHistory CH WITH (NOLOCK)
		ON CH.Hash = Source.Hash
	WHERE 1=1
	AND Source.StatsYearMon = @StatsYearMon
	AND	Source.[Day] = @Day
	AND Source.ClicksConfirmDate IS NULL
	AND Source.PartUpdate=@PartUpdate 


create procedure p2 as
UPDATE CH
SET
...
	FROM Source WITH (NOLOCK)
	JOIN Affilinet.DBO.ClicksHistory CH WITH (NOLOCK)
		ON CH.Hash = Source.Hash
	WHERE 1=1
	AND Source.StatsYearMon = @StatsYearMon
	AND	Source.[Day] = @Day
	AND Source.ClicksConfirmDate IS NULL
	/* выпилено AND (@PartUpdate Is NULL OR Source.PartUpdate=@PartUpdate); */ 


-- а там где был запрос, написать

if @PartUpdate is not null
     exec p1;
else
     exec p2;

-- это из особенностей компиляции процедур следует.


есть и другие способы, динамически в зависимости от параметров формировать строку запроса и потом выполнять через sp_executesql, например.

а если - причина в перекосе распределения по каким-то колонкам, можно поступить также.
26 авг 15, 15:59    [18071208]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
sti
invm
пропущено...
Статистика ни при чем - у вас проблема из-за parameters sniffing.
Лечение "в лоб": добавить option(recompile)

Я, лично, тоже склонен считать что проблема в этом и обновление статистики помогает просто потому, что вынуждает план построить заново.

Но, "склонен считать" это, увы, не аргумент.


так проверь
26 авг 15, 16:00    [18071218]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
sti
Member

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

да, спасибо, это мне известно. Тут этих универсальных запросов пруд пруди и проблем от этого тоже. Уже убедил некоторые разбить на части ))

В данном случае тоже нет убедительного аргумента, что это является причиной.
CLUSTERED PK на Source:
(
[StatsYearMon] ASC,
[Day] ASC,
[PartUpdate] ASC,
[Hash] ASC
)

В любом случае вносить изменения в продакшн реально будет только если опять возникнут проблемы. Поэтому - "так проверь" - проблематично. Смоделировать проблему искусственно на тестовой системе не удается.
26 авг 15, 16:15    [18071335]     Ответить | Цитировать Сообщить модератору
 Re: Странная гистограмма у статистики  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
sti
churupaha,

да, спасибо, это мне известно. Тут этих универсальных запросов пруд пруди и проблем от этого тоже. Уже убедил некоторые разбить на части ))

В данном случае тоже нет убедительного аргумента, что это является причиной.
CLUSTERED PK на Source:
(
[StatsYearMon] ASC,
[Day] ASC,
[PartUpdate] ASC,
[Hash] ASC
)

В любом случае вносить изменения в продакшн реально будет только если опять возникнут проблемы. Поэтому - "так проверь" - проблематично. Смоделировать проблему искусственно на тестовой системе не удается.


добавьте что написано тут 18070888

и посмотрите меняется ли план передаче null и какого-нибудь значения.
26 авг 15, 16:20    [18071370]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить