Главная страница  Карта сайта  Печать  Написать письмо  RSS
Войти
Кнопка открытия главного меню
Персональный сайт
Стеллецкого Владимира
Обновлено: 27.02.2024 г.

Примеры с SQL

«   ‹   1   2   3   4   следующая ›   последняя »

Права в MS SQL сервере (27.02.2024) #

Некоторое время назад, на работе возник вопрос по правам на объекты в MS SQL сервере. Общее понимание у меня было, но решил уточнить детали и довольно быстро нашёл подробное и развёрнутое описание - в MSSQL - есть "цепочки владения":

Когда в SQL Server один объект ссылается на другой объект, и оба объекта имеют одного и того же владельца, то сервер будет следить только за безопасностью первого объекта.
Например, если хранимая процедура ссылается на таблицу, SQL Server будет проверять безопасность только хранимой процедуры, а не таблицы, если оба объекта имеют одного владельца.

Уточню, что под владельцем понимается схема (scheme), так что для объектов, для которых она одинаковая дополнительные разрешения не требуются. Когда же объекты принадлежат разным владельцам, нужно обязательно выдавать права для всех объектов из схемы отличной от схемы первоначально вызываемого объекта.

Ранее обычно сталкивался с первой ситуацией (когда всё было в пределах одной схемы/владельца) - в этом случае права выдавались только на вызываемую хранимую процедуру (или функцию), из которой уже производилось обращение к нужным представлениям (view) или таблицам (или другим хранимым процедурам и/или функциям), а к вызываемым объектам доступ не предоставлялся.

Использование (N)CHAR вместо (N)VARCHAR (05.09.2023) #

На работе, в одной из таблиц в базе данных MS SQL, заметил, что поле используемое для хранения комментария имеет не совсем подходящий тип - NCHAR(250), вместо NVARCHAR(250). Напомню, что данные в полях типа CHAR и NCHAR всегда одной (фиксированной) длины - при сохранении переданный текст дополняется справа (в конце) пробелами до указанной длины поля.

Бывают ситуации, когда это удобно, но в данном случае я посчитал, что это было сделано по ошибке. В итоге, после уточнения информации (не было ли в данном случае какого-то хитрого расчёта) изменил тип поля на NVARCHAR соответствующей длины и сделал усечение лишних пробелов с правой стороны для уже введённых данных. Пример запроса:

-- Замена NCHAR -> NVARCHAR
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS AS c
            WHERE c.TABLE_SCHEMA = 'dbo' AND c.TABLE_NAME = 'TableName' AND c.COLUMN_NAME = 'Comment' AND c.DATA_TYPE = 'nchar')
BEGIN
    BEGIN
        ALTER TABLE [dbo].[TableName]
        ALTER COLUMN Comment NVARCHAR(250)
    END

    UPDATE [dbo].[TableName] SET
        Comment = RTRIM(Comment)
    WHERE Comment IS NOT NULL
END
GO

Когда же наткнулся на такую же ситуацию в другой таблице решил подойти к вопросу более комплексно и по возможности устранить все такие дефекты. Для этого получил список всех таблиц, в которых есть поля указанного типа:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE
    -- при необходимости можно ограничить минимальную длину таких полей,
    -- если есть места, где их использование уместно (обычно какие-то коды или артикулы)
    --c.CHARACTER_MAXIMUM_LENGTH > 10 AND
    (c.DATA_TYPE = 'char' OR c.DATA_TYPE = 'nchar')

Конечно же, можно было попробовать объединить эти два запроса так, чтобы в итоге автоматически формировался "корректировочный" запрос для каждого такого поля в каждой таблице (с учётом остальных параметров), но так как у нас их было не много, сделал всё вручную. Хотя, по правде сказать, не без "косячка" - в одной из таблиц исходное поле было с признаком NOT NULL и сначала при изменении типа поля я этот признак пропустил, но вовремя заметил ошибку и скорректировал запрос. Так что будьте внимательны.

Таким образом удалось немного сократить объём базы данных, так как перестали храниться ненужные пробелы в конце каждого комментария. И хотя в данном случае выигрыш вряд ли был значителен или хотя бы заметен, но в другой ситуации - при большем размере поля или довольно большом количестве строк/записей - это может быть не так. Тут сразу вспомнились таблицы в базе данных одного из прежних моих работодателей, где только использование оптимальных типов для хранения данных позволило сократить размер таблицы более чем в два раза (полностью сохранив исходные данные).

EXISTS() (22.05.2023) #

Часто для проверки существования в какого-либо объекта или данных используют ключевое слово EXISTS().

Обращаю внимание, что оптимизатор MS SQL при использовании этого ключевого слова вообще не учитывает (отбрасывает) всё, что касается информации о полях в используемом подзапросе (всё между SELECT и FROM), поэтому с точки зрения производительности следующие конструкции равнозначны:

EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA = 'dbo')
EXISTS(SELECT t.TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA = 'dbo')
EXISTS(SELECT TOP 1 t.TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA = 'dbo')
EXISTS(SELECT TOP 1 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA = 'dbo')

MS SQL прекратит выполнение запроса и вернёт результат как только будет получена первая строка с данными подзапроса, так что можно использовать любой вариант записи.

И снова про индексы (26.07.2021) #

В очередной раз хочу напомнить про простые и в тоже время важные вещи:

  • необходимость правильного проектирования структуры таблиц и индексов (с учётом планируемого использования и, что немаловажно, ожидаемого роста количества данных);
  • необходимость периодической проверки актуальности и использования уже созданных индексов и, главное, регулярный анализ списка "тяжёлых"/ресурсоёмких запросов (хотя бы через стандартные средства MS SQL).

Некоторое время назад, при "дежурном" осмотре параметров работы MS SQL-сервера, моё внимание привлёк один из таких "тяжёлых" запросов. Анализ ситуации показал, что из таблицы с более чем 25 млн. записей/строк регулярно выбирались данные отфильтрованные по значению одного поля. И хотя в результирующей выборке было всего около 3-5 записей, запрос выполнялся довольно долго, а также попал в список "тяжелых", так как индекса по этому полю не было и каждый раз происходило сканирование всей таблицы.

В итоге, после добавления индекса по этому полю, данный запрос стал выполняться практически мгновенно, исчез из списка тяжёлых запросов, и, главное, перестал впустую нагружать сервер.

К сожалению, за всё в этом мире надо платить. В этот раз пришлось пожертвовать дисковым пространством - индекс занял примерно треть исходного объема таблицы, а также появились накладные расходы на поддержание этого индекса при вставке и обновлении записей. В этот раз я посчитал эти расходы допустимыми, но каждый раз надо внимательно анализировать текущую ситуацию.

PS И ещё один случай - таблица с почти 3,5 млн. строк. Запросом выбирается одна (TOP 1) строка по значениям трёх полей (одно из которых "дата"). Ранее были созданы отдельные индексы практически на все поля этой таблицы (здесь можно и даже нужно вспомнить про понятие селективности индекса и, соответственно, его эффективности для, например, битовых полей). В результате вместо использования существующих индексов каждый раз идёт практически полный перебор. Как решение в данном случае создал один индекс из этих трёх полей, но без включения доп. полей (include), чтобы излишне не раздувать его размер.

Heap-таблицы и forwarded-записи в SQL Server (20.05.2021) #

Интересная статья про кучи. Основные тезисы:

  • Куча — это таблица без кластерного индекса. Данные в куче хранятся без какого-либо логического порядка. Между страницами нет никакой связи.
  • Таблицы-кучи в базе данных можно найти с помощью системного представления sys.indexes (см. мою заметку)
  • Для просмотра дополнительной информации (процент фрагментации индекса, тип индекса, количество страниц и количество forwarded-записей) можно использовать динамическую административную функцию (Dynamic Management Function, DMF) sys.dm_db_index_physical_stats (в DETAILED-режиме)
  • При изменении данных, если SQL Server не может разместить новые данные на существующей странице из-за их большого размера, данные размещаются в другом месте, а на данной странице SQL Server вставляет Forwarded Record (запись о перенаправлении). Также в новом местоположении данных добавляется указатель в обратную сторону на созданную Forwarded Record для отслеживания цепочки forwarding-указателей в случае перемещения данных.
  • Большое количество перемещений влияет на количество логических чтений при выборке данных (их можно посмотреть, если перед выполнением запроса включить сбор и отображение необходимой статистики - SET STATISTICS IO ON).

Варианты решения проблемы большого количество логических чтений из-за forwarded-записей (если она есть):

  • Использование типов данных фиксированной длины (при изменении данные поместятся на уже выделенное место и перенаправления не будет)
  • Отказываться от кучи (добавлением кластерного индекса)
  • Мониторинг и регулярное перестроение кучи (ALTER TABLE ... REBUILD)

Тёмная сторона SQL Server In-Memory OLTP (12.05.2021) #

Выдержка из статьи: принимая решение об использовании In-Memory OLTP в промышленном окружении нужно обязательно учитывать, что:

  1. Файловую группу In-Memory нельзя удалить из БД, она останется с вами навсегда.
  2. Файловая группа In-Memory, при совпадении ряда условий, может быстро и значительно расти.
  3. Таблицы, объявленные как SCHEMA_ONLY, не генерируют IO и не используют Data и Delta файлы на диске, но эти файлы для них создаются при каждом CHECKPOINT'е.
  4. При использовании Availability Group, файловая группа In-Memory будет гораздо больше, чем в точно такой же БД, не входящей в Availability Group.

Сводные таблицы в SQL (03.07.2020) #

Интересная статья с описанием нескольких способов формирования Сводных таблиц в SQL (есть примеры для MySQL, MSSQL Server, Oracle и PostgreSQL). Пополнил знания по нескольким операторам SQL:

  • Выражение COALESCE - замена часто используюмому мною IsNull. У IsNull, как оказалось, есть одна неприятная особенность - тип результата определяется типом первого параметра, что может привести к усечению данных.
    -- Пример "некорректной" работы IsNull
    DECLARE @st VARCHAR(10), @st1 VARCHAR(20)
    SET @st1 = 'Привет, Владимир!'
    SELECT ISNULL(@st, @st1)
    
  • Аргумент ROLLUP у GROUP BY - для создания строки Итого при группировке.

Также взял на вооружение несколько подсмотренных в статье интересных приёмов.

Временные таблицы и табличные переменные (28.12.2018) #

Наткнулся на старенькую статью посвященную сравнению временных таблиц (create table #T (…)) и табличных переменных (declare @T table (…)).

Приведу ключевые различия (в статье приводится подробное сравнение и примеры):

  1. Табличные переменные не учитывают транзакции (откат транзакции не вернёт исходного значения).
  2. Процедуры с временными таблицами не могут быть предварительно скомпилированы (компиляция будет осуществляться при каждом вызове, что может сказаться на времени, особенно для больших процедур).
  3. Табличные переменные существуют только в пределах видимости переменных.

Касательно быстродействия в каждом конкретном случае правильнее проводить эксперименты.

PS: Также напоминаю, что в MS SQL сервере существуют два типа временных таблиц:

  • Локальные (имя начинается с #) - доступны только в пределах текущего соединения, после отключения/разрыва соединения автоматически уничтожаются.
    Небольшие тонкости области видимости локальных временных таблиц:
    • Чтобы хранимая процедура видела локальную временную таблицу её нужно создать до выполнения этой хранимой процедуры
    • Если локальная временная таблица создается в хранимой процедуре, то она будет видна только в текущей хранимой процедуре и во всех вложенных в неё, но не будет видна за пределами этой хранимой процедуры.
  • Глобальные (имя начинается с ##) - доступны из любого соединения/подключения к серверу.
    Так как такие временные таблицы общие для всех их надо использовать с осторожностью.

Классные индексы (25.09.2018) #

Время от времени натыкаюсь в нашей базе данных на MS SQL сервере на разные интересные моменты. Представим себе таблицу на почти 5 млн. строк, например, с заказами. Вся таблица нас не интересует, возьмём только поле Статус заказа.

Внимание вопрос: насколько и в каких случаях будет эффективным индекс по полю со следующим распределением значений:

Распределение данных в таблице

-- пример запроса для получения такого распределения
select
       r.[Status],
       count(*) as cnt
from dbo.[TableOrder] r
group by r.[Status]  

Думаю, что не особо и только в очень частном случае.

Или представим себе таблицу на пару десятков млн. строк с индексом по трём битовым полям (да-да именно битовым, то есть максимум три значения у каждого).

Тут, как говорится, вообще без комментариев.

Запросы в текущее время потребляющие память (14.01.2018) #

Предлагаю запрос, который отображает запросы с MS SQL-сервера, которые потребляют память в текущий момент (при выводе информации применяется фильтр, отсекающий запросы, потребляющие менее 500 МБ). Иногда с его помощью удаётся найти неэффективные запросы, которые следует рассмотреть повнимательнее и, возможно, оптимизировать или переписать.

Кратко приведу информацию о колонках:

  • Данные о пользователе (имя и хост)
  • Время запроса (request_time) и выделения (grant_time) памяти
  • Объем запрошенной (requested_memory_kb) и выделенной (granted_memory_kb) памяти в килобайтах
  • Информация по использованию памяти: сколько потребовалось (required_memory_kb), было использовано (used_memory_kb) и максимальное значение (max_used_memory_kb)
  • Коэффициент "стоимости" запроса (query_cost)
  • Время ожидания (wait_time_ms)
  • Сам выполняющийся запрос (query_text)
  • Информация о выполняемом запросе (event_info)
  • Ссылка на план выполнения запроса (query_plan)
-- запросы в текущее время потребляющие память
SELECT mg.session_id,
       CAST(es.nt_user_name as varchar) as nt_user_name,
       CAST(es.host_name as varchar) as host_name,
       mg.request_time,
       mg.grant_time,
       mg.requested_memory_kb,
       mg.granted_memory_kb,
       mg.required_memory_kb,
       mg.used_memory_kb,
       mg.max_used_memory_kb,
       mg.query_cost,
       mg.resource_semaphore_id,
       mg.wait_time_ms,
       st.[text] as query_text,
       ib.event_info,
       qp.query_plan
FROM sys.dm_exec_query_memory_grants AS mg
     INNER JOIN sys.dm_exec_sessions AS es
                ON es.session_id = mg.session_id
     CROSS APPLY sys.dm_exec_input_buffer(mg.session_id, NULL) AS ib
     CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
     CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
WHERE mg.requested_memory_kb > 500000
ORDER BY mg.requested_memory_kb DESC

С помощью этого запроса обнаружили у себя запросы с аномальным потреблением памяти (10Гб, 20Гб и даже 40Гб).

«   ‹   1   2   3   4   следующая ›   последняя »

  Вы 56 840 посетитель этой странички
с 07 октября 2006 года
© http://svv-home.ru
О сайте