Dynamic management objects. Аргументы поиска (SARG). Денормализация БД. Лекция 6

1. Dynamic Management Objects (динамические административные объекты) Аргументы поиска (SARG) Денормализация БД

Dynamic Management Objects
(динамические административные объекты)
Аргументы поиска (SARG)
Денормализация БД
Лекция 06
Старший преподаватель 
Койнов Игорь Михайлович

2. Динамические административные объекты

Динамические административные 
объекты
SQL Server постоянно контролирует себя и собирает
информацию, полезную для мониторинга состояния
экземпляра, находит проблемы, такие как
отсутствующие индексы.
SQL Server предоставляет эту информацию
посредством динамических административных
объектов (dynamic management objects, DMO).
К этим объектам относятся динамические
административные представления и функции
динамического управления.
Все динамические административные объекты
принадлежат системной схеме sys; имена
динамических административных объектов
начинаются со строки dm_.
2
Койнов И.М.

3. Динамические административные объекты

Динамические административные 
объекты
sys.dm_os_sys_info – можно получить основную информацию
об экземпляре сервера.
sys.dm_os_waiting_tasks – предоставляет информацию о
сеансах, которые в данный момент ожидают чего-либо
sys.dm_exec_sessions – для получения информации о
пользователе, хосте и приложении, находящихся в
ожидании (is_user_process чтобы отфильтровать системные
сеансы).
sys.dm_exec_requests – возвращает информацию о
выполняющихся в данный момент запросах. Оно включает
столбец sql_handle, являющийся хэш-картой текста пакета
T-SQL, который выполняется. Для извлечения полного
текста пакета с помощью связанной с выполнением
динамической административной функции
sys.dm_exec_sql_text, которая принимает этот дескриптор в
качестве параметра.
3
Койнов И.М.

4. Динамические административные объекты

Динамические административные 
объекты
SELECT S.login_name, S.host_name,
S.program_name,
R.command, T.text,
R.wait_type, R.wait_time,
R.blocking_session_id
FROM sys.dm_exec_requests AS R
INNER JOIN sys.dm_exec_sessions AS S
ON R.session_id = S.session_id
OUTER APPLY
sys.dm_exec_sql_text(R.sql_handle) AS T
4
Койнов И.М.

5. Динамические административные объекты

Динамические административные 
объекты
sys.dm_exec_query_stats - можно извлечь
информацию о выполняемых запросах, вводавывода на запрос, использование ЦП на запрос,
истекшее время на запрос и т. д.
sys.dm_exec_sql_text – можно также извлечь текст
запроса. Вы можете извлечь текст определенного
запроса из текста пакета с помощью столбцов
statement_start_offset и statement_end_offset
динамического административного представления
sys.dm_exec_query_stats.
5
Койнов И.М.

6. Динамические административные объекты

Динамические административные 
объекты
SELECT TOP (5)
(total_logical_reads + total_logical_writes) AS total_logical_IO,
execution_count,
(total_logical_reads/execution_count) AS avg_logical_reads,
(total_logical_writes/execution_count) AS avg_logical_writes,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
statement_start_offset, statement_end_offset, qqq.*
FROM sys.dm_exec_query_stats
OUTER APPLY sys.dm_exec_sql_text(sql_handle) qqq
ORDER BY (total_logical_reads + total_logical_writes) DESC;
6
Койнов И.М.

7. Динамические административные объекты

Динамические административные 
объекты
Вы можете находить отсутствующие индексы с помощью:
sys.dm_db_missing_index_details
sys.dm_db_missing_index_columns
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats
Обратите внимание, не следует иметь слишком много
индексов; хотя запросы их используют, SQL Server должен
их поддерживать.
С помощью представления каталога sys.indexes и
динамического административного представления
sys.dm_db_index_usage_stats можно найти индексы,
которые не используются.
7
Койнов И.М.

8. Динамические административные объекты

Динамические административные 
объекты
SELECT
OBJECT_NAME(I.object_id) AS objectname,
I.name AS indexname, I.index_id AS indexid
FROM sys.indexes AS I
INNER JOIN sys.objects AS O ON O.object_id = I.object_id
WHERE
I.object_id > 100
AND I.type_desc = 'NONCLUSTERED' AND I.index_id NOT IN
(SELECT S.index_id
FROM sys.dm_db_index_usage_stats AS S
WHERE S.object_id=I.object_id
AND I.index_id=S.index_id
AND database_id = DB_ID(‘название БД'))
ORDER BY objectname, indexname;
8
Койнов И.М.

9. Динамические административные объекты

Динамические административные 
объекты
SELECT
MID.statement AS [Database.Schema.Table],
MIC.column_id AS ColumnId,
MIG.index_group_handle,
MIC.column_name AS ColumnName,
MIC.column_usage AS ColumnUsage,
MID.equality_columns,
MID.inequality_columns,
MID.included_columns,
MIGS.user_seeks AS UserSeeks,
MIGS.user_scans AS UserScans,
MIGS.last_user_seek AS LastUserSeek,
MIGS.avg_total_user_cost AS AvgQueryCostReduction,
MIGS.avg_user_impact AS AvgPctBenefit
FROM sys.dm_db_missing_index_details AS MID
CROSS APPLY sys.dm_db_missing_index_columns (MID.index_handle) AS MIC
INNER JOIN sys.dm_db_missing_index_groups AS MIG ON MIG.index_handle = MID.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS MIGS ON MIG.index_group_handle=MIGS.group_handle
where
MID.statement like N'%название БД%'
ORDER BY MID.statement,MIGS.avg_user_impact DESC;
9
Койнов И.М.

10. Динамические административные объекты

Динамические административные 
объекты
http://msdn.microsoft.com/ru-ru/library/ms191158.aspx
http://msdn.microsoft.com/ruru/library/bb630282(SQL.110).aspx
https://msdn.microsoft.com/ru-ru/library/ms188754.aspx
10
Койнов И.М.

11. Аргументы поиска (search arguments, SARG)

Аргументы поиска (search arguments, 
SARG)
Чтобы написать подходящий аргумент поиска SARG,
вы должны быть уверены, что столбец, имеющий
индекс, появляется в предикате отдельно, а не как
параметр функции.
Имя столбца должно стоять отдельно на одной
стороне выражения, а константа или вычисляемое
значение — появляться на другой стороне. В качестве
операторов могут использоваться операторы =, >, <,
=>, <=, BETWEEN и LIKE.
Но оператор LIKE можно использовать, только если
подстановочные символы % или _ не стоят в начале
строковой переменной, с которой сравнивается
столбец.
11
Койнов И.М.

12. Аргументы поиска (search arguments, SARG)

Аргументы поиска (search arguments, 
SARG)
Оптимизатор запросов конвертирует
оператор IN в оператор OR с отдельным
сравнением с каждым элементом списка
оператора IN.
Наличие в предикате нескольких условий,
соединенных с помощью оператора OR,
снижает возможность для SQL Server
использовать индексы. Следует
рассмотреть возможность переписать
такой предикат на его логический
эквивалент, использующий оператор AND.
12
Койнов И.М.

13. Аргументы поиска (search arguments, SARG)

Аргументы поиска (search arguments, 
SARG)
Select
*
from
BigTable as bt
where
isnull(bt.[count],1) > 7000
Select
*
from
BigTable as bt
where
(bt.Count is null or bt.Count>7000)
13
Койнов И.М.

14. Аргументы поиска (search arguments, SARG)

Аргументы поиска (search arguments, 
SARG)
Select
*
from
BigTable as bt
where
bt.fam like '%FDS%'
Select
*
from
BigTable as bt
where
bt.fam like 'FDS%'
14
Койнов И.М.

15. Денормализация БД

Денормализация БД
Денормализация — намеренное
приведение структуры базы
данных в состояние, не
соответствующее критериям
нормализации, обычно
проводимое с целью ускорения
операций чтения из базы за
счёт добавления избыточных
данных.
15
Койнов И.М.

16. Денормализация БД

Денормализация БД
Когда:
когда нет больше возможности увеличить скорость
выполнения запросов (МНОГО JOIN’ов)
тратиться много времени на расчётные данные
(возраст, сложные формулы и т.д.)
Плюсы:
Скорость выполнения запросов
Минимизация соединения (количество JOIN’ов => 0)
Радость клиентов за быструю работу БД
Минусы:
Избыточность данных
Занимаемый объем данных на HDD + объем индексов!
Риски при модификации данных (контроль за
связанными данными)
16
Койнов И.М.

17. Денормализация БД

Денормализация БД
Во сколько обойдется?
1. определить требования (чего хотим
достичь)
2. определить требования к данным
(что нужно соблюдать)
3. подсчитать затраты на реализацию
(физические + человеческие +
сопровождение и т.д.)
4. принять решение: отказаться или
реализовать. 
17
Койнов И.М.

18. Денормализация БД

Денормализация БД
select
FLOOR (((cast(bt.Summa as
numeric(18,4))/cast(bt.[Count] as
numeric(18,4))) / 0.01) + 0.5 +
0.01)*0.01 as TARTIF_S,
*
from
[dbo].[BigTable] bt
18
Койнов И.М.

19. Денормализация БД

Денормализация БД
19
Койнов И.М.