По правде, глобальная задумка сделать набор бесплатных тулов для обслуживания SQL Server, которыми сам же и буду пользоваться. Но на эту задачу требуется уж очень много времени, потому и решил начать с чего-то относительно простого — сделать тул для анализа и обслуживания индексов с учетом ошибок конкурирующих решений. А потом на основе этого клепать следующие тулы. Если будет желание поучаствовать в бета-тестировании или просто советом помочь, то буду рад.
В случае работы с логом отсутствие понимания что делает MaxParallel ничем не лучше Delayed durability. И там и там меняется поведение при работе с лог буфером. Возможно что-то другое, поэтому вслепую я бы не рискнул такое на продакшен ставить. При DW нагрузке если данные уже в памяти как Ваш продукт может ускорить запросы? А если они на диске, то опять же скорее всего используется некий аналог упреждающего чтения.
В общем, по моему мнению, чуточку мутно. Тем более, что начиная с 2016-го сиквела добавили аппаратные приблуды, которые дешевле под OLTP нагрузку и менять в коде ничего не нужно. А для DW иногда достаточно сделать кластерный ColumnStore секционированный. Хотя буду объективным истины в чем-то одном нет. Ваш продукт возможно хорош, но нет хорошего прува с технической составляющей. Я бы ее с радостью почитал.
Сам по себе продукт, конечно, интересный. Но технических подробностей не хватает. Вы приводили пример с множественными INSERT...UPDATE...DELETE. Это можно решить на уровне настроек базы за счёт Delayed durability начиная с 2014 версии. Либо использования ин-мемори. Начиная с 2016 SP1 этот функционал и в экспресс редакции есть, поэтому с ускорением OLTP проблем быть не должно. По правде так и не понял как Ваш продукт использовать и что он с трансляцией IO запросов сиквела делает.
SQL Server 2016 on Linux. Мне, как и многим, наверное, пока страшно его запускать в «промышленную эксплуатацию», но если есть у кого-то подобный опыт
Пока ничего хорошего на Linux для SQL Server не предвидится. Вот один из простых примеров. И таких радостей, по правде, уже накапливается много. Возможно через пару кумулятивных обновлений все станет лучше, но пока что-то серьезное на SQL Server под Linux делать я бы не рискнул.
В плане статьи, поддержу мнение, что Updater подлые вещи иногда делает. Но что мешает в момент установки все настроить? Отключить обновление, телеметрию на уровне SQL Server и системы в целом. И тогда подобных сюрпризов будет по минимуму. Опять же не берем во внимание Azure — он живет по другим правилам.
Небольшое репро с использованием кластерного ColumnStore:
SET NOCOUNT ON
USE AdventureWorks2014 -- SQL Server 2017
GO
DROP TABLE IF EXISTS #CCI
DROP TABLE IF EXISTS #Heap
GO
CREATE TABLE #CCI (JSON_Data NVARCHAR(4000))
GO
SELECT JSON_Data =
(
SELECT h.SalesOrderID
, h.OrderDate
, Product = p.[Name]
, d.OrderQty
, p.ListPrice
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
INTO #Heap
FROM Sales.SalesOrderHeader h
JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
JOIN Production.Product p ON d.ProductID = p.ProductID
INSERT INTO #CCI
SELECT * FROM #Heap
CREATE CLUSTERED COLUMNSTORE INDEX CCI ON #CCI
SELECT o.[name], s.used_page_count / 128.
FROM sys.indexes i
JOIN sys.dm_db_partition_stats s ON i.[object_id] = s.[object_id]
AND i.index_id = s.index_id
JOIN sys.objects o ON i.[object_id] = o.[object_id]
WHERE i.[object_id] IN (OBJECT_ID('#CCI'), OBJECT_ID('#Heap'))
Режим batch для колумнстора при последовательном плане более эффективный. Параллельный план дает одинаковую производительностью с поправкой на размер данных:
SET STATISTICS IO, TIME ON
SELECT JSON_VALUE(JSON_Data, '$.OrderDate')
, AVG(CAST(JSON_VALUE(JSON_Data, '$.ListPrice') AS MONEY))
FROM #CCI
GROUP BY JSON_VALUE(JSON_Data, '$.OrderDate')
OPTION(MAXDOP 1)
--OPTION(RECOMPILE, QUERYTRACEON 8649)
SELECT JSON_VALUE(JSON_Data, '$.OrderDate')
, AVG(CAST(JSON_VALUE(JSON_Data, '$.ListPrice') AS MONEY))
FROM #Heap
GROUP BY JSON_VALUE(JSON_Data, '$.OrderDate')
OPTION(MAXDOP 1)
--OPTION(RECOMPILE, QUERYTRACEON 8649)
SET STATISTICS IO, TIME ON
/*
OPTION(MAXDOP 1)
#CCI: CPU = 516 ms, Elapsed = 568 ms
#Heap: CPU = 1015 ms, Elapsed = 1137 ms
OPTION(RECOMPILE, QUERYTRACEON 8649)
#CCI: CPU = 531 ms, Elapsed = 569 ms
#Heap: CPU = 828 ms, Elapsed = 511 ms
*/
При использовании OPENJSON при последовательном плане различий никаких. При параллельном выполнении на моем компе чтение в batch режиме менее эффективное:
SET STATISTICS IO, TIME ON
SELECT OrderDate, AVG(ListPrice)
FROM #CCI
CROSS APPLY OPENJSON(JSON_Data)
WITH (
OrderDate DATE
, ListPrice MONEY
)
GROUP BY OrderDate
OPTION(MAXDOP 1)
--OPTION(RECOMPILE, QUERYTRACEON 8649)
SELECT OrderDate, AVG(ListPrice)
FROM #Heap
CROSS APPLY OPENJSON(JSON_Data)
WITH (
OrderDate DATE
, ListPrice MONEY
)
GROUP BY OrderDate
OPTION(MAXDOP 1)
--OPTION(RECOMPILE, QUERYTRACEON 8649)
SET STATISTICS IO, TIME OFF
/*
OPTION(MAXPOD 1)
#CCI: CPU = 875 ms, Elapsed = 902 ms
#Heap: CPU = 812 ms, Elapsed = 927 ms
OPTION(RECOMPILE, QUERYTRACEON 8649)
#CCI: CPU = 875 ms, Elapsed = 909 ms
#Heap: CPU = 859 ms, Elapsed = 366 ms
*/
Пока что в SQL Server 2016 / 2017 не предусмотрена возможность создания индексов по аналогии с GIN. Аргументация у разработчиков примерно такая: «все и так быстро, но если не устраивает скорость, то может в следующей версии добавим». Примерно по такому принципу в SQL Server 2012 SP1 добавили селективные XML индексы.
В тоже время, есть некоторые обходные пути как можно ускорить поиск по произвольной JSON структуре. Можно создать кластерный ColumnStore и хранить в нем JSON. При парсинге значений будет использоваться batch режим вместо построчной обработки — это даст выигрыш при парсинге. Опять же тестировал у себя данный пример и не могу сказать, что batch режим кардинально быстрее. Репро на работе нет, но смогу вечером добавить.
Я бы еще добавил ссылку на отличный пост от Дмитрия Пилюгина: Медленно в приложении, быстро в SSMS… про parameter sniffing и не только. А так пост годный, прочитал с удовольствием.
На самом деле скорость везде примерно одинакова и зависит от свободного места в файле (будет ли AutoGrowth), включен ли IFI, скорости дисковой подсистемы (не забываем про WRITELOG) и тд.
Если все эти факторы учесть, то вот мой тест:
SET NOCOUNT ON
IF OBJECT_ID('t1', 'U') IS NOT NULL
DROP TABLE t1
GO
CREATE TABLE t1 (
id INT IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED
, d CHAR(250) NOT NULL DEFAULT ''
)
GO
DECLARE @count INT = 0
, @dt DATETIME = GETDATE()
WHILE @count < 500000 BEGIN
INSERT t1 DEFAULT VALUES
SET @count += 1
END
SELECT DATEDIFF(MILLISECOND, @dt, GETDATE()) -- 23 second
GO
CHECKPOINT
IF OBJECT_ID('t2', 'U') IS NOT NULL
DROP TABLE t2
GO
CREATE TABLE t2 (
id UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL PRIMARY KEY CLUSTERED
, d CHAR(250) NOT NULL DEFAULT ''
)
GO
DECLARE @count INT = 0
, @dt DATETIME = GETDATE()
WHILE @count < 500000 BEGIN
INSERT t2 DEFAULT VALUES
SET @count += 1
END
SELECT DATEDIFF(MILLISECOND, @dt, GETDATE()) -- 23 second
GO
CHECKPOINT
IF OBJECT_ID('t3', 'U') IS NOT NULL
DROP TABLE t3
GO
CREATE TABLE t3 (
id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() NOT NULL PRIMARY KEY CLUSTERED
, d CHAR(250) NOT NULL DEFAULT ''
)
GO
DECLARE @count INT = 0
, @dt DATETIME = GETDATE()
WHILE @count < 500000 BEGIN
INSERT t3 DEFAULT VALUES
SET @count += 1
END
SELECT DATEDIFF(MILLISECOND, @dt, GETDATE()) -- 23 second
GO
CHECKPOINT
GO
SELECT t.[name]
, size
, s.avg_fragmentation_in_percent
, s.avg_page_space_used_in_percent
FROM sys.objects t
JOIN (
SELECT p.[object_id]
, size = SUM(a.total_pages) * 8. / 1024
FROM sys.partitions p
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
GROUP BY p.[object_id]
) i ON t.[object_id] = i.[object_id]
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), i.[object_id], NULL, NULL, 'DETAILED') s
WHERE t.is_ms_shipped = 0
AND i.[object_id] > 255
AND t.[type] = 'U'
AND s.index_level = 0
name size avg_fragmentation_in_percent avg_page_space_used_in_percent
-------- ------------- ---------------------------- ------------------------------
t1 130.757812 0,365992680146397 97,4529651593773
t2 197.320312 99,0648999243962 67,572658759575
t3 135.632812 0,666975988864401 98,5015196441809
А то, что в случае использования NEWID() у нас будут чаще происходить операции разбиения страниц, соответственно и фрагментация будет выше (оттого больше логических чтений при работе с этой таблицей).
Поддерживаю… тул достаточно эффективно генерирует тестовые данные, вставляет большие куски через BULK INSERT… за это и нравится. Есть мелкие приколы, но на фоне конкурентов весьма хорош.
ИМХО самый лучший вариант: создается база, создается snapshot, накатываются данные, тест проверяется, snapshot откатывается и все по-новому. Тут Вам и минимальная нагрузка на диск + не надо чистить каждый раз базу. В идеале конечно включить Delayed Durability, чтобы снизить WRITELOG ожидания коих при OLTP нагрузке будет достаточно.
При создании новой базы настройки наследуются от базы model (если не учитывать некоторые нюансы). По дефолту в model стоит FULL. Если база создалась и для нее сделался бекап, то это приведет к разрастанию лога, если нет, то в Вашей базе будет неявно использоваться SIMPLE модель.
Для базы с тестами мы также включаем модель восстановления SIMPLE и отложенную запись в лог DELAYED_DURABILITY = FORCED. В теории это самый простой путь без лишних телодвижений снизить время на подготовку данных для теста.
В общем, по моему мнению, чуточку мутно. Тем более, что начиная с 2016-го сиквела добавили аппаратные приблуды, которые дешевле под OLTP нагрузку и менять в коде ничего не нужно. А для DW иногда достаточно сделать кластерный ColumnStore секционированный. Хотя буду объективным истины в чем-то одном нет. Ваш продукт возможно хорош, но нет хорошего прува с технической составляющей. Я бы ее с радостью почитал.
С наступающими праздниками :)
В плане статьи, поддержу мнение, что Updater подлые вещи иногда делает. Но что мешает в момент установки все настроить? Отключить обновление, телеметрию на уровне SQL Server и системы в целом. И тогда подобных сюрпризов будет по минимуму. Опять же не берем во внимание Azure — он живет по другим правилам.
Режим batch для колумнстора при последовательном плане более эффективный. Параллельный план дает одинаковую производительностью с поправкой на размер данных:
При использовании OPENJSON при последовательном плане различий никаких. При параллельном выполнении на моем компе чтение в batch режиме менее эффективное:
В тоже время, есть некоторые обходные пути как можно ускорить поиск по произвольной JSON структуре. Можно создать кластерный ColumnStore и хранить в нем JSON. При парсинге значений будет использоваться batch режим вместо построчной обработки — это даст выигрыш при парсинге. Опять же тестировал у себя данный пример и не могу сказать, что batch режим кардинально быстрее. Репро на работе нет, но смогу вечером добавить.
Я бы еще добавил ссылку на отличный пост от Дмитрия Пилюгина: Медленно в приложении, быстро в SSMS… про parameter sniffing и не только. А так пост годный, прочитал с удовольствием.
Heap, Clustered index, Columnstore, InMemory… какая пятая? Internal или что-то новое? Из Вашего описания не совсем понятно, про что пойдет речь.
Если все эти факторы учесть, то вот мой тест:
Скорость выполнения в миллисекундах:
Ожидания:
И самое интересное (так в чем же различие):
А то, что в случае использования NEWID() у нас будут чаще происходить операции разбиения страниц, соответственно и фрагментация будет выше (оттого больше логических чтений при работе с этой таблицей).
Кроме того, в этот пост еще хорош для ознакомления Первичный ключ – GUID или автоинкремент?
Если короче, то в общем поддерживаю точку зрения BalinTomsk
Для базы с тестами мы также включаем модель восстановления SIMPLE и отложенную запись в лог DELAYED_DURABILITY = FORCED. В теории это самый простой путь без лишних телодвижений снизить время на подготовку данных для теста.