Ой знаете реально хотел все затереть, но Хабр не дал :) пусть все остается как есть.
Эмоционально или нет... скажем так :) у меня свое мнение. Я гамна уже насмотрелся в своей жизни оттого что делает братский народ в моей стране и городе.
Если честно такая дичь... не буду дублировать что люди ранее писали. Хочется ускорить запрос... избавляемся от любых преобразований на индексном поле... добавляем покрывающий индекс + вычиляемое поле... помечаем что оно имеет бинарный коллейшен.
Краткий пример о чем речь:
USE AdventureWorks2014
GO
SELECT AddressLine1
FROM Person.[Address]
WHERE AddressLine1 LIKE '%100%'
------------------------------------------------------------------
USE [master]
GO
IF DB_ID('test') IS NOT NULL BEGIN
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE test
END
GO
CREATE DATABASE test COLLATE Latin1_General_100_CS_AS
GO
ALTER DATABASE test MODIFY FILE (NAME = N'test', SIZE = 64MB)
GO
ALTER DATABASE test MODIFY FILE (NAME = N'test_log', SIZE = 64MB)
GO
USE test
GO
CREATE TABLE t (
ansi VARCHAR(100) NOT NULL
, unicod NVARCHAR(100) NOT NULL
)
GO
;WITH
E1(N) AS (
SELECT * FROM (
VALUES
(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1)
) t(N)
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
E4(N) AS (SELECT 1 FROM E2 a, E2 b),
E8(N) AS (SELECT 1 FROM E4 a, E4 b)
INSERT INTO t
SELECT v, v
FROM (
SELECT TOP(50000) v = REPLACE(CAST(NEWID() AS VARCHAR(36)) + CAST(NEWID() AS VARCHAR(36)), '-', '')
FROM E8
) t
GO
------------------------------------------------------------------
ALTER TABLE t
ADD ansi_bin AS UPPER(ansi) COLLATE Latin1_General_100_BIN2
ALTER TABLE t
ADD unicod_bin AS UPPER(unicod) COLLATE Latin1_General_100_BIN2
CREATE NONCLUSTERED INDEX ansi ON t (ansi)
CREATE NONCLUSTERED INDEX unicod ON t (unicod)
CREATE NONCLUSTERED INDEX ansi_bin ON t (ansi_bin)
CREATE NONCLUSTERED INDEX unicod_bin ON t (unicod_bin)
GO
------------------------------------------------------------------
SET STATISTICS TIME, IO ON
SELECT COUNT_BIG(*)
FROM t
WHERE ansi LIKE '%AB%'
SELECT COUNT_BIG(*)
FROM t
WHERE unicod LIKE '%AB%'
SELECT COUNT_BIG(*)
FROM t
WHERE ansi_bin LIKE '%AB%' --COLLATE Latin1_General_100_BIN2
SELECT COUNT_BIG(*)
FROM t
WHERE unicod_bin LIKE '%AB%' --COLLATE Latin1_General_100_BIN2
SET STATISTICS TIME, IO OFF
Когда один поток работает, то остальные потоки с тем же @ReceiptId ждут до 5 секунд своей возможности получить экслюзивный лок. Если за 5 секунд это не получается сделать то можно генерировать осмысленную ошибку или как-то это все протоколировать руками.
Делал нагрузочное тестирование данного кода и не заметил каких-либо проблем - ни мемори-ликов, ни каких-то просадок производительности.
Пусть и с опозданием, но решил вставить свои "пять копеек". Пример описанный в статье мне понравился. Приведу рабочий вариант по немного другой теме (но похожей) вдруг кому будет полезным:
DECLARE @ReceiptId UNIQUEIDENTIFIER
DECLARE @LockKey CHAR(36) = CAST(@ReceiptId AS CHAR(36))
, @LockResult INT
EXEC @LockResult = sys.sp_getapplock @Resource = @LockKey
, @LockMode = 'Exclusive'
, @LockOwner = 'Session'
, @LockTimeout = 5000
IF @LockResult IN (0, 1) BEGIN
IF EXISTS(SELECT * FROM dbo.Receipt WHERE ReceiptId = @ReceiptId) BEGIN
/* ReceiptAlreadyPosted */
END
ELSE BEGIN
/* PostReceipt */
END
EXEC sys.sp_releaseapplock @Resource = @LockKey
, @LockOwner = 'Session'
END
ELSE BEGIN
/* Unable to acquire exclusive lock */
END
У нас сейчас проект по розничной торговле и иногда возникает ситуация, когда одна и та же квитанция повторно отправляется с кеш-устройства через API в базу (например, из-за нестабильного сетевого соединения). Это приводит к созданию для таких записей нескольких отдельных промежуточных сессий в рамках которых нужно запроцессать одну и ту же квитанцию.
В большинстве случаев дополнительные сеансы автоматически отбрасываются, но если несколько заданий попытаются обработать такие промежуточные сеансы одновременно (а так и происходит), все, кроме одного такого сеанса, потерпят неудачу, что приведет к ложному предупреждению об ошибке публикации квитанции (мы не можем вставить дубликат ключа в уникальный индекс).
Если человеческим языком, чтобы перестраховаться от дубликата по заранее известному уникальному ключу мы используем sp_getapplock, который без лишних блокировок помогает проверять дубликаты.
Вставлю свои пару копеек. Анализировать значение avg_page_space_used_in_percent правильно, но на практике получать достаточно затратно с точки зрения ресурсов — это приводит к полному (или частичному в некоторых частных случаях) сканированию выбранного индекса. Кроме того, одно дело, когда человек вызывает точечно dm_db_index_physical_stats указывая точно какой индекс, другое дело сканить всю базу (которая может не один терабайт весить).
Можно немного посмотреть с другой стороны… залесть в sys.allocation_units и если есть большое расхождение между total_pages и used_pages, то это потенциальный кандидат для ребилда.
За опросник слезу пустил :) когда увидел кого перечислили в сторонних решениях. Честно рад что моим тулом пользуются.
Спасибо за пост. Реально полезно. Никогда не доводилось In-Memory с FULL моделью восстановления использовать, а то что на SIMPLE было так сильно не приводило к росту In-Memory файловой группы, но ее легко не фринкнуть и это большая проблема.
И докину еще одну проблему с которой столкнулся давно (сейчас не знаю исправили или нет, но было до 2016 SP2) когда таблица In-Memory обернута в Native Compile хранимку то если нет плана выполенения… план генерируется но сам запрос падает по ошибке. Повторный запрос выполняется корректно потому что план уже в кеше. И когда памяти не хватало было весьма занимательно разбираться почему отваливались транзакции в рамках таких вот конструкций.
Если по правде я потерял нить повествования о том с какой проблемой вы боритесь. Открывается соединение выбирается база в рамках базы высканиваются все индексы требующие обслуживания проставляются чекбоксы напротив тех индексов которые нужно запроцессать и становится доступна кнопка по их обслуживаниваю. Не понимаю сути вашей ситуации и в чем сложность.
Обратите внимание что вам нужно выбрать те индексы которые хотите обслужить (чекбоксы слева) и только потом кнопка будет доступна. Либо я не понял вашего вопроса. Если что есть инструкция.
Касательно ограничений колумнсторов и In-Memory стало интересно и потестировал:
Скрипт создания базы
USE [master]
GO
SET NOCOUNT ON
SET STATISTICS IO, TIME OFF
IF DB_ID('express') IS NOT NULL BEGIN
ALTER DATABASE [express] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [express]
END
GO
CREATE DATABASE [express]
ON PRIMARY (NAME = N'express', FILENAME = N'D:\express.mdf', SIZE = 200 MB, FILEGROWTH = 100 MB)
LOG ON (NAME = N'express_log', FILENAME = N'D:\express_log.ldf', SIZE = 200 MB, FILEGROWTH = 100 MB)
ALTER DATABASE [express] SET AUTO_CLOSE OFF
ALTER DATABASE [express] SET RECOVERY SIMPLE
ALTER DATABASE [express] SET MULTI_USER
ALTER DATABASE [express] SET DELAYED_DURABILITY = ALLOWED
ALTER DATABASE [express] ADD FILEGROUP [MEM] CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE [express] ADD FILE (NAME = 'MEM', FILENAME = 'D:\MEM') TO FILEGROUP [MEM]
ALTER DATABASE [express] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON
GO
USE [express]
GO
DROP TABLE IF EXISTS [CCL]
CREATE TABLE [CCL] (
[INT] INT NOT NULL
, [VARCHAR] VARCHAR(100) NOT NULL
, [DATETIME] DATETIME NOT NULL
)
GO
CREATE CLUSTERED COLUMNSTORE INDEX IX ON [CCL] WITH (DATA_COMPRESSION = COLUMNSTORE)
GO
DECLARE @i INT = 0
lbl:
;WITH E1(N) AS (SELECT * FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(N))
, E2(N) AS (SELECT '1' FROM E1 a, E1 b)
, E4(N) AS (SELECT '1' FROM E2 a, E2 b)
, E8(N) AS (SELECT '1' FROM E4 a, E4 b)
INSERT INTO [CCL] WITH(TABLOCK) ([INT], [VARCHAR], [DATETIME])
SELECT TOP(5000000)
ROW_NUMBER() OVER (ORDER BY 1/0)
, CAST(ROW_NUMBER() OVER (ORDER BY 1/0) AS VARCHAR(100))
, DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY 1/0) % 100, '20180101')
FROM E8
SET @i += 1
IF @i < 20 GOTO lbl
SELECT 'DATA', CAST(SUM(size_in_bytes) / (1024. * 1024) AS DECIMAL(18,2))
FROM sys.indexes i
CROSS APPLY sys.fn_column_store_row_groups(i.[object_id]) s
WHERE i.[type] IN (5, 6)
UNION ALL
SELECT 'DICT', CAST(SUM(on_disk_size) / (1024. * 1024) AS DECIMAL(18,2))
FROM sys.column_store_dictionaries
GO
DROP TABLE IF EXISTS [MEM]
CREATE TABLE [MEM] (A INT PRIMARY KEY NONCLUSTERED, B DATETIME INDEX IX2 NONCLUSTERED, C VARCHAR(4000))
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
GO
DECLARE @i INT
, @s DATETIME
, @runs INT = 10
SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs BEGIN
;WITH E1(N) AS (SELECT * FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(N))
, E2(N) AS (SELECT '1' FROM E1 a, E1 b)
, E4(N) AS (SELECT '1' FROM E2 a, E2 b)
, E8(N) AS (SELECT '1' FROM E4 a, E4 b)
INSERT INTO [MEM] ([A], [B], [C])
SELECT TOP(500000)
ROW_NUMBER() OVER (ORDER BY 1/0) + (@i * 1000000)
, GETDATE()
, NEWID()
FROM E8
SET @i += 1
END
SELECT *
FROM sys.dm_db_xtp_table_memory_stats
Если говорить о колумнсторах, то там размер данных и словарей может превышать 350Мб:
---- ----------
DATA 509.02
DICT 1072.04
При этом для In-Memory таблиц при превышении размера в 350Мб получаем такую ошибку:
Msg 41823, Level 16, State 109, Line 85
Could not perform the operation because the database has reached its quota for in-memory tables. This error may be transient. Please retry the operation. See 'http://go.microsoft.com/fwlink/?LinkID=623028' for more information.
Я немного противник обновлений, но это не говорит что я категорически против их. Просто они должны настояться как хорошее вино, чтобы был видел эффект от их использования сообществом. Очень хорошо помогает этот сайт.
Ещё бы добавить сценарий как разделяют большую базу данных на маленькие в Express, т к есть ограничения на размеры (много БД и одна с представлениями, в которой объединяются все нужные таблицы из всех малых БД)
Это в статье есть. Привел пример вертикального шардирования в рамках одного инстанса.
также как происходит поддержка всего этого
Увы тут быстрого ответа не выйдет — все зависит от ситуации. Например, всякие там AlwaysOn на Express Edition не сделаешь. С другой стороны, что мешает перевести базу в Full Recovery делать бекапы лога и их через PowerShell разворачивать на другом сервере для копии этой базы в режиме Read-Only. Не скажу что идеально, но дешевый аналог отказоустойчивости под репортную нагрузку себя оправдывает.
Также рекомендую скрипты и длинные выводы по ним вложить в спойлеры
Сделаем, но чуть позже. Увы сейчас редактор встроенный сильно тормозит потому правка статьи дело не из легких.
Странно что прога работает медленнее чем ваши скрипты. Можете постучаться в личку? Скажем в телеграмм или по скайпу (в профиле линки есть). Хочется разобраться в ситуации и пофиксить если это реально косяк с моей стороны.
Почитать нет. Ну а стрим на ютубе как бы как и обычное видео... потому велкомс
Ой знаете реально хотел все затереть, но Хабр не дал :) пусть все остается как есть.
Эмоционально или нет... скажем так :) у меня свое мнение. Я гамна уже насмотрелся в своей жизни оттого что делает братский народ в моей стране и городе.
Если хочется конструктива велкомс: https://www.youtube.com/watch?v=wXH3fUN0PsM
Сегодня будем с другом стримить и паралельно шутить о русском мире а Харькове, колумсторах и немного поговорим о жизни :)
Если честно такая дичь... не буду дублировать что люди ранее писали. Хочется ускорить запрос... избавляемся от любых преобразований на индексном поле... добавляем покрывающий индекс + вычиляемое поле... помечаем что оно имеет бинарный коллейшен.
Краткий пример о чем речь:
Когда один поток работает, то остальные потоки с тем же @ReceiptId ждут до 5 секунд своей возможности получить экслюзивный лок. Если за 5 секунд это не получается сделать то можно генерировать осмысленную ошибку или как-то это все протоколировать руками.
Делал нагрузочное тестирование данного кода и не заметил каких-либо проблем - ни мемори-ликов, ни каких-то просадок производительности.
Пусть и с опозданием, но решил вставить свои "пять копеек". Пример описанный в статье мне понравился. Приведу рабочий вариант по немного другой теме (но похожей) вдруг кому будет полезным:
У нас сейчас проект по розничной торговле и иногда возникает ситуация, когда одна и та же квитанция повторно отправляется с кеш-устройства через API в базу (например, из-за нестабильного сетевого соединения). Это приводит к созданию для таких записей нескольких отдельных промежуточных сессий в рамках которых нужно запроцессать одну и ту же квитанцию.
В большинстве случаев дополнительные сеансы автоматически отбрасываются, но если несколько заданий попытаются обработать такие промежуточные сеансы одновременно (а так и происходит), все, кроме одного такого сеанса, потерпят неудачу, что приведет к ложному предупреждению об ошибке публикации квитанции (мы не можем вставить дубликат ключа в уникальный индекс).
Если человеческим языком, чтобы перестраховаться от дубликата по заранее известному уникальному ключу мы используем sp_getapplock, который без лишних блокировок помогает проверять дубликаты.
Можно немного посмотреть с другой стороны… залесть в sys.allocation_units и если есть большое расхождение между total_pages и used_pages, то это потенциальный кандидат для ребилда.
За опросник слезу пустил :) когда увидел кого перечислили в сторонних решениях. Честно рад что моим тулом пользуются.
И докину еще одну проблему с которой столкнулся давно (сейчас не знаю исправили или нет, но было до 2016 SP2) когда таблица In-Memory обернута в Native Compile хранимку то если нет плана выполенения… план генерируется но сам запрос падает по ошибке. Повторный запрос выполняется корректно потому что план уже в кеше. И когда памяти не хватало было весьма занимательно разбираться почему отваливались транзакции в рамках таких вот конструкций.
Если говорить о колумнсторах, то там размер данных и словарей может превышать 350Мб:
При этом для In-Memory таблиц при превышении размера в 350Мб получаем такую ошибку:
Msg 41823, Level 16, State 109, Line 85
Could not perform the operation because the database has reached its quota for in-memory tables. This error may be transient. Please retry the operation. See 'http://go.microsoft.com/fwlink/?LinkID=623028' for more information.
Это в статье есть. Привел пример вертикального шардирования в рамках одного инстанса.
Увы тут быстрого ответа не выйдет — все зависит от ситуации. Например, всякие там AlwaysOn на Express Edition не сделаешь. С другой стороны, что мешает перевести базу в Full Recovery делать бекапы лога и их через PowerShell разворачивать на другом сервере для копии этой базы в режиме Read-Only. Не скажу что идеально, но дешевый аналог отказоустойчивости под репортную нагрузку себя оправдывает.
Сделаем, но чуть позже. Увы сейчас редактор встроенный сильно тормозит потому правка статьи дело не из легких.
Всем спасибо за отзывы.