Предисловие
Довольно часто пользователи, разработчики, администраторы и т д СУБД 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] для последующего использования:
Пример реализации хранимой процедуры [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 по неоптимальным статистикам.
Текущий размер всего кэша планов и кэша планов запросов можно посмотреть, реализовав например следующее представление в БД по администрированию:
Пример реализации представления [inf].[vSizeCache]
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] для конкретной БД:
Пример реализации представления [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] в рассматриваемой БД:
Пример реализации представления [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]:
Пример реализации представления [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]:
Пример реализации представления [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] в тех БД, где необходимо рассмотреть уровень фрагментации индексов:
Пример реализации представления [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
Таблица для сохранения истории про реорганизацию индексов всех БД экземпляра MS SQL Server
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] по оптимизации индексов на каждой нужной БД (также можно и на системных БД) следующим образом:
Пример реализации хранимой процедуры [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=ON);'
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] в БД для администрирования, для последующего ее вызова:
Пример реализации хранимой процедуры [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]:
Пример реализации хранимой процедуры [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], которую в дальнейшем нужно будет периодически запускать согласно регламенту:
Пример реализации хранимой процедуры [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, например следующим образом:
Пример чистки логов БД 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:
Пример использования 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 есть кластерный индекс) и из нее нужно удалить всех людей с именем, в котором содержится подстрока ‘на’, и обновить комментарий по имени тех людей, фамилия которых заканчивается на ‘ва’.
Вот как можно реализовать данную задачу:
Примеры по п.7
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 на простых запросах, приведем пример с работами так называемых дубликатов. Достаточно распространена задача об удалении дубликатов записей. Ее можно реализовать следующим образом:
Пример удаления дубликатов записей согласно п.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
» Строка подключения
» Уровень изоляции
» Автоматическое обновление статистики
» Автоматическая дефрагментация индексов
» Текущий размер всего кэша планов и кэша планов запросов
