Архив рубрики: Базы данных

Кэш временных таблиц в SQL Server (SQL Server temporary table caching)

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

В данной статье мы попытаемся разобраться как разрешать кэширование временных таблиц и сравним производительность для cached vs. non-cached temporary tables.

Читать далее

MS SQL 2016. Удаление партиции (TRUNCATE TABLE with PARTITION)

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

Синтаксис:

TRUNCATE TABLE [database].[schema].[table] WITH (PARTITIONS [partition number expression] | [range] );

Варианты использования:
Использование порядкового номера партиции: (PARTITIONS (8))
Список партиций: (PARTITIONS (1,2,3,4))
Диапазон партиций: (PARTITIONS (1 TO 4))
Комбинированный  список: (PARTITIONS (1,2 TO 4))

Пример:

TRUNCATE TABLE dbo.YourTable WITH (PARTITIONS (2,6 TO 9));

Ну и один немаловажный момент — если таблица без партиций, то подобное использование синтаксиса вызовет ошибку.

Улучшаем Maintenance Plan в MS SQL 2016

В этой статье я расскажу какие новые функциональные возможности доступны в SQL Server 2016 касательно планов обслуживания базы данных (Maintenance Plan) для переиндексации, реорганизации индексов и DBCC CHECKDB и есть ли особенности в их тонкой настройке для уменьшения downtime периода.

SQL Server 2016 Reindex and Reorganize

SQL Server CTP 2 016 2.4+ улучшил функциональность для переиндексации и реорганизации индексов, используя задачи по обслуживанию базы данных. Предположим, что мы хотим реализовать следующий план переиндексации:

1. Reorganize index: если фрагментация > 15% и количество страниц > 1000
2. Rebuild index: Если фрагментация > 30% and количество страниц > 1000

Если мы откроем план обслуживания базы данных из Management Studio в SQL 2016 CTP 2.4, то увидим, что у нас есть эти опции для тонкой настройки индексов:

Как видим у нас есть возможность задавать следующие параметры для оптимизации индексов:
— если фрагментация более задаваемого значения
— если количество страниц более задаваемого значения
— и еще один немаловажный параметр — или вообще индекс использовался в течении n-го количества дней

 Улечшение для SQL Server 2016 Rebuild Index

В SQL Server 2016, задача перестроения индекса в плане обслуживания базы данных включает в себя дополнительные функции:

MAXDOP: изменение максимальной степени параметра конфигурации параллелизма на время операций с индексами.

Pad_Index: использует опцию PAD_INDEX, которая указывает процент, до которого заполняются не листовые индексные страницы

MAX_DURATION: время ожидания (целочисленное значение, указанное в минутах) в течение которого блокировки для операции перестроения индекса в режиме «в сети» будут ожидать с низким приоритетом при выполнении команды DDL. Если операция будет заблокирована на время MAX_DURATION, будет выполнено одно из действий ABORT_AFTER_WAIT. Время MAX_DURATION всегда указывается в минутах.

В случае превышения значения MAX_DURATION, с помощью параметра ABORT_AFTER_WAIT мы можем указать, что делать далее:

NONE — Продолжить ожидание блокировки с обычным приоритетом.
SELF — Прекратить операцию DDL по перестроению индекса в режиме «в сети», выполняемую в данный момент без предпринятия какого-либо действия.
BLOCKERS — Остановить все пользовательские транзакции, в данный момент блокирующие операцию DDL по перестроению индекса в режиме «в сети», чтобы можно было продолжить данную операцию. Параметр BLOCKERS требует, чтобы учетная запись имела разрешение ALTER ANY CONNECTION.

SQL Server 2016 Database Check Integrity Task

Если посмотреть на задачу, которая обслуживает целостность, то можно увидеть следующее:
Include Indexes: включение индексов в процессе DBCC CHECKDB
Physical only: ограничивает проверку на целостность физической структуры страницы, запись заголовков и согласованность распределения базы данных.  Использование этой опции может уменьшить время выполнения DBCC CHECKDB для больших баз данных, и рекомендуется для частого использования в production системах.
Tablock:  DBCC CHECKDB получает блокировку таблицы вместо использования внутреннего моментального снимка базы данных.  Это включает в себя краткосрочные exclusive (X) блокировки на базе данных. Использование этой опции может помочь DBCC CHECKDB процедуре работать быстрее под нагрузкой, но снижает параллелизм в базе данных, во время ее выполнения.

Весьма ожидаемая фича SQL Server 2016 — DROP IF EXISTS

Вам нравится писать следующие условные операторы DROP?

IF OBJECT_ID('dbo.Product', 'U') IS NOT NULL
 DROP TABLE dbo.Product;

IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trProductInsert')
 DROP TRIGGER trProductInsert

Думаю нет.

Думаю часто новички ищут подобное в Google — SQL Server Drop if Exists.

Начиная с С SQL Server +2016 CTP3 вы можете использовать новые новую фичу вместо большого IF врапера, например:

DROP TABLE IF EXISTS dbo.table1

DROP TRIGGER IF EXISTS trtable1

Если объект даже не существует, выполнение скрипта продолжится.

Список объектов БД, которые можно удалить таким образом:

AGGREGATE PROCEDURE TABLE
ASSEMBLY ROLE TRIGGER
VIEW RULE TYPE
DATABASE SCHEMA USER
DEFAULT SECURITY POLICY VIEW
FUNCTION SEQUENCE
INDEX SYNONYM

Также возможны следующие выполнения команды

  • ALTER TABLE DROP COLUMN IF EXISTS
  • ALTER TABLE DROP CONSTRAINT IF EXISTS

 

PostgreSQL. Динамический LIMIT / Dynamic LIMIT

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

CREATE OR REPLACE FUNCTION <name of function>
(
 IN <Input param #1> <type>,
 IN <Input param #2> <type>,
 IN i_limit integer 
)
RETURNS TABLE 
(
 column1 <type>,
 column2 <type>,
 column3 <type>
)
AS
$$
SELECT 
 <column1>,
 <column2>,
 <column3>
FROM mm.gain_history a
...........
WHERE
...........
LIMIT
   COALESCE($3, NULL);
$$
LANGUAGE 'sql';

PostgreSQL. Скрипт для нахождения разрастающихся объектов базы данных

Недавно столкнулся с ситуацией, когда backup базы стал довольно внушительного размера, хотя на самом деле я прекрасно понимал, что даже самые большие таблицы не «весят» столько.

Как оказалось, выполнив следующий скрипт:

SELECT nspname || '.' || relname as name,
 pg_size_pretty(pg_total_relation_size(C.oid)) as totalsize, -- incl. TOASTed and indx
 pg_size_pretty(pg_relation_size(C.oid)) as relsize -- pure size (w/o TOAST and indexes)
 FROM pg_class C
 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 WHERE nspname NOT IN ('pg_catalog', 'information_schema')
 ORDER BY pg_total_relation_size(C.oid) DESC
 LIMIT 20;

я обнаружил, что есть некие объекты pg_toast_<некий порядковый номер>, имеющие громадный размер. При этом связанные с ними таблицы мой базы имели такой же размер.
Сделав VACUUM (автоматический вакуум у меня отключен на этой базе), все обернулось самым лучшим образом. 

Выполнив еще раз вышеуказанный запрос, pg_toast_ уже в результатах не оказался.

 

SQL Server 2012. Несколько способов динамической сортировки

Добрый день!
Сегодня, изучая новые возможности SQL Server 2012, я поэкспериментировал с несколькими способами динамической сортировки.

Допустим мы имеем таблицу

table1
(
 ID INT,
 Name1 varchar(100),
 Name2 varchar(100),
 Name3 varchar(100)
)

И нам необходимо по какому-то признаку сортировать данные при выборке из таблицы по разным столбцам

Читать далее

Postgresql. Backup to Amazon S3 through WAL-E on CentOS

В этой статье я расскажу про свой опыт разворачивания backup сервиса WAL-E для заливания бэкапов Postgresql баз на сервис Amazon S3.

Первым делом поведаю, что у меня было на входе:

1. ОС — Centos 6.2
2. DB — Postgresql 9.2
3. Желание хранить бэкапы базы данных на Amazon S3

Далее опишу пункты которые мною выполнялись(заведомо до этого я уже имел доступ к сервису Амазона):

Читать далее

Рассылка почты с PostgreSQL (Sending E-Mail from PostgreSQL)

Делая монитор процессов, происходящих в базе данных Postgresql, столкнулся с вопросом посылки некого аларма из базы данных на специфический адрес с уведомлением о происходящем.

Далее расскажу о своих шагах в этом направлении:

1. Учитывая то, что сам Postgresql этого делать не умеет, пришлось прибегнуть к сторонним библиотекам. Все во благо, когда СУБД позволяет интегрировать в себе «1000+1» язык разработки.

2. Первым делом надо проверить установлена ли у Вас библиотека Python для Postgres.

На моем сервере установлена CentOS + Postgresql 9.2

Путь папки с библиотеками — /usr/pgsql-9.2/lib/

В этой папке должно находится, что-то типа plpython2.so или plpython3.so (первой, более старой, вполне достаточно).

У меня этого не было, посему пришлось искать подходящий пакет. В качестве подходящего кандидата был выбран — postgresql92-plpython-9.2.2-1PGDG.rhel6.x86_64

2. Далее, следующим стейтментом создаем язык в Postgres:

CREATE LANGUAGE plpython2u;

3. Далее создаем функцию для отсылки сообщений:

CREATE OR REPLACE FUNCTION public.send_email(_from Text,_password Text,smtp Text,port INT,receiver text, subject text, send_message text) RETURNS TEXT LANGUAGE plpython2u
AS $function$

import smtplib
sender = _from
receivers = receiver
message = ("From: %s\nTo: %s\nSubject: %s\n\n %s"
% (_from,receiver,subject,send_message))

try:

smtpObj = smtplib.SMTP(smtp,port)
smtpObj.starttls()
smtpObj.login(_from, _password)
smtpObj.sendmail(sender, receivers,message)
print ('Successfully sent email')
except SMTPException:
print ('Error: unable to send email')

return message
$function$
;

4.  Пробуем послать первое сообщение (в качестве smtp сервера я использовал gmail):

select send_email
('<Sender address>',
'<Sender password>',
'smtp.gmail.com',
587,
'<email of recipient>',
'<Your subject>',
'<Message body>');

5. Результатом должен быть приход письма с <Sender address>.

Буду рад, если кому-то это объяснение поможет 🙂