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

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

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

Динамические административные объекты 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, которая принимает этот дескриптор в качестве параметра.

Динамические административные объекты 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

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

Динамические административные объекты 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;

Динамические административные объекты Вы можете находить отсутствующие индексы с помощью: 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 можно найти индексы, которые не используются.

Динамические административные объекты 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;

Динамические административные объекты 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;

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

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

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

Аргументы поиска (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)

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

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

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

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

Денормализация БД 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

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