Предисловие.
Если человек еще в состоянии обучаться, то это нужно делать систематически, результаты обучения должны быть измеримы, например - подтверждены сертификатом или дипломом. Поэтому в своих публикациях я стараюсь придерживаться тематики экзамена 1С:Эксперт по технологическим вопросам. Поводом для статьи послужил вебинар http://edu.1c.ru/expert/ который произвел на меня превосходное впечатление. Ниже используются материалы вебинара, спасибо Виктору Богачёву.
Рядом с каждым утверждением содержится ссылка на документацию - буквы MS, PG.
Избранные административные представления и функции.
Список баз данных.
[ Наименование, идентификатор…]
[ MS Sys.databases (состояние базы данных, доступность, режим RCSI, модель восстановления (full, simple), настройки создания и обновления статистики, отметка о включении Query Stories, …) ]
[ PG pg_database ]
Список индексов.
[ Реквизиты: идентификатор индекса, идентификатор таблицы, уникальность, доступность к использованию, кластеризованность…]
[ MS sys.indexes ]
[ PG pg_index ]
Список статистик.
[Содержит гистограммы…]
[ MS sys.dm_db_stats_histogram ]
[ PG pg_stats ]
Информация о таблицах
[ MS sys.tables ]
[ PG pg_class (содержит информацию по таблицам, индексам, статистикам…)
Пример определения размера таблицы (отношения) и базы данных по списку и спец. функцией:
SELECT
relname,
relpages,
reltuples
FROM pg_class
ORDER BY relpages DESC
LIMIT 9;
--SELECT pg_database_size(current_database());
--SELECT pg_relation_size('pg_statistic')
]
Избранные динамические административные представления и функции.
[ MS Для просмотра DMV требуются права VIEW SERVER STATE, VIEW DATABASE STATE. Данные статистики накапливаются с момента запуска сервера SQL, но некоторые DMV могут быть очищены.
Тексты запросов MS SQL заимствованы из статей (Ian Stirk) Uncover Hidden Data to Optimize Application Performance и Troubleshooting Performance Problems in SQL Server 2005 ]
[ PG для сброса статистики pg_stat_reset(). При перезапуске сервера статистика сохраняется, если нет восстановления БД после запуска. Параметры postgresql.conf, влияющие на сбор статистики:
-
track_activities мониторинг текущих команд
-
track_counts сбора статистики по обращениям к таблицам и индексам.
-
track_functions отслеживание использования пользовательских функций.
-
track_io_timing мониторинг времени чтения и записи блоков.]
Выполнение запросов (Exec)
[ MS
-
sys.dm_exec_query_plan (параметр вызова plan_handle, поле query_plan cодержит представление Showplan…)
-
sys.dm_exec_query_stats (количество выполнений, статистика (total, last, min, max) по времени ЦП, физическому чтению, логическому чтению/записи, времени выполнения, количеству строк, степени параллелизма, объему памяти…)
-
sys.dm_exec_requests (информация о выполняющихся запросах…)
-
sys.dm_exec_sql_text (параметр вызова sql_ handle или plan_handle, содержит текст запроса…)
Пример:
SELECT
[Query plan] = qp.query_plan,
[Query text] = qt.text,
QueryState.[Total Reads],
QueryState.[Execution count]
FROM (SELECT TOP 10
[Total Reads] = (total_logical_reads),
[Execution count] = (qs.execution_count),
[sql_handle] = qs.sql_handle,
[plan_handle] = qs.plan_handle
FROM sys.dm_exec_query_stats AS qs
ORDER BY [Total Reads] DESC) AS QueryState
CROSS APPLY sys.dm_exec_sql_text(QueryState.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(QueryState.plan_handle) AS qp
]
[ PG
-
pg_stat_statements (статистика по выполнению запросов: число выполнений, длительность, количество строк, процент использования буферного кеша, количество прочитанных/записанных блоков, время чтения/записи… Чтобы начать собирать статистику, нужно добавить модуль pg_stat_statements в файле postgresql.conf, строка pg_stat_statements и выполнить CREATE EXTENSION pg_stat_statements. Для очистки используйте SELECT pg_stat_statements_reset();)
Пример:
SELECT
query,
calls,
total_time,
rows,
100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 5;
]
Индексы (Index)
[ MS
-
sys.dm_db_index_usage_stats (статистика: количество использований…)
-
sys.dm_db_missing_index_group_stats (возможный выигрыш…)
-
sys.dm_db_missing_index_details (родительская таблица, условия полей…)
-
sys.dm_db_missing_index_groups (содержит связь между dm_db_missing_index_group_stats и dm_db_missing_index_details.)
Пример:
SELECT TOP 10
[Total Cost] = ROUND(Stat.avg_total_user_cost * Stat.avg_user_impact * (Stat.user_seeks + Stat.user_scans),0),
Stat.avg_user_impact,
TableName = Detail.statement,
[EqualityUsage] = Detail.equality_columns,
[InequalityUsage] = Detail.inequality_columns,
[Include Cloumns] = Detail.included_columns
FROM sys.dm_db_missing_index_groups Groups
INNER JOIN sys.dm_db_missing_index_group_stats Stat
ON Stat.group_handle = Groups.index_group_handle
INNER JOIN sys.dm_db_missing_index_details Detail
ON Detail.index_handle = Groups.index_handle
ORDER BY [Total Cost] DESC;
Результат работы - список недостающих индексов. Создавать индексы нужно средствами 1С. Соответствие между именами 1С и СУБД - функция "ПолучитьСтруктуруДанных". Информация "Include Cloumns" показывает, что как использовать включенные столбцы индекса в СУБД, но в 1С такой возможности нет - поэтому для полей "Include Cloumns" также создаем индексы.
SELECT CURRENT_TIMESTAMP;
use MyDataBaseName;
SELECT top 20
[IndexName] = IndexTable.name,
[user_updates] = StatIndex.user_updates
FROM(SELECT top 50
[object_id] = SizeIndx.[object_id],
[index_id] = SizeIndx.index_id
FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TableName'), NULL, NULL, 'LIMITED') AS SizeIndx
order by SizeIndx.page_count desc
) AS GroupSize
INNER JOIN sys.indexes AS IndexTable
ON GroupSize.[object_id] = IndexTable.[object_id]
AND GroupSize.index_id = IndexTable.index_id
INNER JOIN sys.dm_db_index_usage_stats as StatIndex
ON GroupSize.[object_id] = StatIndex.[object_id]
AND GroupSize.index_id = StatIndex.index_id
WHERE ( StatIndex.user_seeks + StatIndex.user_scans + StatIndex.user_lookups) = 0
order by StatIndex.user_updates desc
SELECT CURRENT_TIMESTAMP;
Запрос работал медленно (дольше 5 минут), поэтому сделал вторую версию. Используется режим Limited, который быстрее Detailed. Выбираются бОльшие по количеству страниц таблицы, затем находятся неиспользуемые индексы. Имена индексов имеют вид _InfoRg18313_ByProperty18332, что несложно расшифровать в 1С. Замеряется время до и после запроса.
Запрос использует функцию db_id(), поэтому перед его использованием следует установить текущую базу данных, команда use MyWorkDataBase. Выберите подходящую вам базу. Результат работы - список неиспользуемых индексов больших таблиц, которые могут замедлять запись в таблицу.
]
[ PG
-
pg_stat_all_indexes (статистика: количество использований…)
-
pg_statio_all_indexes (statio - данные о чтении страниц с диска и из буфера…)
]
Ввод-вывод (IO)
[ MS
-
sys.dm_io_virtual_file_stats
Пример:
SELECT
[ReadLatency] = CASE WHEN [num_of_reads] > 0
THEN ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatency] = CASE WHEN [num_of_writes] > 0
THEN ([io_stall_write_ms] / [num_of_writes]) END,
[Latency] = CASE WHEN ([num_of_reads] + [num_of_writes] > 0)
THEN ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
[AvgBPerRead] = CASE WHEN [num_of_reads] > 0
THEN ([num_of_bytes_read] / [num_of_reads]) END,
[AvgBPerWrite] = CASE WHEN [num_of_writes] > 0
THEN ([num_of_bytes_written] / [num_of_writes]) END,
[AvgBPerTransfer] = CASE WHEN ([num_of_reads] + [num_of_writes] > 0)
THEN (([num_of_bytes_read] + [num_of_bytes_written]) / ([num_of_reads] + [num_of_writes])) END,
LEFT ([mf].[physical_name], 2) AS [Drive],
DB_NAME ([vfs].[database_id]) AS [DB],
[mf].[physical_name]
FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
--ORDER BY [WriteLatency] DESC;
]
[ PG
-
pg_stat_all_tables
-
pg_statio_all_tables (statio - данные о чтении страниц с диска и из буфера…)
-
pg_stat_database
Пример:
SELECT
datname,
temp_bytes,
deadlocks,
blk_read_time,
blk_write_time,
stats_reset
FROM
pg_stat_database;
Результат работы - список имя базы, объем данных записанный во временные таблицы, количество дедлок, время затраченное на чтение, время затраченное на запись, время начала статистики.
]
Транзакции (Tran)
[ MS
-
sys.dm_tran_locks (Активные блокировки. Описание ресурса, описание блокировки.)
Пример: //infostart.ru/1c/articles/707333/
]
[ PG
-
pg_locks (Активные блокировки. Описание ресурса, описание блокировки.)
Пример (блог разработчика):
SELECT
locktype,
mode,
granted,
pid,
pg_blocking_pids(pid) AS wait_for
FROM pg_locks
Результат запроса - тип блокировки, режим, информация об установке, id сессии, id блокирующей сессии.
]
Системные
[ MS
-
sys.dm_os_wait_stats ( Накопленные данные ожидания, длительность. Очистка – при перезапуске или DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)… )
-
sys.dm_os_sys_info ( Физическая память, доступно в буферном кеше… )
-
sys.dm_os_performance_counters ( Счетчики PerfMon. Имя счетчика, текущее значение… )
-
sys.allocation_units (ИД таблицы, количество страниц...)
Пример:
SELECT TOP 10
[Wait type] = wait_type,
[Wait time (s)] = wait_time_ms / 1000,
[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 / SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;
результат работы: тип ожидания, продолжительность, процент
SELECT TOP 9
t.Name AS TableName,
p.Rows AS RowCounts,
SUM(a.total_pages) * 8 / 1000000 AS TotalSpaceGB,
SUM(a.used_pages) * 8 / 1000000 AS UsedSpaceGB
FROM
sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.Name NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.object_id > 255
GROUP BY t.Name, p.Rows
ORDER BY TotalSpaceGB DESC
результат работы: название таблиц, количество строк, всего страниц, используется страниц.
]
[ PG
-
pg_stat_activity (Текущие данные, без накопления. База данных, пользователь, тип ожидания, текст запроса…)
-
pg_stat_progress_vacuum (база данных, таблица, фаза очистки, число обработанных кортежей…)
Пример:
SELECT
pid,
wait_event_type,
wait_event
FROM pg_stat_activity
WHERE wait_event is NOT NULL;
список текущих пользователей
SELECT
pid,
datname,
usename,
application_name
FROM pg_stat_activity
чтобы завершить процесс пользователя
SELECT pg_cancel_backend(procpid)
]
Обновление, март 2024
SELECT DatabaseName = DB_NAME(),
TableName = OBJECT_NAME(s.[object_id]),
IndexName = i.name,
user_updates,
system_updates,
s.user_seeks,
s.user_scans,
s.user_lookups,
'drop index ['+i.name+'] ON ['+OBJECT_NAME(s.[object_id])+']' as [Drop]
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id and
s.index_id = i.index_id
WHERE s.database_id = DB_ID() and
OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 and
i.is_disabled = 0 and
i.is_unique = 0 and
i.is_primary_key = 0 and
i.type_desc <> 'HEAP' and
-- i.name like 'missing%'
order by user_seeks desc
SELECT TOP 20
CONVERT (decimal (28, 1),
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
) AS estimated_improvement,
'CREATE INDEX missing_index_' +
CONVERT (varchar, mig.index_group_handle) + '_' +
CONVERT (varchar, mid.index_handle) + ' ON ' +
mid.statement + ' (' + ISNULL (mid.equality_columns, '') +
CASE
WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL (mid.inequality_columns, '') + ')' +
ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON
migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON
mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;
