Предисловие
Довольно часто пользователи, разработчики, администраторы и т д СУБД MS SQL Server встречаются с проблемами производительности БД или СУБД в целом.
В данной статье будут даны общие рекомендации по настройке оптимизации как БД, так и всей СУБД в целом. Также будут даны основные рекомендации по взаимодействию приложения .NET и MS SQL Server. Будут даны примеры решения на большинство из приведенных ниже рекомендаций.
В данной статье не будут рассматриваться оптимизация аппаратных ресурсов, самой ОС и использование разных встроенных фич для СУБД и ОС в целом, т к это заняло бы целую книгу.
Решение
В общей сложности существуют всего 3 блока рекомендаций по оптимизации с БД:
1) непосредственно сама оптимизация БД и СУБД в целом
2) оптимизация взаимодействия приложения и MS SQL Server (далее будем рассматривать взаимодействие приложения .NET и MS SQL Server)
3) оптимизация самих запросов
Сначала разберем первый блок.
Существуют всего 3 основных рекомендации по оптимизации БД и СУБД в целом:
1) устаревание процедурного кэша
2) неоптимальные индексы
3) неоптимальные статистики
Данный блок нужно прорабатывать в рамках регламентных работ от 1 раза в день до 1 раза в неделю в зависимости от потребностей всей информационной системы. Также стоит учитывать, что во время работы этого блока БД и СУБД в целом будут сильно нагружены по ресурсам. Поэтому данную работу необходимо проводить либо в часы минимальной нагрузки, либо на резервном сервере, либо распределив работу в течении всего дня (в последнем варианте тогда п.1 неприменим).
Также важно отметить, что этот блок необходимо выполнять после всех массовых обработок данных в рамках других регламентных работ.
Обычно сначала выполняют п.2 (оптимизируют индексы), а затем п.1 (очищают процедурный кэш), и после этого делают п.3 (обновляют статистику).
Разберем каждый пункт первого блока отдельно.
П.1 по устареванию процедурного кэша решается простой очисткой этого самого кэша путем вызова простой команды:
DBCC FLUSHPROCINDB('ИМЯ_БД');
Однако, важно отметить, что такое решение не всегда подходит под все БД и под все СУБД. Такое решение подходит для БД, обладающих следующими характеристиками:
1) размер данных БД до 500 ГБ
2) общий объем данных БД меняется каждый день, т е не просто появляются новые данные, а также существенные объемы обновляются и удаляются
Примерами таких БД являются TFS, CRM, NAV и 1C.
Если же БД имеет размер данных более 500 ГБ или данные только добавляются, а изменяются и удаляются в крайне малых объемах (под крайне малыми понимается, что объем изменений на столько многократно меньше всего объема неизменяемых данных, что этим объемом изменений можно пренебречь), то такое решение необходимо сперва проверить на тестируемой среде, максимально близкой к производственной. В любом случае при очистке процедурного кэша для таких БД и СУБД в целом в последующем обновление статистики будет очень долгой и ресурсоемкой операцией. Поэтому для таких БД и СУБД в целом п.1 по очистке процедурного кэша вначале нужно отменить-по крайней мере до получения результатов испытаний на тестируемой среде.
Для БД, в которых можно применить п.1, и СУБД, содержащих только такие БД, можно реализовать следующую хранимую процедуру [srv].[AutoUpdateStatisticsCache] для последующего использования:
USE [БД для администрирования]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [srv].[AutoUpdateStatisticsCache]
@DB_Name nvarchar(255)=null,
@IsUpdateStatistics bit=0
AS
BEGIN
/*
очистка кеша с последующим обновлением статистики по всем несистемным БД
*/
SET NOCOUNT ON;
declare @tbl table (Name nvarchar(255), [DB_ID] int);
declare @db_id int;
declare @name nvarchar(255);
declare @str nvarchar(255);
--получаем все БД, которые не помечены как только для чтения
insert into @tbl(Name, [DB_ID])
select name, database_id
from sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb', 'distribution')
and is_read_only=0 --write
and state=0 --online
and user_access=0 --MULTI_USER
and is_auto_close_on=0
and (name=@DB_Name or @DB_Name is null);
while(exists(select top(1) 1 from @tbl))
begin
--получаем идентификатор нужной БД
select top(1)
@db_id=[DB_ID]
, @name=Name
from @tbl;
--очищаем кэш по id БД
DBCC FLUSHPROCINDB(@db_id);
if(@IsUpdateStatistics=1)
begin
--обновляем статистику
set @str='USE'+' ['+@name+']; exec sp_updatestats;'
exec(@str);
end
delete from @tbl
where [DB_ID]=@db_id;
end
END
GO
Здесь в конце если выставлен параметр @IsUpdateStatistics в 1, то также идет обновление статистики по заданной БД в параметре @DB_Name. Если @IsUpdateStatistics=1, то тогда п.1 по очистке процедурного кэша необходимо проводить после решения проблемы п.2, т е в таком случае закрывается проблема п.3 по неоптимальным статистикам.
Текущий размер всего кэша планов и кэша планов запросов можно посмотреть, реализовав например следующее представление в БД по администрированию:
USE [БД для администрирования]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [inf].[vSizeCache] as
--Текущий размер всего кэша планов и кэша планов запросов (https://club.directum.ru/post/1125)
with tbl as (
select
TotalCacheSize = SUM(CAST(size_in_bytes as bigint)) / 1048576,
QueriesCacheSize = SUM(CAST((case
when objtype in ('Adhoc', 'Prepared')
then size_in_bytes else 0
end) as bigint)) / 1048576,
QueriesUseMultiCountCacheSize = SUM(CAST((case
when ((objtype in ('Adhoc', 'Prepared')) and (usecounts>1))
then size_in_bytes else 0
end) as bigint)) / 1048576,
QueriesUseOneCountCacheSize = SUM(CAST((case
when ((objtype in ('Adhoc', 'Prepared')) and (usecounts=1))
then size_in_bytes else 0
end) as bigint)) / 1048576
from sys.dm_exec_cached_plans
)
select
'Queries' as 'Cache',
(select top(1) QueriesCacheSize from tbl) as 'Cache Size (MB)',
CAST((select top(1) QueriesCacheSize from tbl) * 100 / (select top(1) TotalCacheSize from tbl) as int) as 'Percent of Total/Queries'
union all
select
'Total' as 'Cache',
(select top(1) TotalCacheSize from tbl) as 'Cache Size (MB)',
100 as 'Percent of Total/Queries'
union all
select
'Queries UseMultiCount' as 'Cache',
(select top(1) QueriesUseMultiCountCacheSize from tbl) as 'Cache Size (MB)',
CAST((select top(1) QueriesUseMultiCountCacheSize from tbl) * 100 / (select top(1) QueriesCacheSize from tbl) as int) as 'Percent of Queries/Queries'
union all
select
'Queries UseOneCount' as 'Cache',
(select top(1) QueriesUseOneCountCacheSize from tbl) as 'Cache Size (MB)',
CAST((select top(1) QueriesUseOneCountCacheSize from tbl) * 100 / (select top(1) QueriesCacheSize from tbl) as int) as 'Percent of Queries/Queries'
--option(recompile)
GO
Теперь разберем п.2. об неоптимальных индексах.
Под неоптимальными индексами будем понимать следующие 4 фактора:
1) сильно фрагментированные индексы
2) неиспользуемые индексы
3) недостающие индексы
4) индексы, которые для своего обслуживания используют больше затрат, чем приносят выигрыш в производительности
Под сильно фрагментированным индексом будем понимать следующие показатели фрагментации:
1) более 30% для индексов, размер которых не менее 20 страниц
2) более 20% для индексов, размер которых не менее 100 страниц
3) более 10% для индексов, размер которых не менее 500 страниц
Индексы из п.2 и п.4 можно определить, например, с помощью следующего представления [inf].[vDelIndexOptimize] для конкретной БД:
USE [ИМЯ_БД]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [inf].[vDelIndexOptimize] as
/*
возвращаются те индексы, которые не использовались в запросах более одного года
как пользователями, так и системой.
БД master, model, msdb и tempdb не рассматриваются
*/
select DB_NAME(t.database_id) as [DBName]
, SCHEMA_NAME(obj.schema_id) as [SchemaName]
, OBJECT_NAME(t.object_id) as [ObjectName]
, obj.Type as [ObjectType]
, obj.Type_Desc as [ObjectTypeDesc]
, ind.name as [IndexName]
, ind.Type as IndexType
, ind.Type_Desc as IndexTypeDesc
, ind.Is_Unique as IndexIsUnique
, ind.is_primary_key as IndexIsPK
, ind.is_unique_constraint as IndexIsUniqueConstraint
, (t.[USER_SEEKS]+t.[USER_SCANS]+t.[USER_LOOKUPS]+t.[SYSTEM_SEEKS]+t.[SYSTEM_SCANS]+t.[SYSTEM_LOOKUPS])-(t.[USER_UPDATES]+t.[System_Updates]) as [index_advantage]
, t.[Database_ID]
, t.[Object_ID]
, t.[Index_ID]
, t.USER_SEEKS
, t.USER_SCANS
, t.USER_LOOKUPS
, t.USER_UPDATES
, t.SYSTEM_SEEKS
, t.SYSTEM_SCANS
, t.SYSTEM_LOOKUPS
, t.SYSTEM_UPDATES
, t.Last_User_Seek
, t.Last_User_Scan
, t.Last_User_Lookup
, t.Last_System_Seek
, t.Last_System_Scan
, t.Last_System_Lookup
, ind.Filter_Definition,
STUFF(
(
SELECT N', [' + [name] +N'] '+case ic.[is_descending_key] when 0 then N'ASC' when 1 then N'DESC' end FROM sys.index_columns ic
INNER JOIN sys.columns c on c.[object_id] = obj.[object_id] and ic.[column_id] = c.[column_id]
WHERE ic.[object_id] = obj.[object_id]
and ic.[index_id]=ind.[index_id]
and ic.[is_included_column]=0
order by ic.[key_ordinal] asc
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)'),1,2,''
) as [Columns],
STUFF(
(
SELECT N', [' + [name] +N']' FROM sys.index_columns ic
INNER JOIN sys.columns c on c.[object_id] = obj.[object_id] and ic.[column_id] = c.[column_id]
WHERE ic.[object_id] = obj.[object_id]
and ic.[index_id]=ind.[index_id]
and ic.[is_included_column]=1
order by ic.[key_ordinal] asc
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)'),1,2,''
) as [IncludeColumns]
from sys.dm_db_index_usage_stats as t
inner join sys.objects as obj on t.[object_id]=obj.[object_id]
inner join sys.indexes as ind on t.[object_id]=ind.[object_id] and t.index_id=ind.index_id
where ((last_user_seek is null or last_user_seek <dateadd(year,-1,getdate()))
and (last_user_scan is null or last_user_scan <dateadd(year,-1,getdate()))
and (last_user_lookup is null or last_user_lookup <dateadd(year,-1,getdate()))
and (last_system_seek is null or last_system_seek <dateadd(year,-1,getdate()))
and (last_system_scan is null or last_system_scan <dateadd(year,-1,getdate()))
and (last_system_lookup is null or last_system_lookup <dateadd(year,-1,getdate()))
or (((t.[USER_UPDATES]+t.[System_Updates])>0) and (t.[SYSTEM_SEEKS]<=(t.[USER_UPDATES]+t.[System_Updates]-(t.[USER_SEEKS]+t.[USER_SCANS]+t.[USER_LOOKUPS]+t.[SYSTEM_SCANS]+t.[SYSTEM_LOOKUPS])))))
and t.database_id>4 and t.[object_id]>0
and ind.is_primary_key=0 --не является ограничением первичного ключа
and ind.is_unique_constraint=0 --не является ограничением уникальности
and t.database_id=DB_ID()
GO
Также важно проанализировать перекрытие индексов. Для этого создадим представление [srv].[vDelIndexInclude] в рассматриваемой БД:
USE [ИМЯ_БД]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [srv].[vDelIndexInclude] as
/*
Погорелов А.А.
Поиск перекрывающихся(лишних) индексов.
Если поля индекса перекрываются более широким индексом в том же порядке следования полей начиная с первого поля, то
этот индекс считается лишним, так как запросы могут использовать более широкий индекс.
http://www.sql.ru/blogs/andraptor/1218
*/
WITH cte_index_info AS (
SELECT
tSS.[name] AS [SchemaName]
,tSO.[name] AS [ObjectName]
,tSO.[type_desc] AS [ObjectType]
,tSO.[create_date] AS [ObjectCreateDate]
,tSI.[name] AS [IndexName]
,tSI.[is_primary_key] AS [IndexIsPrimaryKey]
,d.[index_type_desc] AS [IndexType]
,d.[avg_fragmentation_in_percent] AS [IndexFragmentation]
,d.[fragment_count] AS [IndexFragmentCount]
,d.[avg_fragment_size_in_pages] AS [IndexAvgFragmentSizeInPages]
,d.[page_count] AS [IndexPages]
,c.key_columns AS [IndexKeyColumns]
,COALESCE(ic.included_columns, '') AS [IndexIncludedColumns]
,tSI.is_unique_constraint
FROM
(
SELECT
tSDDIPS.[object_id] AS [object_id]
,tSDDIPS.[index_id] AS [index_id]
,tSDDIPS.[index_type_desc] AS [index_type_desc]
,MAX(tSDDIPS.[avg_fragmentation_in_percent]) AS [avg_fragmentation_in_percent]
,MAX(tSDDIPS.[fragment_count]) AS [fragment_count]
,MAX(tSDDIPS.[avg_fragment_size_in_pages]) AS [avg_fragment_size_in_pages]
,MAX(tSDDIPS.[page_count]) AS [page_count]
FROM
[sys].[dm_db_index_physical_stats] (DB_ID(), NULL, NULL , NULL, N'LIMITED') tSDDIPS
GROUP BY
tSDDIPS.[object_id]
,tSDDIPS.[index_id]
,tSDDIPS.[index_type_desc]
) d
INNER JOIN [sys].[indexes] tSI ON
tSI.[object_id] = d.[object_id]
AND tSI.[index_id] = d.[index_id]
INNER JOIN [sys].[objects] tSO ON
tSO.[object_id] = d.[object_id]
INNER JOIN [sys].[schemas] tSS ON
tSS.[schema_id] = tSO.[schema_id]
CROSS APPLY (
SELECT
STUFF((
SELECT
', ' + c.[name] +
CASE ic.[is_descending_key]
WHEN 1 THEN
'(-)'
ELSE
''
END
FROM
[sys].[index_columns] ic
INNER JOIN [sys].[columns] c ON
c.[object_id] = ic.[object_id]
and c.[column_id] = ic.[column_id]
WHERE
ic.[index_id] = tSI.[index_id]
AND ic.[object_id] = tSI.[object_id]
AND ic.[is_included_column] = 0
ORDER BY
ic.[key_ordinal]
FOR XML
PATH('')
)
,1, 2, ''
) AS [key_columns]
) c
CROSS APPLY (
SELECT
STUFF((
SELECT
', ' + c.[name]
FROM
[sys].[index_columns] ic
INNER JOIN [sys].[columns] c ON
c.[object_id] = ic.[object_id]
AND c.[column_id] = ic.[column_id]
WHERE
ic.[index_id] = tSI.[index_id]
AND ic.[object_id] = tSI.[object_id]
AND ic.[is_included_column] = 1
FOR XML
PATH('')
)
,1, 2, ''
) AS [included_columns]
) ic
WHERE
tSO.[type_desc] IN (
N'USER_TABLE'
)
AND OBJECTPROPERTY(tSO.[object_id], N'IsMSShipped') = 0
AND d.[index_type_desc] NOT IN (
'HEAP'
)
)
SELECT
t1.[SchemaName]
,t1.[ObjectName]
,t1.[ObjectType]
,t1.[ObjectCreateDate]
,t1.[IndexName] as [DelIndexName]
,t1.[IndexIsPrimaryKey]
,t1.[IndexType]
,t1.[IndexFragmentation]
,t1.[IndexFragmentCount]
,t1.[IndexAvgFragmentSizeInPages]
,t1.[IndexPages]
,t1.[IndexKeyColumns]
,t1.[IndexIncludedColumns]
,t2.[IndexName] as [ActualIndexName]
FROM
cte_index_info t1
INNER JOIN cte_index_info t2 ON
t2.[SchemaName] = t1.[SchemaName]
AND t2.[ObjectName] = t1.[ObjectName]
AND t2.[IndexName] <> t1.[IndexName]
AND PATINDEX(REPLACE(t1.[IndexKeyColumns], '_', '[_]') + ',%', t2.[IndexKeyColumns] + ',') > 0
WHERE
t1.[IndexIncludedColumns] = '' -- don't check indexes with INCLUDE columns
AND t1.[IndexIsPrimaryKey] = 0 -- don't check primary keys
AND t1.is_unique_constraint=0 -- don't check unique constraint
AND t1.[IndexType] NOT IN (
N'CLUSTERED INDEX'
,N'UNIQUE CLUSTERED INDEX'
) -- don't check clustered indexes
GO
Здесь важно отметить то, что даже если индекс попал под п.2 или п.4, то не нужно спешить его удалять. Необходимо убедиться в том, что он действительно не нужен системе. Для этого необходимо на тестовой среде, которая максимально приближена к производственной, провести необходимые испытания-сначала с индексом, а затем с удаленным индексом (сделать замеры и сравнить).
Недостающие индексы (п.3) можно определить, например, с помощью следующего представления [inf].[vRecomendateIndex]:
USE [ИМЯ_БД]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [inf].[vRecomendateIndex] as
-- Отсутствующие индексы из DMV
SELECT @@ServerName AS ServerName ,
DB_Name(ddmid.[database_id]) as [DBName] ,
t.name AS 'Affected_table' ,
ddmigs.user_seeks * ddmigs.avg_total_user_cost * (ddmigs.avg_user_impact * 0.01) AS index_advantage,
ddmigs.group_handle,
ddmigs.unique_compiles,
ddmigs.last_user_seek,
ddmigs.last_user_scan,
ddmigs.avg_total_user_cost,
ddmigs.avg_user_impact,
ddmigs.system_seeks,
ddmigs.last_system_scan,
ddmigs.last_system_seek,
ddmigs.avg_total_system_cost,
ddmigs.avg_system_impact,
ddmig.index_group_handle,
ddmig.index_handle,
ddmid.database_id,
ddmid.[object_id],
ddmid.equality_columns, -- =
ddmid.inequality_columns,
ddmid.[statement],
( LEN(ISNULL(ddmid.equality_columns, N'')
+ CASE WHEN ddmid.equality_columns IS NOT NULL
AND ddmid.inequality_columns IS NOT NULL THEN ','
ELSE ''
END) - LEN(REPLACE(ISNULL(ddmid.equality_columns, N'')
+ CASE WHEN ddmid.equality_columns
IS NOT NULL
AND ddmid.inequality_columns
IS NOT NULL
THEN ','
ELSE ''
END, ',', '')) ) + 1 AS K ,
COALESCE(ddmid.equality_columns, '')
+ CASE WHEN ddmid.equality_columns IS NOT NULL
AND ddmid.inequality_columns IS NOT NULL THEN ','
ELSE ''
END + COALESCE(ddmid.inequality_columns, '') AS Keys ,
ddmid.included_columns AS [include] ,
'Create NonClustered Index IX_' + t.name + '_missing_'
+ CAST(ddmid.index_handle AS VARCHAR(20))
+ ' On ' + ddmid.[statement] COLLATE database_default
+ ' (' + ISNULL(ddmid.equality_columns, '')
+ CASE WHEN ddmid.equality_columns IS NOT NULL
AND ddmid.inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(ddmid.inequality_columns, '') + ')'
+ ISNULL(' Include (' + ddmid.included_columns + ');', ';')
AS sql_statement ,
ddmigs.user_seeks ,
ddmigs.user_scans ,
CAST(( ddmigs.user_seeks + ddmigs.user_scans )
* ddmigs.avg_user_impact AS BIGINT) AS 'est_impact' ,
( SELECT DATEDIFF(Second, create_date, GETDATE()) Seconds
FROM sys.databases
WHERE name = 'tempdb'
) SecondsUptime
FROM sys.dm_db_missing_index_groups ddmig
INNER JOIN sys.dm_db_missing_index_group_stats ddmigs
ON ddmigs.group_handle = ddmig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details ddmid
ON ddmig.index_handle = ddmid.index_handle
INNER JOIN sys.tables t ON ddmid.OBJECT_ID = t.OBJECT_ID
WHERE ddmid.database_id = DB_ID()
--ORDER BY est_impact DESC;
GO
Здесь вернется список недостающих индексов по конкретной БД.
Если же нужен список недостающих индексов по всем БД СУБД, то можно его вывести с помощью определения следующего представления [inf].[vNewIndexOptimize]:
USE [БД для администрирования]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [inf].[vNewIndexOptimize] as
/*
степень полезности новых индексов
index_advantage: >50 000 - очень выгодно создать индекс
>10 000 - можно создать индекс, однако нужно анализировать и его поддержку
<=10000 - индекс можно не создавать
*/
SELECT @@ServerName AS ServerName,
DB_Name(ddmid.[database_id]) as [DBName],
OBJECT_SCHEMA_NAME(ddmid.[object_id], ddmid.[database_id]) as [Schema],
OBJECT_NAME(ddmid.[object_id], ddmid.[database_id]) as [Name],
ddmigs.user_seeks * ddmigs.avg_total_user_cost * (ddmigs.avg_user_impact * 0.01) AS index_advantage,
ddmigs.group_handle,
ddmigs.unique_compiles,
ddmigs.last_user_seek,
ddmigs.last_user_scan,
ddmigs.avg_total_user_cost,
ddmigs.avg_user_impact,
ddmigs.system_seeks,
ddmigs.last_system_scan,
ddmigs.last_system_seek,
ddmigs.avg_total_system_cost,
ddmigs.avg_system_impact,
ddmig.index_group_handle,
ddmig.index_handle,
ddmid.database_id,
ddmid.[object_id],
ddmid.equality_columns, -- =
ddmid.inequality_columns,
ddmid.[statement],
( LEN(ISNULL(ddmid.equality_columns, N'')
+ CASE WHEN ddmid.equality_columns IS NOT NULL
AND ddmid.inequality_columns IS NOT NULL THEN ','
ELSE ''
END) - LEN(REPLACE(ISNULL(ddmid.equality_columns, N'')
+ CASE WHEN ddmid.equality_columns
IS NOT NULL
AND ddmid.inequality_columns
IS NOT NULL
THEN ','
ELSE ''
END, ',', '')) ) + 1 AS K ,
COALESCE(ddmid.equality_columns, '')
+ CASE WHEN ddmid.equality_columns IS NOT NULL
AND ddmid.inequality_columns IS NOT NULL THEN ','
ELSE ''
END + COALESCE(ddmid.inequality_columns, '') AS Keys ,
ddmid.included_columns AS [include] ,
'Create NonClustered Index [IX_' + OBJECT_NAME(ddmid.[object_id], ddmid.[database_id]) + '_missing_'
+ CAST(ddmid.index_handle AS VARCHAR(20))
+ '] On ' + ddmid.[statement] COLLATE database_default
+ ' (' + ISNULL(ddmid.equality_columns, '')
+ CASE WHEN ddmid.equality_columns IS NOT NULL
AND ddmid.inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(ddmid.inequality_columns, '') + ')'
+ ISNULL(' Include (' + ddmid.included_columns + ');', ';')
AS sql_statement ,
ddmigs.user_seeks ,
ddmigs.user_scans ,
CAST(( ddmigs.user_seeks + ddmigs.user_scans )
* ddmigs.avg_user_impact AS BIGINT) AS 'est_impact' ,
( SELECT DATEDIFF(Second, create_date, GETDATE()) Seconds
FROM sys.databases
WHERE name = 'tempdb'
) SecondsUptime
FROM
sys.dm_db_missing_index_group_stats ddmigs
INNER JOIN sys.dm_db_missing_index_groups AS ddmig
ON ddmigs.group_handle = ddmig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS ddmid
ON ddmig.index_handle = ddmid.index_handle
--WHERE mid.database_id = DB_ID()
--ORDER BY migs_adv.index_advantage
GO
Здесь (как и в п.2 и п.4) также важно отметить то, что даже если индекс попал под п.3, то не нужно спешить его создавать. Необходимо убедиться в том, что он действительно нужен системе. Для этого необходимо на тестовой среде, которая максимально приближена к производственной, провести необходимые испытания-сначала без нового индекса, а затем с новым индексом (сделать замеры и сравнить). Довольно не редки случаи, когда новый индекс из п.3 станет в последствии индексом из п.2 или п.4.
Так как же решить проблему п.1 — избавиться от сильной степени фрагментированности индексов? В Интернете полно готовых решений по этому вопросу. Приведем еще один пример, который будет основан на рекомендациях из msdn.
Для этого создадим представление [inf].[vIndexDefrag] в тех БД, где необходимо рассмотреть уровень фрагментации индексов:
USE [ИМЯ_БД]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [inf].[vIndexDefrag]
as
with info as
(SELECT
ps.[object_id],
ps.database_id,
ps.index_id,
ps.index_type_desc,
ps.index_level,
ps.fragment_count,
ps.avg_fragmentation_in_percent,
ps.avg_fragment_size_in_pages,
ps.page_count,
ps.record_count,
ps.ghost_record_count
FROM sys.dm_db_index_physical_stats
(DB_ID()
, NULL, NULL, NULL ,
N'LIMITED') as ps
inner join sys.indexes as i on i.[object_id]=ps.[object_id] and i.[index_id]=ps.[index_id]
where ps.index_level = 0
and ps.avg_fragmentation_in_percent >= 10
and ps.index_type_desc <> 'HEAP'
and ps.page_count>=8 --1 экстент
and i.is_disabled=0
)
SELECT
DB_NAME(i.database_id) as db,
SCHEMA_NAME(t.[schema_id]) as shema,
t.name as tb,
i.index_id as idx,
i.database_id,
(select top(1) idx.[name] from [sys].[indexes] as idx where t.[object_id] = idx.[object_id] and idx.[index_id] = i.[index_id]) as index_name,
i.index_type_desc,i.index_level as [level],
i.[object_id],
i.fragment_count as frag_num,
round(i.avg_fragmentation_in_percent,2) as frag,
round(i.avg_fragment_size_in_pages,2) as frag_page,
i.page_count as [page],
i.record_count as rec,
i.ghost_record_count as ghost,
round(i.avg_fragmentation_in_percent*i.page_count,0) as func
FROM info as i
inner join [sys].[all_objects] as t on i.[object_id] = t.[object_id];
GO
Здесь будет дан список тех включенных индексов, которые не являются кучами, занимают не менее 1 экстента (8 страниц) и имеют уровень фрагментации не менее 10%.
Также создадим 2 таблицы в БД для администрирования — для сохранения списка обработанных индексов и для сохранения результатов обработанных индексов. Первая таблица нужна для того, чтобы не брать в расчет два раза одни и те же индексы, пока не обработаны все индексы:
USE [БД для администрирования]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [srv].[ListDefragIndex](
[db] [nvarchar](100) NOT NULL,
[shema] [nvarchar](100) NOT NULL,
[table] [nvarchar](100) NOT NULL,
[IndexName] [nvarchar](100) NOT NULL,
[object_id] [int] NOT NULL,
[idx] [int] NOT NULL,
[db_id] [int] NOT NULL,
[frag] [decimal](6, 2) NOT NULL,
[InsertUTCDate] [datetime] NOT NULL,
CONSTRAINT [PK_ListDefragIndex] PRIMARY KEY CLUSTERED
(
[object_id] ASC,
[idx] ASC,
[db_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]
GO
ALTER TABLE [srv].[ListDefragIndex] ADD CONSTRAINT [DF_ListDefragIndex_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO
USE [БД для администрирования]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [srv].[Defrag](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[db] [nvarchar](100) NOT NULL,
[shema] [nvarchar](100) NOT NULL,
[table] [nvarchar](100) NOT NULL,
[IndexName] [nvarchar](100) NOT NULL,
[frag_num] [int] NOT NULL,
[frag] [decimal](6, 2) NOT NULL,
[page] [int] NOT NULL,
[rec] [int] NULL,
[ts] [datetime] NOT NULL,
[tf] [datetime] NOT NULL,
[frag_after] [decimal](6, 2) NOT NULL,
[object_id] [int] NOT NULL,
[idx] [int] NOT NULL,
[InsertUTCDate] [datetime] NOT NULL,
CONSTRAINT [PK_Defrag] 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]
GO
ALTER TABLE [srv].[Defrag] ADD CONSTRAINT [DF_Defrag_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO
Далее создадим саму хранимую процедуру [srv].[AutoDefragIndex] по оптимизации индексов на каждой нужной БД (также можно и на системных БД) следующим образом:
USE [ИМЯ_БД]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [srv].[AutoDefragIndex]
@count int=null --кол-во одновременно обрабатываемых индексов
,@isrebuild bit=0 --позволять ли перестраиваться индексам (фрагментация которых свыше 30%)
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--определяем возможность перестраивать индекс в режиме ONLINE
declare @isRebuildOnline bit=CASE WHEN (CAST (SERVERPROPERTY ('Edition') AS nvarchar (max)) LIKE '%Enterprise%' OR CAST (SERVERPROPERTY ('Edition') AS nvarchar (max)) LIKE '%Developer%' OR CAST (SERVERPROPERTY ('Edition') AS nvarchar (max)) LIKE '%Evaluation%') THEN 1 ELSE 0 END;
declare @IndexName nvarchar(100)
,@db nvarchar(100)
,@db_id int
,@Shema nvarchar(100)
,@Table nvarchar(100)
,@SQL_Str nvarchar (max)=N''
,@frag decimal(6,2)
,@frag_after decimal(6,2)
,@frag_num int
,@page int
,@ts datetime
,@tsg datetime
,@tf datetime
,@object_id int
,@idx int
,@rec int;
--для обработки
declare @tbl table (
IndexName nvarchar(100)
,db nvarchar(100)
,[db_id] int
,Shema nvarchar(100)
,[Table] nvarchar(100)
,frag decimal(6,2)
,frag_num int
,[page] int
,[object_id] int
,idx int
,rec int
);
--для истории
declare @tbl_copy table (
IndexName nvarchar(100)
,db nvarchar(100)
,[db_id] int
,Shema nvarchar(100)
,[Table] nvarchar(100)
,frag decimal(6,2)
,frag_num int
,[page] int
,[object_id] int
,idx int
,rec int
);
set @ts = getdate()
set @tsg = @ts;
--выбираем индексы, которые фрагментированы не менее, чем на 10%
--и которые еще не выбирались
if(@count is null)
begin
insert into @tbl (
IndexName
,db
,[db_id]
,Shema
,[Table]
,frag
,frag_num
,[page]
,[object_id]
,idx
,rec
)
select ind.index_name,
ind.db,
ind.database_id,
ind.shema,
ind.tb,
ind.frag,
ind.frag_num,
ind.[page],
ind.[object_id],
ind.idx ,
ind.rec
from [inf].[vIndexDefrag] as ind
where not exists(
select top(1) 1 from [БД для администрирования].[srv].[ListDefragIndex] as lind
where lind.[db_id]=ind.database_id
and lind.[idx]=ind.idx
and lind.[object_id]=ind.[object_id]
)
--order by ind.[page] desc, ind.[frag] desc
end
else
begin
insert into @tbl (
IndexName
,db
,[db_id]
,Shema
,[Table]
,frag
,frag_num
,[page]
,[object_id]
,idx
,rec
)
select top (@count)
ind.index_name,
ind.db,
ind.database_id,
ind.shema,
ind.tb,
ind.frag,
ind.frag_num,
ind.[page],
ind.[object_id],
ind.idx ,
ind.rec
from [inf].[vIndexDefrag] as ind
where not exists(
select top(1) 1 from [БД для администрирования].[srv].[ListDefragIndex] as lind
where lind.[db_id]=ind.database_id
and lind.[idx]=ind.idx
and lind.[object_id]=ind.[object_id]
)
--order by ind.[page] desc, ind.[frag] desc
end
--если все индексы выбирались (т е выборка пуста)
--то очищаем таблицу обработанных индексов
--и начинаем заново
if(not exists(select top(1) 1 from @tbl))
begin
delete from [БД для администрирования].[srv].[ListDefragIndex]
where [db_id]=DB_ID();
if(@count is null)
begin
insert into @tbl (
IndexName
,db
,[db_id]
,Shema
,[Table]
,frag
,frag_num
,[page]
,[object_id]
,idx
,rec
)
select ind.index_name,
ind.db,
ind.database_id,
ind.shema,
ind.tb,
ind.frag,
ind.frag_num,
ind.[page],
ind.[object_id],
ind.idx ,
ind.rec
from [inf].[vIndexDefrag] as ind
where not exists(
select top(1) 1 from [БД для администрирования].[srv].[ListDefragIndex] as lind
where lind.[db_id]=ind.database_id
and lind.[idx]=ind.idx
and lind.[object_id]=ind.[object_id]
)
--order by ind.[page] desc, ind.[frag] desc
end
else
begin
insert into @tbl (
IndexName
,db
,[db_id]
,Shema
,[Table]
,frag
,frag_num
,[page]
,[object_id]
,idx
,rec
)
select top (@count)
ind.index_name,
ind.db,
ind.database_id,
ind.shema,
ind.tb,
ind.frag,
ind.frag_num,
ind.[page],
ind.[object_id],
ind.idx ,
ind.rec
from [inf].[vIndexDefrag] as ind
where not exists(
select top(1) 1 from [БД для администрирования].[srv].[ListDefragIndex] as lind
where lind.[db_id]=ind.database_id
and lind.[idx]=ind.idx
and lind.[object_id]=ind.[object_id]
)
--order by ind.[page] desc, ind.[frag] desc
end
end
--если выборка не пустая
if(exists(select top(1) 1 from @tbl))
begin
--запоминаем выбранные индексы
INSERT INTO [БД для администрирования].[srv].[ListDefragIndex]
(
[db]
,[shema]
,[table]
,[IndexName]
,[object_id]
,[idx]
,[db_id]
,[frag]
)
select [db]
,[shema]
,[table]
,[IndexName]
,[object_id]
,[idx]
,[db_id]
,[frag]
from @tbl;
insert into @tbl_copy (
IndexName
,db
,[db_id]
,Shema
,[Table]
,frag
,frag_num
,[page]
,[object_id]
,idx
,rec
)
select IndexName
,db
,[db_id]
,Shema
,[Table]
,frag
,frag_num
,[page]
,[object_id]
,idx
,rec
from @tbl;
--формируем запрос на оптимизацию выбранных индексов (в случае реорганизации-с последующим обновлением статистики по ним)
while(exists(select top(1) 1 from @tbl))
begin
select top(1)
@IndexName=[IndexName],
@Shema=[Shema],
@Table=[Table],
@frag=[frag]
from @tbl;
if(@frag>=30 and @isrebuild=1 and @isRebuildOnline=1)
begin
set @SQL_Str = @SQL_Str+'ALTER INDEX ['+@IndexName+'] on ['+@Shema+'].['+@Table+'] REBUILD WITH(ONLINE);'
end
else
begin
set @SQL_Str = @SQL_Str+'ALTER INDEX ['+@IndexName+'] on ['+@Shema+'].['+@Table+'] REORGANIZE;'
+'UPDATE STATISTICS ['+@Shema+'].['+@Table+'] ['+@IndexName+'];';
end
delete from @tbl
where [IndexName]=@IndexName
and [Shema]=@Shema
and [Table]=@Table;
end
--оптимизируем выбранные индексы
execute sp_executesql @SQL_Str;
--записываем результат оптимизации индексов
insert into [БД для администрирования].srv.Defrag(
[db],
[shema],
[table],
[IndexName],
[frag_num],
[frag],
[page],
ts,
tf,
frag_after,
[object_id],
idx,
rec
)
select
[db],
[shema],
[table],
[IndexName],
[frag_num],
[frag],
[page],
@ts,
getdate(),
(SELECT top(1) avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID([db]), [object_id], [idx], NULL ,
N'LIMITED')
where index_level = 0) as frag_after,
[object_id],
[idx],
[rec]
from @tbl_copy;
end
END
GO
Важно отметить, что при перестроении индекса, обновлять статистику по индексу не нужно. Также здесь идет перестроение индекса, только если он фрагментирован не менее, чем на 30% и при этом выпуск MS SQL Server позволяет это сделать в режиме ONLINE, и при этом был задан входной параметр @isrebuild хранимой процедуры как 1.
Здесь параметр count нужен больше для распределения нагрузки в течении всего дня. Если же оптимизация по индексам происходит только в определенное время в течении суток или реже, то в count можно передать NULL (как по умолчанию).
Теперь создадим хранимую процедуру [srv].[AutoDefragIndexDB] в БД для администрирования, для последующего ее вызова:
USE [БД для администрирования]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [srv].[AutoDefragIndexDB]
@DB nvarchar(255)=NULL, --по конкретной БД или по всем
@count int=NULL, --кол-во индексов для рассмотрения в каждой БД
@IsTempdb bit=0 --включать ли БД tempdb
AS
BEGIN
/*
вызов оптимизации индексов для заданной БД
*/
SET NOCOUNT ON;
declare @db_name nvarchar(255);
declare @sql nvarchar(max);
declare @ParmDefinition nvarchar(255)= N'@count int';
if(@DB is null)
begin
select [name]
into #tbls
from sys.databases
where [is_read_only]=0
and [state]=0 --ONLINE
and [user_access]=0--MULTI_USER
and (((@IsTempdb=0 or @IsTempdb is null) and [name]<>N'tempdb') or (@IsTempdb=1));
while(exists(select top(1) 1 from #tbls))
begin
select top(1)
@db_name=[name]
from #tbls;
set @sql=N'USE ['+@db_name+']; '+
N'IF(object_id('+N''''+N'[srv].[AutoDefragIndex]'+N''''+N') is not null) EXEC [srv].[AutoDefragIndex] @count=@count;';
exec sp_executesql @sql, @ParmDefinition, @count=@count;
delete from #tbls
where [name]=@db_name;
end
drop table #tbls;
end
else
begin
set @sql=N'USE ['+@DB+']; '+
N'IF(object_id('+N''''+N'[srv].[AutoDefragIndex]'+N''''+N') is not null) EXEC [srv].[AutoDefragIndex] @count=@count;';
exec sp_executesql @sql, @ParmDefinition, @count=@count;
end
END
GO
Таким образом для автоматизации процесса оптимизации индексов можно сделать следующие шаги:
1) для каждой нужной БД определить представление [inf].[vIndexDefrag] и хранимую процедуру [srv].[AutoDefragIndex]
2) в БД по администрированию определить две таблицы [srv].[ListDefragIndex] и [srv].[Defrag], и хранимую процедуру [srv].[AutoDefragIndexDB]
3) создать задачу в Агенте на периодический вызов хранимой процедуры [srv].[AutoDefragIndexDB] из БД по администрированию
Теперь разберем п.3 неоптимальные статистики.
В большинстве случаев при выполнении п.1-2 или просто п.2 по непосредственно самой оптимизации БД и СУБД в целом и выставлении в свойствах БД обновление статистики (автоматическое обновление статистики, статистика автоматического создания, асинхронное автоматическое обновление статистики, автоматическое создание статистики с добавлением), MS SQL Server сам неплохо справляется с задачей по оптимизации статистики.
Главное-не забывать обновлять статистику после реорганизации индекса, т к в таком случае она не обновляется, а также при выполнении п.1 по очистке процедурного кэша.
Но иногда бывают случаи, когда MS SQL Server не справляется со своей задачей из-за специфики работы всей информационной системы или при невозможности использовать п.1 (очистку процедурного кэша). Тогда можно из п.1 взять просто команду по обновлению статистики по всей БД:
USE [ИМЯ_БД]
GO
exec sp_updatestats;
GO
Однако, если и этого недостаточно или обновление статистики по всей БД занимает очень много времени, то необходимо рассмотреть более гибкий алгоритм по обновлению статистики.
Сразу стоит отметить, что при построении более гибкого алгоритма по обновлению статистики, п.1 по очистке процедурного кэша и обновлению всей статистики по БД неприменим в блоке по непосредственно самой оптимизации БД и СУБД в целом.
Приведем здесь пример реализации п.3 в том случае, когда невозможно выполнить обновление статистики по всей БД и встроенных средств также недостаточно.
Для этого в нужных БД создадим следующую хранимую процедуру [srv].[AutoUpdateStatistics]:
USE [ИМЯ_БД]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [srv].[AutoUpdateStatistics]
--Максимальный размер в МБ для рассматриваемого объекта
@ObjectSizeMB numeric (16,3) = NULL,
--Максимальное кол-во строк в секции
@row_count numeric (16,3) = NULL
AS
BEGIN
/*
тонкое обновление статистики
*/
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
declare @ObjectID int;
declare @SchemaName nvarchar(255);
declare @ObjectName nvarchar(255);
declare @StatsID int;
declare @StatName nvarchar(255);
declare @SQL_Str nvarchar(max);
;with st AS(
select DISTINCT
obj.[object_id]
, obj.[create_date]
, OBJECT_SCHEMA_NAME(obj.[object_id]) as [SchemaName]
, obj.[name] as [ObjectName]
, CAST(
(
--общее число страниц, зарезервированных в секции (по 8 КБ на 1024 поделить=поделить на 128)
SELECT SUM(ps2.[reserved_page_count])/128.
from sys.dm_db_partition_stats as ps2
where ps2.[object_id] = obj.[object_id]
) as numeric (38,2)
) as [ObjectSizeMB] --размер объекта в МБ
, s.[stats_id]
, s.[name] as [StatName]
, sp.[last_updated]
, i.[index_id]
, i.[type_desc]
, i.[name] as [IndexName]
, ps.[row_count]
, s.[has_filter]
, s.[no_recompute]
, sp.[rows]
, sp.[rows_sampled]
--кол-во изменений вычисляется как:
--сумма общего кол-ва изменений в начальном столбце статистики с момента последнего обновления статистики
--и разности приблизительного кол-ва строк в секции и общего числа строк в таблице или индексированном представлении при последнем обновлении статистики
, sp.[modification_counter]+ABS(ps.[row_count]-sp.[rows]) as [ModificationCounter]
--% количества строк, выбранных для статистических вычислений,
--к общему числу строк в таблице или индексированном представлении при последнем обновлении статистики
, NULLIF(CAST( sp.[rows_sampled]*100./sp.[rows] as numeric(18,3)), 100.00) as [ProcSampled]
--% общего кол-ва изменений в начальном столбце статистики с момента последнего обновления статистики
--к приблизительному количество строк в секции
, CAST(sp.[modification_counter]*100./(case when (ps.[row_count]=0) then 1 else ps.[row_count] end) as numeric (18,3)) as [ProcModified]
--Вес объекта:
--[ProcModified]*десятичный логарифм от приблизительного кол-ва строк в секции
, CAST(sp.[modification_counter]*100./(case when (ps.[row_count]=0) then 1 else ps.[row_count] end) as numeric (18,3))
* case when (ps.[row_count]<=10) THEN 1 ELSE LOG10 (ps.[row_count]) END as [Func]
--было ли сканирование:
--общее количество строк, выбранных для статистических вычислений, не равно
--общему числу строк в таблице или индексированном представлении при последнем обновлении статистики
, CASE WHEN sp.[rows_sampled]<>sp.[rows] THEN 0 ELSE 1 END as [IsScanned]
, tbl.[name] as [ColumnType]
, s.[auto_created]
from sys.objects as obj
inner join sys.stats as s on s.[object_id] = obj.[object_id]
left outer join sys.indexes as i on i.[object_id] = obj.[object_id] and (i.[name] = s.[name] or i.[index_id] in (0,1)
and not exists(select top(1) 1 from sys.indexes i2 where i2.[object_id] = obj.[object_id] and i2.[name] = s.[name]))
left outer join sys.dm_db_partition_stats as ps on ps.[object_id] = obj.[object_id] and ps.[index_id] = i.[index_id]
outer apply sys.dm_db_stats_properties (s.[object_id], s.[stats_id]) as sp
left outer join sys.stats_columns as sc on s.[object_id] = sc.[object_id] and s.[stats_id] = sc.[stats_id]
left outer join sys.columns as col on col.[object_id] = s.[object_id] and col.[column_id] = sc.[column_id]
left outer join sys.types as tbl on col.[system_type_id] = tbl.[system_type_id] and col.[user_type_id] = tbl.[user_type_id]
where obj.[type_desc] <> 'SYSTEM_TABLE'
)
SELECT
st.[object_id]
, st.[SchemaName]
, st.[ObjectName]
, st.[stats_id]
, st.[StatName]
INTO #tbl
FROM st
WHERE NOT (st.[row_count] = 0 AND st.[last_updated] IS NULL)--если нет данных и статистика не обновлялась
--если нечего обновлять
AND NOT (st.[row_count] = st.[rows] AND st.[row_count] = st.[rows_sampled] AND st.[ModificationCounter]=0)
--если есть что обновлять (и данные существенно менялись)
AND ((st.[ProcModified]>=10.0) OR (st.[Func]>=10.0) OR (st.[ProcSampled]<=50))
--ограничения, выставленные во входных параметрах
AND (
([ObjectSizeMB]<=@ObjectSizeMB OR @ObjectSizeMB IS NULL)
AND
(st.[row_count]<=@row_count OR @row_count IS NULL)
);
WHILE (exists(select top(1) 1 from #tbl))
BEGIN
select top(1)
@ObjectID =[object_id]
,@SchemaName=[SchemaName]
,@ObjectName=[ObjectName]
,@StatsId =[stats_id]
,@StatName =[StatName]
from #tbl;
SET @SQL_Str = 'IF (EXISTS(SELECT TOP(1) 1 FROM sys.stats as s WHERE s.[object_id] = '+CAST(@ObjectID as nvarchar(32)) +
' AND s.[stats_id] = ' + CAST(@StatsId as nvarchar(32)) +')) UPDATE STATISTICS ' + QUOTENAME(@SchemaName) +'.' +
QUOTENAME(@ObjectName) + ' ('+QUOTENAME(@StatName) + ') WITH FULLSCAN;';
execute sp_executesql @SQL_Str;
delete from #tbl
where [object_id]=@ObjectID
and [stats_id]=@StatsId;
END
drop table #tbl;
END
GO
Здесь можно заметить тот факт, что обновляется статистика лишь по тем объектам, для которых были сделаны существенные изменения. Однако, стоит обращать внимание на значение столбца [IsScanned]. Если оно отлично от 1, то это означает факт того, что общее число строк в таблице или индексированном представлении при последнем обновлении статистики и общее количество строк, выбранных для статистических вычислений, не совпадают. А это значит, что статистика уже устарела. И хотя алгоритм рассматривает лишь существенные изменения в данных, не стоит исключать и потребность в том, что когда-нибудь возможно понадобится обновить всю статистику объекта, в котором было мало изменений и который весит по данным очень много. Потому приведенный выше алгоритм не может быть универсальным для всех БД, но для большинства подойдет как отправная точка для дальнейшего тонкого тюнинга по оптимизации статистики.
Далее, в БД для администрирования создаем хранимую процедуру [srv].[AutoUpdateStatisticsDB], которую в дальнейшем нужно будет периодически запускать согласно регламенту:
USE [БД для администрирования]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [srv].[AutoUpdateStatisticsDB]
@DB nvarchar(255)=NULL, --по конкретной БД или по всем
@ObjectSizeMB numeric (16,3) = NULL,
--Максимальное кол-во строк в секции
@row_count numeric (16,3) = NULL,
@IsTempdb bit=0 --включать ли БД tempdb
AS
BEGIN
/*
вызов тонкой оптимизации статистики для заданной БД
*/
SET NOCOUNT ON;
declare @db_name nvarchar(255);
declare @sql nvarchar(max);
declare @ParmDefinition nvarchar(255)= N'@ObjectSizeMB numeric (16,3), @row_count numeric (16,3)';
if(@DB is null)
begin
select [name]
into #tbls
from sys.databases
where [is_read_only]=0
and [state]=0 --ONLINE
and [user_access]=0--MULTI_USER
and (((@IsTempdb=0 or @IsTempdb is null) and [name]<>N'tempdb') or (@IsTempdb=1));
while(exists(select top(1) 1 from #tbls))
begin
select top(1)
@db_name=[name]
from #tbls;
set @sql=N'USE ['+@db_name+']; '+
N'IF(object_id('+N''''+N'[srv].[AutoUpdateStatistics]'+N''''+N') is not null) EXEC [srv].[AutoUpdateStatistics] @ObjectSizeMB=@ObjectSizeMB, @row_count=@row_count;';
exec sp_executesql @sql, @ParmDefinition, @ObjectSizeMB=@ObjectSizeMB, @row_count=@row_count;
delete from #tbls
where [name]=@db_name;
end
drop table #tbls;
end
else
begin
set @sql=N'USE ['+@DB+']; '+
N'IF(object_id('+N''''+N'[srv].[AutoUpdateStatistics]'+N''''+N') is not null) EXEC [srv].[AutoUpdateStatistics] @ObjectSizeMB=@ObjectSizeMB, @row_count=@row_count;';
exec sp_executesql @sql, @ParmDefinition, @ObjectSizeMB=@ObjectSizeMB, @row_count=@row_count;
end
END
GO
Обычно более гибкий алгоритм обновления статистики нужен в таких БД, где аппаратные мощности не способны за разумное время обновить статистику по всей БД, т е это БД с очень большими размерами данных (значительно больше 1 ТБ).
Таким образом были рассмотрены все 3 пункта первого блока о непосредственной оптимизации самой БД и СУБД в целом.
В дополнении к этому блоку стоит добавить следующие общие рекомендации:
1) необходимо следить за тем, чтобы файлы данных БД были фрагментированы от 5 до 20% (если меньше 5%-увеличить (задав больше изначальный размер в свойствах файла БД), больше 20%-сжать с помощью команды SHRINKFILE)
2) необходимо обслуживать по индексам и статистикам системные БД (особенно msdb)
3) необходимо чистить логи БД msdb, например следующим образом:
declare @dt datetime=DateAdd(day,-28,GetDate());
exec msdb.dbo.sp_delete_backuphistory @dt;
exec msdb.dbo.sp_purge_jobhistory @oldest_date=@dt;
exec msdb.dbo.sp_maintplan_delete_log null, null, @dt;
exec msdb.dbo.sp_cycle_errorlog;
exec msdb.dbo.sp_Cycle_Agent_ErrorLog;
...
Теперь разберем второй блок по оптимизации взаимодействия приложения и MS SQL Server (приложения .NET и MS SQL Server).
Здесь приведем лишь основные рекомендации без примеров реализации, чтобы статья не получилась слишком емкой.
Итак, вот общие рекомендации по оптимизации взаимодействия приложения и MS SQL Server:
1) Старайтесь работать не со строкой, а с набором при отправке команд в СУБД
2) Старайтесь асинхронно отправлять запросы к СУБД и не заставлять пользователя ждать отклика приложения
3) Старайтесь отправлять запросы к СУБД пачками, а не единичными (особенно актуально при изменении данных) — т е реализовывайте механизм отложенного запуска и системы накопления запросов
4) Используйте хэширование для всех компонентов ПО для уменьшения обращений к СУБД
5) Подписывайте в строке подключения к БД компонент приложения в Application Name:
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApp2
{
class Program
{
static void Main(string[] args)
{
OpenSqlConnection();
Console.ReadKey();
}
private static void OpenSqlConnection()
{
string connectionString = GetConnectionString();
using (SqlConnection connection = new SqlConnection())
{
connection.ConnectionString = connectionString;
connection.Open();
Console.WriteLine("State: {0}", connection.State);
Console.WriteLine("ConnectionString: {0}",
connection.ConnectionString);
}
}
static private string GetConnectionString()
{
SqlConnectionStringBuilder conn = new SqlConnectionStringBuilder();
conn.ApplicationName = "MyProgram";
conn.DataSource = "SQLServerName";
conn.InitialCatalog = "DBName";
conn.IntegratedSecurity = true;
return conn.ToString();
}
}
}
6) Правильно задавайте уровень изоляции при работе с БД
7) На серверной стороне ПО при необходимости реализуйте очередь обращений к СУБД, которая будет учитывать не только специфику работы информационной системы, но и также аппаратные возможности сервера СУБД или группы серверов СУБД, если последние работают как AlwaysOn
8) Старайтесь заранее фильтровать как можно больше данных, а не запрашивать сразу все от СУБД и потом применять фильтр (например, применяйте постраничный возврат при необходимости с динамической асинхронной подгрузкой данных)
9) Старайтесь не проводить фильтрацию по большому массиву данных на стороне СУБД (см. предыдущий пункт)
10) Разделяйте логи компонентов и логи БД, а не пишите все в одну таблицу
Теперь разберем третий блок по оптимизации самих запросов.
Здесь вновь сильно вдаваться в подробности не будем, т к это заняло бы целую книгу, а опишем лишь некоторые ключевые моменты.
Итак, общие рекомендации по оптимизации самих запросов:
1) старайтесь заранее как можно сильнее фильтровать данные еще до момента соединения их с другими таблицами
2) старайтесь как можно меньше по объему данных сортировать результирующий набор
3) по возможности избегайте конструкции DISTINCT, LIKE '%...', OUTER JOIN особенно на больших данных
4) если в выборке нужно лишь одно поле от соединяемой таблицы, то не соединяйте такую таблицу, а в самой выборке сделайте подзапрос
5) при фильтрации, агрегации и выборке старайтесь учитывать имеющиеся индексы, чтобы оптимизатор мог ими воспользоваться
6) старайтесь возвращать только те поля, которые действительно нужны, а не все поля из всех соединенных таблиц (не подходите унифицировано к коду в T-SQL, т к это очень плохой подход особенно с большими данными)
7) при обновлении и удалении данных (если фильтр или агрегация построены не по кластерному индексу или помимо кластерного индекса есть и другие условия или агрегация), то не делайте операцию сразу по таблице, а сначала выберите удаляемые/изменяемые данные во временную таблицу (которая будет состоять из столбцов, которые входят в кластерный индекс, а также всех прочих необходимых полей для обновления) и уже после примените непосредственно удаление/обновление
8) не перегружайте условия для соединения таблиц, а лучше вынесите часть условия в фильтр
9) используйте разумно хинты к запросам
Для большего понимания приведем пример п.7.
Например, у нас есть таблица Personal (по полю ID есть кластерный индекс) и из нее нужно удалить всех людей с именем, в котором содержится подстрока ‘на’, и обновить комментарий по имени тех людей, фамилия которых заканчивается на ‘ва’.
Вот как можно реализовать данную задачу:
select [ID]
into #tbl_del
from [Personal]
where [FirstName] like '%на%';
delete from p
from #tbl_del as t
inner join [Personal] as p on t.[ID]=p.[ID];
drop table #tbl_del;
select [ID]
,[FirstName]
into #tbl_upd
from [Personal]
where [LastName] like '%ва';
update p
set p.[Comment]=t.[FirstName]
from #tbl_upd as t
inner join [Personal] as p on t.[ID]=p.[ID];
drop table #tbl_upd;
Теперь когда был рассмотрен пример п.7 на простых запросах, приведем пример с работами так называемых дубликатов. Достаточно распространена задача об удалении дубликатов записей. Ее можно реализовать следующим образом:
--Пусть будет признаком дубликата в таблице Personal совпадение по именам и фамилиям.
--Тогда нужно удалить последние по дате создания записи, которые дублируются, и оставить только одну:
;with DelDups as (
select [ID],
--используем оконную функцию с секционированием по имени и фамилии
row_number() over(partition by [FirstName], [LastName] order by [InsertDate] desc) as [rn]
from [Personal]
)
select [ID]
into #tbl_del
delete from DelDups
where rn>1;
delete from p
from #tbl_del as t
inner join [Personal] as p on t.[ID]=p.[ID];
drop table #tbl_del;
Аналогично рассматривается пример с обновлением дублирующих строк.
Таким образом были рассмотрены все 3 блока по оптимизации как самой БД и СУБД в целом, так и при обращении к ней ПО и самих запросов.
В дополнении также стоит отметить то, что при росте информационной системы, в частности кол-ва одновременных пользователей и объемов самой БД, стоит продумать разделение систем на OLTP и OLAP, где фоновые задачи (такие как интеграция, перемещение данных (ETL и др) и т д) будут выполняться с OLAP-системой (в том числе и отчеты), а задачи реального времени, исходящие от пользователей, будут выполняться с OLTP-системой. Такое разделение позволит еще более тонко настроить каждую из систем и в значительной степени снизит нагрузку на OLTP-систему. Здесь можно придерживаться золотому правилу: кол-во запросов от пользователей в OLTP системе должно в разы превосходить кол-ва всех прочих запросов. Аналогично и по суммарным объемам обрабатываемых данных за достаточно длительный период (неделя, месяц и т д). Хотя на практике не редко можно встретить обратную ситуацию, когда пользователи обращаются в основном к OLAP-системе, а все прочие задачи обращаются (накапливают данные новые с последующей перекачкой в OLAP через ETL) к OLTP-системе.
Замечание. На самом деле, чтобы отличить OLAP-систему от OLTP достаточно проверить один факт: в первой системе кол-во выборок данных будет многократно больше кол-ву изменений данных по частоте операций (т е либо изменения редки, либо происходят в таких малых объемах по сравнению с выбираемыми объемами, что этими изменениями можно пренебречь). В OLTP-системе же ровно все наоборот. OLAP и OLTP системы настраиваются по разному, а гибридное решение будет все больше проявлять свою не оптимальность с ростом объемов данных и кол-ва одновременных обращений к этим данным (особенно остро будут проявляться эти характеристики в базах данных, чьи объемы существенно больше 1 ТБ). Если же БД значительно меньше 1 ТБ, то разделение на OLTP и OLAP может не понадобится, т к такая оптимизация значительный рост в производительности может и не дать (пользователи не ощутят).
Также старайтесь разумно использовать все доступные фичи как от самой СУБД, так и от ОС в целом, а также правильно настроить сами аппаратные ресурсы для общей оптимизации работы.
Также не стоит забывать и про ограничение ресурсов (ЦП, ОЗУ и пропускная способность системы ввода-вывода, а также скорость ее работы).
После всех оптимизаций также важно со временем уточнять о будущем росте потребностей к СУБД (в частности, рост кол-ва одновременных обращений, увеличения объемов обрабатываемых данных и т д) и заранее прогнозировать проблемы и пути их решения (т е что можно еще улучшить, а где необходимо улучшение аппаратных средств или закупка недостающих мощностей на будущее).
Результат
В данной статье была рассмотрена оптимизация БД и СУБД в целом, а также даны общие рекомендации по оптимизации приложений для работы с СУБД и самих запросов к БД.
Данные выше рекомендации позволяют существенно оптимизировать всю СУБД на длительный срок с учетом дальнейшего развития информационной системы и увеличения кол-ва одновременных обращений к СУБД.
Источники:
» Динамические административные представления системы
» Перекрытие индексов
» Реорганизация и перестроение индексов
» SHRINKFILE
» msdb
» Строка подключения
» Уровень изоляции
» Автоматическое обновление статистики
» Автоматическая дефрагментация индексов
» Текущий размер всего кэша планов и кэша планов запросов
Комментарии (76)
zodchiy
01.03.2018 14:43Есть вопрос. Что лучше?
— Фронтэнд делает 500 async запросов выполняющихся на бд 0.5-2с каждый.
— Один запрос с портянкой на 500 параметров, которая будет работать через WHILE (или JOIN) в одой процедуре за 40 секунд?
И да, в любой момент все 100 подключений подключены.
jobgemws Автор
01.03.2018 15:09Если инфраструктура позволяет (в плане одновременных подключений), то лучше 1-ый вариант, тогда пользователь быстрее получит данные. А если и не позволяет, то организовать очередь на серверной стороне. Но если совсем уж грустно по кол-ву одновременных возможных подключений (напр, не более 10-ти) и еще задачи какие есть в реал тайм, то второй вариант. Но лучше от второго уходить
zodchiy
01.03.2018 15:33Спасибо за ответ, примерно так и поступаю, где-то все 500 запросов делаю, а где эти же 500 объединяю в один на клиенте и распаралеливаю на бэкенде, а где-то и то в самом скуле. Просто хотелось бы best practies по нагруженным asp.net/mssql решениям.
Почему нельзя растягивать пул?
Старайтесь отправлять запросы к СУБД пачками, а не единичными
А как?
Подписывайте в строке подключения к БД компонент приложения в Application Name:
Чем лучше? Почему?jobgemws Автор
01.03.2018 16:50Application Name-просто потом легче анализировать от кого запрос пришел. Пул очень индивидуально-сколько он кэшировать может.
Запросы пачками-имеется в виду, не по одной строчке, а сразу одним запросом.
Например, удаляете 34 строки-удаляйте не тупо в цикле вызовом хранимки по 1 строке, а сразу 34. А еще лучше просто пометить на удаление сразу 34 строки одним запросом. А потом или ночью уже физически удалить
nikolayv81
02.03.2018 19:25А чем пометить и ночью удалить лучше?
При обновлении (пометить) и построчном хранении насколько пос все старые строчки(условно, там ведь страницы) будут удалены, новые созданы + логи, при последующем удалении "ночью" будет повторное удаление строчек.
Или в mssql что-то сильно по другому?jobgemws Автор
02.03.2018 19:36Логическое удаление лучше, т к не фрагментирует индексы и не вызывает удаления в связанных таблицах
Лучше физическое удаление делать в минимальные нагрузочные часыnikolayv81
02.03.2018 21:09Так в том то и дело, что физически, при обновлении, старые записи(блоки) будут помечены как удалённые, а обновлённые будут записаны. Т.е. по факту в индексе логический адрес ячейки не сменится а физический будет заменён. Т.е. отложенное удаление в сумме приведёт к большему количеству операций ввода-вывода и потенциально большей фрагментации данных в файлах БД (износу ssd-ников в т.ч.) при этом ещё потребует изменения логики работы приложения (фильтровать придётся ещё и удалённые записи, возможно добавлять индексы в случае наличия большого количества удалений).
Для oltp ещё возникает проблема с unique key т.к. сценарий удалили запись — вставили такую-же удалили ещё раз приводит к нарушению уникальности даже в случае включения в constraint признака удаления.
В общем на мой взгляд выигрыш сомнителен, особенно если БД из Oracle, PG, firebird. Возможно с
MSSQL дела обстоят по другому всёже корни блокировочника могут давать особенности, не знаю.jobgemws Автор
02.03.2018 21:13Логическое удаление используется во многих решениях и уже очень давно: CRM, NAV, 1C и т д
Жаль, что своими доводами я вас не убедил
Tantrido
01.03.2018 16:07-1Довольно часто пользователи, разработчики, администраторы и т д СУБД MS SQL Server встречаются с проблемами производительности БД или СУБД в целом.
Пусть переходят на PostgreSQL и перестают жаловаться! Я у себя отмечал прирост скорости в 20 и более раз.jobgemws Автор
01.03.2018 16:47+1Выбор СУБД несомненно должен зависеть от инфраструктуры и задачах.
Однако, сама СУБД не решит все проблемы и не является унифицированным средством и подходом для решения всего на свете
То, что какая-то СУБД-это наше все-большое и распространенное заблуждение.
Каждая СУБД имеет и свои плюсы и свои минусы
Также важно еще сопоставлять с бюджетом, поддержкой и т д и т п
Бесплатно, тоже не всегда хорошо, т к порой поддержка будет значительно дороже платного аналога и более дешевой поддержки.
А уж тонкостей по настройке, оптимизации и эксплуатации везде хватает.
Нужно смотреть в комплексе, а не в вакуумеTantrido
01.03.2018 16:58-1Маркетинговый бред. Полно фирм уже по постгресу предоставляющие поддержку. Вот например: postgrespro.ru/services/support — причём здесь поддержку оказывают сами разработчики — выше качества нет. На sql.ru дадут такую поддержку, что ни с каким МС не сравнится. Вот ещё: www.postgresql.org/support и ещё профи: www.postgresql.org/support/professional_support
А вообще попробуй сначала посгрес с его возможностями и функциями, которых на порядки больше, чем в мс, а потом поговорим. Ты и смотри в комплексе.
africaunite
01.03.2018 17:07+1Грубо как.
jobgemws Автор
01.03.2018 18:00+1Люди не идеальны и все мы разные
Конкретно здесь оскорблений или непристойной информации не было потому и не минусую
Но резковато-это да
Но порой это выручает, а порой и жизнь сама накажет
OlehR
01.03.2018 17:43+2Как мне надоело ето восхваление постгрес. Где би что не обсуждалось по БД там уже расказивают какой постгрес крутой и сколько даст прироста. Да ето очень крутая БД. Точно лутшая с безплатных версионников. :)
А по факту:
1) В класе версионник он отстал от оракла навсегда.
2) Партиции которие дают реальний прирост и маштабируемость появилось буквально в 10 версии и назвать что ето уже продакшин — как-то язик не поварачивается. Про in-memory в посгри я тоже ничего не слишал. (Оракл 12, MSSQL 2014). Пускай реализация местами странная и ограничений вагон и тележка но оно работает и дает реальний прирост.
Если правильно настроить базу и експлуатировать ее как она «любит» то на одном и том же железе разница может бить в процентах а не в разы.
jobgemws Автор
01.03.2018 17:46+2И кто после этого маркетингом занимается? Да ещё с таким пристрастием и так грубо
Я тоже читаю про постгрес и как СУБД считаю её не плохой
Но повторюсь-все нужно смотреть в комплексе
omegik
01.03.2018 18:10+1Это ж каких там функций больше на порядки, позвольте узнать?
Tantrido
01.03.2018 19:44Любых: математических, стринговые, время, аггрегатные и т.д. и т.п.: www.postgresql.org/docs/current/static/functions.html
Там где в MS SQL приходилось писать простыни хранимок и писать велосипеды для распарсивания чего-то, или потом пришлось даже на CLR C# хранимки перейти, в PostgeSQL есть всё из коробки в SQL.omegik
02.03.2018 11:12Ля-ля-ля, бла-бла-бла — посмотрел — по количеству примерно сопоставимо. Ну хоть-бы примерчик какой-то привели, где там нужно писать простыни и велосипеды.
BalinTomsk
01.03.2018 19:19+1---Я у себя отмечал прирост скорости в 20 и более раз
Может не смогли настроить MSSQL? 20 выглядит фантастикой, давайте репо.
---с его возможностями и функциями
Давайте где там Graph DBMS в Постгрессе?
Go, JavaScript (Node.js), R, Ruby не поддерживаются.
Transact SQL and .NET languages в Server-side scripts тоже не поддерживается.
In-memory capabilities нет.Tantrido
01.03.2018 19:40Может не смогли настроить MSSQL? 20 выглядит фантастикой, давайте репо.
А может вы PostgreSQL не пользовались?! Настраивать я MS SQL умею, мы базу и запросы ускоряли на порядки.BalinTomsk
01.03.2018 19:48То есть репо не будет? Будем верить словам?
А учтивая что R не поддeрживается о каком BI вообще можно говорить?Tantrido
01.03.2018 21:08Не надо верить словам: просто возьми свою задачу и проверь её работу на постгресе — всё! :)
OlehR
01.03.2018 22:07Очень спорное утверждение. Если вы правильно используете БД значит вы используете тригера, процедуры, пакеты, функции, особенности БД в запросах и вюхах а ето от несколько десятков тисяч до нескольких сотен тисяч строк.
Разница в запросах + их оптимизация, не говоря уже о разнице pl/sql t-sql и pg/sql настолько огромная что ето фактически наново писать всю логику. То есть ето в принципе невозможно. А переход с версионника на блокировщик или наоборот ето еще то приключение, вплоть то переписивания логики приложения.
А то о чем вы говорите — ето способ писать приложения только используя SQL, а всю логику вне БД. При етом вы используете только незначительную часть возможности БД.
P.S. Была попотка перенести приложение c оракла на IBM DB2 которий декларировал совместимость pl/sql на 99%. Так от все вкусняшки по быстродействию оракла в pl/sql DB2 там не реализованы. Хотя я уверен есть свои. Оценив неоходимое время — мы отказались.Tantrido
01.03.2018 22:11Я слил данные и сравнил скорость конечных запросов. Многие переходят с оракла и мс — и здесь было полно обзоров и довольны.
VMichael
01.03.2018 22:41Ну тут с полгода назад была статья товарища из Яндекса, как они почту (вроде почту, уже запамятовал, если интересно, поищите) перевели с Оракла на Постгре. Там говорилось, что пришлось поставить в 3 раза больше серверов для получения сопоставимой производительности. Окупилось только за счет экономии лицензионных отчислений.
Т.е. или товарищи из Яндекса не разбираются в Постгре (а я думаю весьма разбираются) или ваши заявления несколько не обоснованы.Tantrido
01.03.2018 22:52С ораклом не сравнивал, не знаю. По 3 раза увеличение серверов — первый раз слышу. Люди довольны, даже с ораклом сравнивая шустрее работает. С МС — две огромные разницы.
VMichael
01.03.2018 23:53Вот статья: habrahabr.ru/post/321756
Цитата оттуда:
У нас сейчас в 3 раза больше железа под PostgreSQL, но это ничто по сравнению со стоимостью Oracle.
Я сам работаю в основном с MS SQL.
Были проекты на Postrgre, но с меньшими на порядки объемами, не сравнимыми. Поэтому, быть может, я не ощутил какой то мега разницы.
Большое преимущество Постгре, его бесплатность.
Но вот перейти на Постгре после MS SQL, означает переписать все заново, для проектов, начиная с какого то размера, это обычно дорого и долго.
BalinTomsk
02.03.2018 01:37Я бы сравнил, но боюсь моих познаний по оптимизации Постгреса не хватит и он проиграет.
Раз уж вы стесняетесь и читаете себя спецом в обоих серверах. Переведи это репо на новейший стандарт постгресса и скажите за сколько процедура отработает.
CREATE DATABASE MSSQLtst ON PRIMARY(NAME = InMemoryData, FILENAME = 'c:\db\MSSQLtst.mdf', size=200MB), FILEGROUP [MSSQLtst_FG] CONTAINS MEMORY_OPTIMIZED_DATA( NAME = [MSSQLtst_dir], FILENAME = 'c:\db\MSSQLtst') LOG ON (name = [MSSQLtst_log], Filename='c:\db\MSSQLtst.ldf', size=100MB) GO USE MSSQLtst; GO CREATE TABLE MSSQLtbl (ID INT NOT NULL, Name VARCHAR(100) NOT NULL CONSTRAINT ID_MSSQLtbl_Mem PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT=1000000)) WITH (MEMORY_OPTIMIZED=ON) GO CREATE PROCEDURE proc_MSSQLtst WITH NATIVE_COMPILATION, SCHEMABINDING,EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE='english') DECLARE @counter AS INT = 1 DECLARE @start DATETIME SELECT @start = GETDATE() WHILE (@counter <= 100000) BEGIN INSERT INTO dbo.MSSQLtbl VALUES(@counter, 'SQLAuthority') SET @counter = @counter + 1 END SELECT DATEDIFF(SECOND, @start, GETDATE() ) [MSSQLtst in sec] END GO declare @start datetime2= getdate() EXEC proc_MSSQLtst select datediff(MILLISECOND, @start, getdate() ) GOTantrido
02.03.2018 04:55МС славится своими костылями, когда приходится писать свои костыли в обход МС-овских. У нас SQL был близок к стандарту более не менее, поэтому на постгрес перевелось без проблем, почти 1 в 1, а там такие костыли, типа таблицы в памяти, не нужны как я уже неоднократно говорил. Это как раз от жуткой тормознутости МС появилось, чтобы хоть как-то быстрее работало. Мы использовали иногда в хранимках. А в постгресе для аналогичных задач есть массивы, например.
Попробуй один проект на постгресе начать — будешь недоумевать как на МС мог работать. Всё грамотно сделано.jobgemws Автор
02.03.2018 07:14На любой СУБД нужно проводить регламентные работы и оптимизацию периодически, также нужна оптимизация и в ПО, которое взаимодействует с этим СУБД
В статье дана общая концепция и примеры на скуле и .NET
Перейдя на постгрес все равно придётся делать теже концепции, но другим кодом
И если ПО настроено неправильно или написано мягко сказать вычурно, то СУБД не во всех аспектах спасет
BalinTomsk
02.03.2018 16:13Я так и понял, что все эти сказки пpо Постгресс таким и оказались.
На MSSQL мой пример отрабатывает за 83 ms, на Постгресс это будет работать в лучшем случае секунд 10-15.
Это я еще не рассматривают код где MSSQL может распаралелить запрос, а Постгресс нет.VulvarisMagistralis
02.03.2018 16:46А ниче, что MS — блокировщик, а Postgres — версионник?
И подходы для них в корне различны.
Лобовое сравнение одинакового кода для них — пригодно только для простейших тестовых ситуаций.
Для сложных — нужно отдельно оптимизировать под конкретный сервер.BalinTomsk
02.03.2018 17:07То что вы написали было правдой лет десять назад, MSSQL уже не только блокировочник, но и версионник.
Более того я даже указал в моем примере уровень изоляции.
---пригодно только для простейших тестовых ситуаций.
Я привел концептуальную разницу, где постгресс серьезно проигрывает даже на простейших текстах. И мне этот пример в BI задачах принципиально нужен.
Второй пример приведенный ниже тоже показывает серьезное концептуальное отставание в производительности. На дворе 21 век, а до сих Постгресс не может распаралеливать запросы.
jobgemws Автор
02.03.2018 17:17Прошу привести код в обоих СУБД и параметры компьютера, которые использовались в тесте
BalinTomsk
02.03.2018 17:44обычный дескторный компютер 5-летней давности.
2 экземпляра кода для MSSQL вам уже приведены. Вы эксперт в Постгрессе — приведите аналогичный, показывающий что я неправ.
Моя оценка основана понимании того что будет делать не-MSSQL сервер. Мой код простой — вставка ста тысяч значений в таблицу внутри процедуры.jobgemws Автор
02.03.2018 18:12Прошу прощения, я не экспетр в постгресе
Код для скуля есть, скинте код для постгресаBalinTomsk
02.03.2018 19:49Я тоже не эксперт, поэтому попросил продемонстрировать преимущества постгресса на элементарных примерах.
Tantrido
02.03.2018 20:33Повторяю ещё раз: на постгресе такой запрос не нужен.
Это я еще не рассматривают код где MSSQL может распаралелить запрос, а Постгресс нет.
Лишний раз убеждаюсь, что Вы постгреса не знаете от слова совсем. Я работал на обеих базах: 5+ лет на МС и 7+ на постгресе, на двух проектах: BI и ETL — в обоих проектах при переходе на постгрес скорость вырастала на порядок.
Могу допустить, что за последнее время особенно портировав МС на Линукс, могли произойти позитивные изменения — надо смотреть, но не таки кардинальные, как на постгресе.
silvercaptain
02.03.2018 21:10Лишний раз убеждаюсь, что Вы постгреса не знаете от слова совсем. Я работал на обеих базах: 5+ лет на МС и 7+ на постгресе, на двух проектах: BI и ETL — в обоих проектах при переходе на постгрес скорость вырастала на порядок.
А мужики то оказывается не в курсе…
Позвольте, а где же здесь Постгресс??
www.tpc.org/tpch/results/tpch_advanced_sort.asp?PRINTVER=false&FLTCOL1=ALL&ADDFILTERROW=&filterRowCount=1&SRTCOL1=h_sponsor&SRTDIR1=ASC&ADDSORTROW=&sortRowCount=1&DISPRES=100+PERCENT&include_withdrawn_results=none&include_historic_results=no
Наверное мешки таскать — не языком чесать?Tantrido
02.03.2018 22:18Это что за табличка? МС заказала? Учитывая, что на ТОП-500 МС вообще нет, резонно спросить: где здесь МС?!
silvercaptain
02.03.2018 23:14+1Делают эти тесты производители железа
А табличка, ну это самый известный в мире database benchmarks. Подробней можно почитать здесь:
www.tpc.org/information/about/abouttpc.asp
И что такое TOP-500?Tantrido
03.03.2018 00:31Производители железа, которые давно и прочно, и это не для кого не секрет, завязаны на откаты МС — это не показатель: www.tpc.org/information/about/faq-generic.asp
Any company may join the TPC. A membership costs $15,000 per year.
А постгрес не завязан на конкретную фирму.
TOP-500 — это линуксоиды знают: www.linux.org.ru/news/clusters/13823822
Но это всё таки не так интересно. Интересен свой опыт. А я пробовал на однотипных задачах обе СУБД. Был резкий контраст когда даже на старом железе запросы в постгре летали мсек, а при переходе на МС на порядок мощном железе аналогичный запрос тянул на секунды или даже десятки секунд — это было сильно заметно.
У людей вот тоже в одиночных тестах производительность отличается на порядок: www.enterprisedb.com/blog/postgresql-vs-microsoft-sql — надо всё пробовать самому!
По функциям — вот отличный обзор: www.pg-versus-ms.com
и вот: dba.stackexchange.com/a/77015/15730VMichael
03.03.2018 02:16У людей вот тоже в одиночных тестах производительность отличается на порядок: www.enterprisedb.com/blog/postgresql-vs-microsoft-sql — надо всё пробовать самому!
Сходил по ссылке, почитал.
Там не подтверждается ваше утверждение о мега преимуществе Постгре в производительности.
Цитата оттуда (Яндекс перевод):
Было бы серьезной ошибкой любое категоричное утверждение о том, какая система быстрее и на сколько.
Я в своем опыте работы не заметил лучшей производительности работы Постгре относительно MS SQL.
Еще раз, не холиварю, что лучше, а что хуже.
Оппонирую исключительно высказываниям типа:
«Постгре в 20 раз быстрее MS», «даже на старом железе запросы в постгре летали мсек, а при переходе на МС на порядок мощном железе аналогичный запрос тянул на секунды или даже десятки секунд».
Это все очень субъективные высказывания просто.
Мы же все тут ратуем за «научный подход».
Т.е. сначала определяется методика тестирования, критерии, потом проводятся тесты и производится сравнение результатов.
А то, MS строит индексы сам по внешним ключам, а Постгре нет и наоборот есть какие либо различия в настройках по умолчанию. Про это забыли или не знали.
Вот и получили огромную разницу в производительности, как вариант.Tantrido
03.03.2018 16:35+1Возможно неправильно обобщать свой опыт на всё — описал только то, с чем столкнулся. Проекта того нет, более подробно описать не смогу на данный момент.
По настройкам: МС был оптимизирован максимально: настройки, версионность, изоляция, индексы, запросы и т.п. Постгрес — дефолтный, данные не кешированные и БЕЗ ИНДЕКСОВ, повторюсь.
При переходе на Линукс из-за гораздо более грамотного проектирования системы, чем у МС, на том же железе всегда отмечал увеличение производительности как минимум в 2р: сети, ФС, IO, скорости скомпилированной программы и т.п. Тоже самое отметил и в случае с БД.
Не исключаю, что в последнее время они могли значительно увеличить производительность SQL Server-а, .Net Core и т.п. Какие-то серьёзные изменения у них вроде были.
Если в будущем будет подобный проект — отпишусь.jobgemws Автор
03.03.2018 16:38Вы правы, до 2012 скуля у MS SQL Server были явные проблемы причем не только в производительности. Но с 2012 версии все кардинально изменилось. Особенно в более новых версиях
Tantrido
03.03.2018 16:40+1Тогда будет в двойне интереснее сравнить. Хотя вот эти таблицы в памяти как раньше могут определённые сомнения навеять… :-) посмотрим…
jobgemws Автор
03.03.2018 17:17Берите сразу 2016 для сравнения, если успеете до конца этого года, иначе-тогда уже 2017 версию берите.
Когда планируете опубликовать результаты?Tantrido
03.03.2018 17:34Бог планирует! :) Я же сказал, когда будет соответствующий проект.
jobgemws Автор
03.03.2018 17:35И когда он будет? Без сроков не будет и результата обещанного Вами
Tantrido
03.03.2018 17:43Уважаемый, я ничего не обещал! :) Я описал свой опыт и сказал, что будет интересно сравнить.
jobgemws Автор
03.03.2018 17:47«Да, было бы хорошо. Однако сейчас работа другого характера, доступа к тем данным нет. Будет возможность — с удовольствием: стараюсь теперь на хабре писать интересные вещи по ходу работы, чтобы не терялись и могли кому-то пригодиться»
Да, действительно
Но ладно нам-читателям, для своего же интереса или Вы позволите самообман? В итоге конечно решать Вам. Но либо факты с учетом текущих версий, а не 5-10 и более лет назад, или ничего. Предполагать все могут, а уж генераторов всяких идей на каждом шагу. Это малоценная информация (я бы даже сказал бесполезная или устаревшая).
jobgemws Автор
01.03.2018 20:02Почему для BI не рассмотрели вертику? Одно из лучших решений, но дорогих.
Но BI-решения всегда были дорогими
Постгресс одна из самых лучших бесплатных СУБД-в этом нет сомнений, но если позволяет бюджет, то лучше рассмотреть для BI-вертику или скуль, если OLTP-скуль
Видел решение по финансовой бирже-более 100 ТБ БД на постгресе… скажу вам плачевно все-на поддержку тратятся суммы в разы больше, чем если бы купили вертику и 2-х спецов, а не целый штат
silvercaptain
02.03.2018 07:39+2Realy?
что бы не быть голословным можешь выкачать этот набор данных:
northcentr.blob.core.windows.net/tpcc/order_line.txt
(8,6GB)
Это CSV с разделителем tab
Загрузить в PostgreSQL
Прооптимизированный, куда же без этого :)
и выполнить запрос:
SELECT row_number() over (order by ol_number) rnk, ol_number ,sum(ol_quantity) AS sum_qty ,sum(ol_amount) AS sum_amount ,avg(ol_quantity) AS avg_qty ,avg(ol_amount) AS avg_amount ,count(*) AS count_order FROM order_line GROUP BY ol_number ORDER BY ol_number;
Жду время выполнения этого запроса.
Последняя версия, на которой я это тестировал была
PostgreSQL 9.6.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
И результат был мягко говоря не радостный.
Как по мне — типичная задача BI на не самых больших данныхTantrido
02.03.2018 14:24Извини, у меня работа. Как пел Высоцкий: «Я себе уже всё доказал!» У меня на гораздо более сложном многоэтажном запросе с несколькими JOIN и агрегатами и базе в несколько гиг постгрес был в 20 раз быстрее на базе БЕЗ ИНДЕКСОВ, чем МС с индексами, включая кластерный, с индексированный VIEW WITH SCHEMABINDING, который как ты знаешь в МС работает гораздо быстрее обычного JOIN-а таблиц и т.п.
jobgemws Автор
02.03.2018 14:58Так напишите об этом статью, особенно о сравнении производительности-при каких условиях, что и как тестировали и т д-это будет всем интересно, конструктивно, а не голословно
Tantrido
02.03.2018 15:27+1Да, было бы хорошо. Однако сейчас работа другого характера, доступа к тем данным нет. Будет возможность — с удовольствием: стараюсь теперь на хабре писать интересные вещи по ходу работы, чтобы не терялись и могли кому-то пригодиться.
silvercaptain
02.03.2018 18:05У меня на гораздо более сложном многоэтажном запросе с несколькими JOIN и агрегатами и базе в несколько гиг постгрес был в 20 раз быстрее на базе БЕЗ ИНДЕКСОВ, чем МС с индексами, включая кластерный, с индексированный VIEW WITH SCHEMABINDING, который как ты знаешь в МС работает гораздо быстрее обычного JOIN-а таблиц и т.п.
К сожалению, с Join'ами на больших таблицах все не так радужно… И мегаспецы с sql.ru так же бессильны. Это лично мой пример, и моя попытка получить помощь
www.sql.ru/forum/1199125/has-joinjobgemws Автор
02.03.2018 18:24Я вас удивлю, но к сожалению на SQL.RU я вообще не мог получить ответа на все возникающие проблемы, потому только или иностранные ресурсы, или знакомые или эксперименты
jobgemws Автор
02.03.2018 18:31Но в итоге как решили проблему на постгрессе?
Спасут ли индексы? Если да, то какие подошли оптимальное всего?
Или какое было ещё решение?silvercaptain
02.03.2018 19:01+1Запрос удалось выполнить за «приемлемое» время. Но разница с SQL Server'ом все равно Космос
speshuric
02.03.2018 00:59Вот читаю я эту статью и вижу, что написано человеком знающим и опытным — без иронии, действительно знающим и опытным. Но. Но ваши рекомендации опоздали лет на 8-10 — не для какого-то конкретного читателя опоздали, а в принципе опоздали. И по объёмам данных опоздали, и по конкретным приёмам опоздали.
Мне уже надоело переучивать DBA и разработчиков, начитавшихся "советов в энторнетах", подобных данной статье. Проблема не в советах, конечно же а в том, что их применяют не включая мозг. Эти статьи смешиваются, превращаются в опасные мифы и заблуждения.
Вот просто для иллюстрации, совет "сначала выберите удаляемые/изменяемые данные во временную таблицу". Совет для некоторых конкретных случаев весьма дельный. Но на самом деле часто это очень вредный совет:
- Не сильно заботясь воткнули stop-and-go.
- Потеребили tempdb
- Всё сложили в таблицу без индексов (кстати и в вашем примере), а если таких таблиц 10-20, то что потом в соединении?
- А если и создали, то воспроизводимость планов с индексами и таблицами в tempdb и их диагностика мягко говоря слабее, чем с продуманными постоянными
- И при создании/удалении временных таблиц можно легко нарваться на узкое место в виде перекомпиляции запросов.
Не бывает универсальных (или "вечных") рекомендаций. Рекомендации нужно применять либо как архитектурные принципы ("знаем, что есть исключения, но делаем так пока нет явных причин так не делать"), либо только после диагностики. А вот про диагностику у вас вообще почти ничего нет. Я не про ту диагностику где "индекс давно не используется", а про "что является узким местом данной системы и чем это обусловлено". Ну хоть бы
sys.dm_os_wait_statsупомянули. А в идеале, чтобы велосипеды не изобретать, можно и тулзы Брента Озара упомянуть. Кстати, на его основном сайте очень много интересной, полезной и более актуальной информации о производительности MS SQLspeshuric
02.03.2018 01:08И еще сильно напрягли рекомендации в стиле "делайте всё правильно", например:
- "Правильно задавайте уровень изоляции при работе с БД" — ахаха, 80% корпоративных БД на nolock до сих пор сидят, а из разработчиков, хорошо, если 20% могут осмысленно уровни изоляции обсуждать
- "используйте разумно хинты к запросам" — тоже прекрасно. Степени разумности, которые я видел — от "нельзя ничего" до "указать индекс для каждой таблицы".
jobgemws Автор
02.03.2018 07:18Это и понятно, но все расписывать-целую книгу писать, потому в статье затронул 3 аспекта по оптимизации и в каждом не исчерпывающий список проблем
jobgemws Автор
02.03.2018 07:37Во-первых эти концепции скорее не устареют, а будут дополняться и более детализироваться
А во-вторых, здесь даны примеры. И как и любой пример он имеет свои преимущества и недостатки
И в-третьих, грубо, но для общего понимания вполне сгодится-всегда используется tempdb и если проблема с выделением временной таблицы, то либо вы обрабатываете слишком много данных за раз для вашей системы (тогда делайте порциями обработку), либо проблема не в запросе, а в tempdb-нехватка памяти, медленный ввод-вывод и т д
И итог-напишите свою статью, исходя из того опыта и знаний, что накопил и поделитесь с читателями
Вставка же небольшой порции данных во временную таблицу, позволяет минимизировать блокировки. Для систем, где есть проблема с tempdb разницы не будет, т к либо ожидание будет на блокировку у других запросов в том числе при не использовании временной таблицы, либо задержка будет в вводе-выводе. Но система сама сделает промежуточную таблицу при определённых условиях, а система, где с tempdb все нормально-данный запрос позволит другим не ждать окончания блокировки
И Вы правы-все нужно применять с головой
Raimon
По первой части сам давно пользуюсь скриптами ola.hallengren.com. Они учитывают особенности разных версий SQL Server умеют создавать job'ы, пишут логи и имеют кучу настроек.
jobgemws Автор
Да, хороший инструмент, но по обновлению статистик по большим БД не самое лучшее решение