Tag Archives: performance

Про скорость поиска по строкам и таблицу с настройками

Довольно широко распространена практика, хранить настройки приложений в базе данных в отдельной таблице примерно следующей структуры.

use [tempdb];
go


if object_id('dbo.settings', 'U') is not null
	drop table dbo.settings;
go

create table dbo.settings (
	[name] varchar(128) not null,
	[value] sql_variant null,

	constraint [pk_settings] primary key clustered ([name])
);
go

Настройки могут быть самые разные: какие-то процессы могут постоянно обновлять свой статус, хранить дату последней обработки данных; также это могут быть какие-то параметры для внешних сервисов или ETL процессов. В общем, способов применения очень много, и такой подход встречается очень часто. Но я, конкретно, хотел бы остановиться на одной довольно распространенной ошибке, которая встречается в этом случае, да и в некоторых других. Очень часто названия параметров содержат довольно большой одинаковый префикс, например: Process1_config_param1, Process1_last_active_time, Process2_config_param1, Process2_last_active_time и т.п. Я надеюсь, вы уловили смысл. У нас в базе данных могут быть десятки, сотни или даже тысячи строк, у которых 5-30 первых символов совпадает. И это может повлиять на производительность, т.к. при поиске по названию придется проверять все эти строки. Давайте устроим небольшой тест на производительность и посмотрим, как себя ведет SQL Server.

В первом тесте я вставлю 1000 значений в таблицу, у которых первые 10 символов будут одинаковые, а последние 4 уникальные, и запущу подряд 3 миллиона выборок по одному из значений.

truncate table dbo.settings;
alter table dbo.settings rebuild;

insert into dbo.settings (name, value)
select replicate('A', 10) + replicate('0', 4 - ceiling(log(rn + 1, 10))) + cast(rn as varchar(10)), null
from (
	select top (1000) row_number() over (order by (select 1)) as rn
	from sys.all_columns
) as num;

declare @test sql_variant, @i int = 1;
while @i <= 3000000
begin

	select @test = value
	from dbo.settings
	where name = 'AAAAAAAAAA0100'

	set @i += 1

end

В следующем тесте я сделаю первые 4 символа уникальные, а остальные совпадающие. Т.е. я убрал все повторяющиеся куски в конец строки, а уникально идентифицирующие в начало.

truncate table dbo.settings;
alter table dbo.settings rebuild;

insert into dbo.settings (name, value)
select format(rn, '0000') + replicate('A', 10), null
from (
	select top (1000) row_number() over (order by (select 1)) as rn
	from sys.all_columns
) as num;



declare @test sql_variant, @i int = 1;
while @i <= 3000000
begin

	select @test = value
	from dbo.settings
	where name = '0100AAAAAAAAAA'

	set @i += 1

end

Ну и в финальном тесте я вообще убрал повторяющиеся значения, а оставил только короткие уникальные наименования.

truncate table dbo.settings;
alter table dbo.settings rebuild;

insert into dbo.settings (name, value)
select format(rn, '0000'), null
from (
	select top (1000) row_number() over (order by (select 1)) as rn
	from sys.all_columns
) as num;



declare @test sql_variant, @i int = 1;
while @i <= 3000000
begin

	select @test = value
	from dbo.settings
	where name = '0100'

	set @i += 1

end

Вы можете запустит все 3 теста у себя и сравнить результаты. Я же хочу поделиться своими. Тесты запускались несколько раз, результаты были достаточно стабильны. Точное время выполнения приводить не имеет смысла, поделюсь лишь сравнительными итогами. Если взять за основу первое решение, то второй вариант относительно него выполняется на 10% быстрее, что очень существенно. Если взять третий тест, то он будет приблизительно на 15% быстрее чем первый и чуть больше, чем на 5% быстрее, чем второй.

Итого, получается, что следует использовать максимально короткие названия параметров и называть их так, чтобы уже с первых символов они были бы уникально идентифицированы. Разница, как мы видим, довольно существенна и поможет значительно ускорить поиск.

Влияние настроек энергопотребления на производительность SQL Server

Сегодняшняя моя заметка будет посвящена тому, как настройки энергопотребления в операционной системе и в BIOS могут влиять на производительность SQL Server. Сразу хочу предупредить, что данная статья не является руководством к действию. Все результаты, опубликованные в этой заметке, основаны на тестировании только одной определенной нагрузки и приводятся только в качестве примера. Ни в коем случае не стоит применять указанные в статье настройки преварительно не проведя тестирование на вашей нагрузке.

Для тестирования использовалась платформа Supermicro SuperServer 6017R-N3RF4+ с парой процессоров Intel® Xeon® Processor E5-2650 и включенным Hyper Threading, 64 Гб оперативной памяти и установленным Microsoft SQL Server 2008 R2.

Конфигурацию дисковой подсистемы я не привожу, т.к. для тестирования я выбрал запрос, который дает нагрузку только на процессор. Данные из таблицы объемом приблизительно 500 Мб и содержащие поле типа XML предварительно были закэшированы в Buffer Pool. Запрос проводит выборку из вышеуказанных данных, проводя над XML данными несколько операций с использованием методов nodes, query и value. Запрос я запускал несколько раз для каждой комбинации настроек как параллельно на всех логических процессорах, так и с опцией MAXDOP = 1.

За настройки энергопотребления CPU в BIOS отвечают следующие настройки в разделе CPU Power Management Configuration: Power Technology и Energy Performance Bias. По умолчанию они имеют значения Energy Efficiency и Balanced Performance. Соответственно, чтобы отключить экономию энергии для CPU я переключал эти параметры в значения Disabled и Performance соответственно.

В операционной системе в панели управления в разделе Power Options также можно выбрать один из 3х режимов энергопотребления. Значение по умолчанию Balanced. Причем перезапуск SQL Server или операционной системы после изменения не требуется – все начинает работать сразу.

Теперь я привожу результаты тестирования. Для начала посмотрим на время выполнения запроса с опцией MAXDOP = 1.

BIOS – настройки по умолчанию BIOS, отключена экономия энергии для CPU
Power Options: Balanced 3 мин. 15 сек. 4 мин. 18 сек.
Power Options: High performance 3 мин. 10 сек. 4 мин. 18 сек.
Power Options: Power saver 5 мин. 48 сек. 4 мин. 18 сек.

Как мы видим, если оставить настройки BIOS по умолчанию и менять параметры энергопотребления в операционной системе, то можно приблизительно на 2.5% ускорить запрос только выбрав значение High performance в Power Option. И напротив, что стало для меня удивлением, если изменить настройки в BIOS – мы наоборот, теряем в производительности.

А теперь давайте посмотрим на результаты, когда запрос выполнялся параллельно на 32 логических процессорах.

BIOS – настройки по умолчанию BIOS, отключена экономия энергии для CPU
Power Options: Balanced 25-26 сек. 21 сек.
Power Options: High performance 18-19 сек. 21 сек.
Power Options: Power saver 29 сек. 21 сек.

И опять получились довольно интересные результаты. Если изменить настройки BIOS по умолчанию, то мы получаем некоторый выигрыш в производительности. Но наилучший результат показывает ситуация, когда настройки BIOS не изменяются, а в настройках энергопотребления операционной системы мы выбираем High performance.

Итак, согласно результатам получается, что не стоит изменять настройки BIOS, но изменение параметров операционной системы может ускорить время выполнения от 2.5 до 30 процентов, что, согласитесь, очень и очень неплохо. Но опять же напомню, что я проверял только одну определенную нагрузку, хотя она и была в основном на CPU. Если у вас есть возможность провести подобное тестирование нагрузки на процессор, то призываю вас делиться результатами в комментариях – обсудим.