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

Откуда:
Сообщений: 9
Добрый день! Прошу помощи.

Имеется MS SQL 2016 (13.0.4001.0). 256Гб ОЗУ, 32 ядра Xeon. Большая БД - около 50Тб (содержит изображения). Модель восстановления БД - Simple.
Существуют две большие таблицы (более 600кк строк в каждой): Info и Image.

Scheme
+


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Image](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Image] [varbinary](max) NULL,
	[InfoID] [bigint] NOT NULL,
 CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED 
(
	[Id] 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]
GO

/****** Object:  Table [dbo].[Info]    Script Date: 30.10.2018 12:27:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Info](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Field_1] [nvarchar](max) NULL,
	[Field_2] [datetime2](7) NOT NULL,
	[Field_3] [datetime2](7) NOT NULL,
	[Field_4ID] [int] NOT NULL,
	[Field_5] [nvarchar](max) NULL,
	[Field_6Id] [int] NOT NULL,
	[Field_7] [nvarchar](13) NULL,
	[Field_8] [int] NOT NULL,
	[Field_9] [nvarchar](max) NULL,
	[Field_10] [int] NOT NULL,
	[Field_11] [int] NOT NULL,
 CONSTRAINT [PK_Info] PRIMARY KEY CLUSTERED 
(
	[ID] 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]
GO

/****** Object:  Index [IX_Image_InfoID]    Script Date: 30.10.2018 12:27:54 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Image_InfoID] ON [dbo].[Image]
(
	[InfoID] 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
SET ANSI_PADDING ON
GO

/****** Object:  Index [IX_Info_Field_2_Field_7_Field_4ID]    Script Date: 30.10.2018 12:27:54 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Info_Field_2_Field_7_Field_4ID] ON [dbo].[Info]
(
	[Field_2] ASC,
	[Field_7] ASC,
	[Field_4ID] ASC
)
WHERE ([Field_7] IS NOT NULL)
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

/****** Object:  Index [IX_Info_Field_4ID]    Script Date: 30.10.2018 12:27:54 ******/
CREATE NONCLUSTERED INDEX [IX_Info_Field_4ID] ON [dbo].[Info]
(
	[Field_4ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO

/****** Object:  Index [IX_Info_Field_4ID_Field_3]    Script Date: 30.10.2018 12:27:54 ******/
CREATE NONCLUSTERED INDEX [IX_Info_Field_4ID_Field_3] ON [dbo].[Info]
(
	[Field_4ID] ASC,
	[Field_3] ASC
)
INCLUDE ( 	[ID],
	[Field_2],
	[Field_7],
	[Field_8]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO

/****** Object:  Index [IX_Info_Field_7]    Script Date: 30.10.2018 12:27:54 ******/
CREATE NONCLUSTERED INDEX [IX_Info_Field_7] ON [dbo].[Info]
(
	[Field_7] ASC
)
INCLUDE ( 	[ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [NonClusteredIndex-20180913-142646]    Script Date: 30.10.2018 12:27:54 ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20180913-142646] ON [dbo].[TiInHour]
(
	[Field_4ID] ASC,
	[Hour] ASC
)
INCLUDE ( 	[ID],
	[Count],
	[ElapsedTimeSum],
	[Loaded]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO

/****** Object:  StoredProcedure [dbo].[InsertInfo]    Script Date: 30.10.2018 12:27:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertInfo]
	@Field_1 nvarchar(250),
	@Field_2 datetime2(7),
	@Field_3 datetime2(7),
	@Field_4ID int,
	@Field_5 nvarchar(40),
	@Field_6Id int,
	@Field_7 nvarchar(13),
	@Field_8 int,
	@Image varbinary(MAX),
	@Field_10 int,
	@Field_11 int
AS  
INSERT INTO Info ([Field_1]
      ,[Field_2]
      ,[Field_3]
      ,[Field_4ID]
      ,[Field_5]
      ,[Field_6Id]
      ,[Field_7]
      ,[Field_8]
      ,[Field_10]
      ,[Field_11]) 
	  OUTPUT INSERTED.ID
	  VALUES
	  ( @Field_1
      ,@Field_2
      ,@Field_3
      ,@Field_4ID
      ,@Field_5
      ,@Field_6Id
      ,@Field_7
      ,@Field_8
      ,@Field_10
      ,@Field_11)

INSERT INTO Image 
	([Image], [InfoID]) VALUES (@Image, SCOPE_IDENTITY())
GO



Записываем данные через приложение .NET Core, используя System.Data.SqlClient и хранимую процедуру (есть в схеме). Упрощенно алгоритм выглядит так: каждую запись по одной записываем в Info (там только текст), получаем ID и записываем изображение с этим ID в Image.

SQL установлен с настройками по умолчанию. База лежит на 5 разных RAID массивах, емкостью по 10Тб. Нагрузка на ЦП низкая, очереди на дисках нет. Входные данные всегда усреднено одинаковые. Подсчитываю время записи на 1000 записей.

Проблема: время записи на 1000 "строк" постоянно прыгает. К примеру, 1-2 часа пишет со скоростью 20 сек на 1000 "строк", после начинает тратить на это 8 секунд. Работает так от нескольких минут до нескольких часов, снова сваливается в 20 сек. Проверял все это ночью, нагрузки клиентов в данный период нет - подтверждается логами приложения и особенностью работы.

Индексы перестраивал полностью на Info, на Image фрагментации нет. Не помогает.

Такое ощущение, что SQL выполняет какие-то регламентные операции, чистит кеш или что-то там пересчитывает, работает какое-то время, потом снова тупит. Как понять, с чем может быть связано это рендомная запись при одинаковых условиях?

Сообщение было отредактировано: 30 окт 18, 13:16
30 окт 18, 13:08    [21718887]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36905
DiZar
Как понять, с чем может быть связано это рендомная запись при одинаковых условиях?

Как вы убедились, что условия разные? Вы размер записываемых данных посчитали? Вы помониторили нагрузку на сервере, когда "снова сваливается в 20 сек"?
30 окт 18, 13:19    [21718905]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
DiZar
Member

Откуда:
Сообщений: 9
Гавриленко Сергей Алексеевич
DiZar
Как понять, с чем может быть связано это рендомная запись при одинаковых условиях?

Как вы убедились, что условия разные? Вы размер записываемых данных посчитали? Вы помониторили нагрузку на сервере, когда "снова сваливается в 20 сек"?


В моменты быстрой записи и медленной смотрел текущие операции скриптом:

+ Скрипт по текущим операциям

select session_id, status, wait_type, command, last_wait_type, percent_complete
, qt.text sql1
, total_elapsed_time/1000 as [total_elapsed_time, sec],
wait_time/1000 as [wait_time, sec], (total_elapsed_time - wait_time)/1000 as [work_time, sec]
from sys.dm_exec_requests as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt


Сравнивал загрузку ЦП, количество очередей на дисках, ожидающие запросы в мониторинге ресурсов в Managment Studio.

В двух случаях все эти показатели были одинаковыми, кроме записи на диск. В момент "быстрой" работы SQL пишет 7-8 метров в секунду в лог и столько же в файлы БД. При медленной работе скорость записи составляет 3 Мб\сек.

Объем данных не замерял, сделаю.
30 окт 18, 13:51    [21718938]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
DiZar
Member

Откуда:
Сообщений: 9
Текущих операций в эти моменты, кроме самого insert вообще нет. Т.е. SQL никто никто не мешает.
30 окт 18, 13:54    [21718945]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7663
DiZar, значит в этот момент что-то еще хочет получить доступ к диску.
30 окт 18, 13:56    [21718947]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
TaPaK
Member

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

размер/ приращение лога какой?
30 окт 18, 13:57    [21718951]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36905
Посмотрите нагрузку на файлы через sys.dm_io_virtual_file_stats.

Нехорошо, если кол-во байт записанных / прочитанных отличается между файлами данных, или отличается ожидание на одну операцию ввода-вывода между файлами. Так же сраните показатели между периодами быстрой и медленной работы.

З.Ы. Ну и самое главное: хотите писать быстро -- прикрутите bulk-вставку.
30 окт 18, 14:02    [21718963]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36905
TaPaK
DiZar,

размер/ приращение лога какой?
И файлов данных. Настроено ли instant file initialization?
30 окт 18, 14:04    [21718965]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7663
Как-то сомнительно, что размер приращения такой, что он два часа диск пилит.
30 окт 18, 14:09    [21718971]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36905
Владислав Колосов
Как-то сомнительно, что размер приращения такой, что он два часа диск пилит.
Степень точности измерения продолжительности медленной работы не известена.

Сообщение было отредактировано: 30 окт 18, 14:12
30 окт 18, 14:12    [21718976]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
DiZar
Member

Откуда:
Сообщений: 9
TaPaK
DiZar,

размер/ приращение лога какой?


1024 Мб

sys.dm_io_virtual_file_stats попробую посмотреть чуть позже.

bulk-вставку пробовали, что-то остановило нас, постараюсь вспомнить.

И файлов данных. Настроено ли instant file initialization?


Файлам данных сейчас разрешено расти только одному по 2048 Мб. Instant file initialization не настраивал, только сегодня прочитал про это. Но, я нарезал свободного места вперед. Сейчас свободного места порядка 600Гб. Это сутки работы без авторасширения.

Замерил по последним логам периоды, оказывается закономерность есть:

32 минут медленной
15 минут быстрой
36 минут медленной
16 минут быстрой

Еще момент забыл указать. В субботу я перестроил все индексы и поставил запись, в надежде, что все залетает. Чуда не произошло - все тупило. Потом взял и нарезал вперед 300 Гб места и запись пошла очееень быстро. Я думал, что решил проблему, но вчера она возникла снова даже при свободном месте. И добавление места во все файлы также не помогает. Логи SQL кстати чистые. Смущает именно цикличность ....
30 окт 18, 14:31    [21719010]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
TaPaK
Member

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

посмотрите на события file growths и chekpoint. Вполне может быть что дело в них... Ну и чем больше индексов тем медленней вставка
30 окт 18, 14:54    [21719041]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
invm
Member

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

Сохраните для пишущей сессии содержимое sys.dm_exec_session_wait_stats в начале и конце медленных периодов. Потом посмотрите на чем были ожидания.

А может у вас сервер в виртуальной среде и чудит именно она?
30 окт 18, 15:14    [21719092]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
DiZar
Member

Откуда:
Сообщений: 9
Все рекомендации проверю вечером, отпишусь, спасибо всем!
30 окт 18, 16:58    [21719252]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
uaggster
Member

Откуда:
Сообщений: 811
Для проформы вопрос: лог лежит, конечно же на отдельном диске? не вместе с каким-либо сегментом данных?
30 окт 18, 17:00    [21719255]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7663
На виртуальных дисках и процессорах вообще все что угодно может быть.
30 окт 18, 18:41    [21719421]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
DiZar
Member

Откуда:
Сообщений: 9
Новости следующие.

1. Да, это виртуалка. Доступа к администрированию у меня нет. Что происходит на гипервизоре для меня загадка.
2. Думаю дело не в instant file initialization, т.к. место я нарезал в файлах руками на пару дней вперед. Тоже самое с file growths, маловероятно.
3. Сhekpoint, прочитал. Идя хорошая, но, насколько я понял, это влияло бы на на каждую запись. Хотя, судя по настройкам, у меня автоматические контрольные точки (в SSMS в настройках стоит 0, что якобы говорит от автоматических, но команда "SELECT name, target_recovery_time_in_seconds FROM sys.databases WHERE name = 'My_DB" выдает значение "60". Что свидетельствует о косвенных контрольных точках.

Переложить лог на отдельный диск, собрать статистику sys.dm_exec_session_wait_stats и остальные рекомендации проверить не успел, потому происходит что-то странное. Я решил считать количество записанных Мб, добавил это в логи. Перезалил ПО и запустил.....уже 2 часа запись идет на идеальной скорости. Хотя до этого я перезаливал его раз 20 для сбора статистики и ничего такого не было. Начинаю склоняться к версии "На виртуальных дисках и процессорах вообще все что угодно может быть."

Продолжаю наблюдение.....
30 окт 18, 22:50    [21719605]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
Владислав Колосов
Member

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

"лучшие практики" рекомендуют использование физических дисков на виртуальных машинах (одно из главных требований производительности), но если вы не можете повлиять на настройки, то придется смириться.
31 окт 18, 10:58    [21719965]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
uaggster
Member

Откуда:
Сообщений: 811
DiZar, вот хорошая статья на Хабре https://habr.com/post/414269/
Там, в общем то, прописные истины в отношении логфайлов, но прочесть полезно.
Да, в случае высоконагруженных систем, в частности - в случае если вы заливаете данные терабайтами - логи желательно держать на отдельных физических дисках, либо на виртуальных, но физически расположенных на отдельной, предназначенной только для этой БД и только для логов - группе дисков, причем размер виртуального диска должен быть фиксированным.
Кстати, размер в виртуального диска с данными - тоже крайне желательно делать фиксированным.
И в любом случае крайне нежелательно помещать логи и данные на один диск, даже виртуальный.

Кстати, нужно проверить, сколько иопсов у вас выделено под диск, на котором расположены логи. Пропускная способность, например, может быть зарезана сверху.
Если у вас, к примеру, 300 иопсов на диске с логами - вы на больше 300 операций в секунду и не сделаете, причем не важно, по сколько вы будете заливать на диск - пакетом по 1000 записей или по 1 записи - всё равно максимум 300 таких пакетов в секунду. Но по 1000 записей это будет, грубо говоря, 300 тыс. записей максимум, а по одной - 300 :-)
Ну, речь о верхней планке, разумеется.

Поправьте, если ошибаюсь.
31 окт 18, 16:14    [21720531]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
DiZar
Member

Откуда:
Сообщений: 9
uaggster, спасибо за статью! Полезные вещи всегда читаю и сохраняю. IOPS могу замерить, но для этого придется остановить систему, чтобы замерять в "чистых" условиях. Пока этого делать не хочется.
31 окт 18, 21:35    [21720884]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
DiZar
Member

Откуда:
Сообщений: 9
Всем спасибо. Похоже, отгадка нашлась. СХД занимается беками по расписанию. В этом причина такого поведения СУБД. Тема закрыта.
1 ноя 18, 22:10    [21721991]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL разное время insert 'a  [new]
DiZar
Member

Откуда:
Сообщений: 9
*бекапами данных, включая мои 50Тб
1 ноя 18, 22:11    [21721993]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить