Архив метки: MS SQL

Кэш временных таблиц в 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

 

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

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

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

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

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

Читать далее

Новые функции Lead & Lag в MS SQL 2012 (Denali)

С новой версией SQL Server 2012 мы встретим много новых возможностей. Про одну из них я расскажу Вам сегодня.

Это функции Lead и Lag.

Что это за функции ?

Это функции доступа к данным, которые граничат с той или иной строкой не используя self-соединений.

Читать далее

MS SQL. Удаляем дупликаты с помощью оконных функций.

Допустим, что перед нами стоит задача удалить дубликаты из таблиц(ы).

Это можно сделать следующим образом.

Допустим у нас есть некая таблица (учитывая мою «симпатию к трактористам»):

CREATE TABLE tbl_List_of_Tractorist (T_ID int identity, T_Name varchar(50))

Наполняем таблицу. В том числе генерим дупликаты.

INSERT INTO tbl_List_of_Tractorist
VALUES('Vasya'), ('Petya'), ('Ivan'), ('Petya'), ('Ivan'), ('Vasya')
, ('Petya'), ('Ivan')

Как видим у нас 3 Ивана, 3 Петра и 2 Василия.

Из этого всего надо оставить каждого имени по-одному.
Делаем следующее:

;WITH CTE
	AS
	(
	    SELECT T_ID, T_Name, ROW_NUMBER() OVER(partition by T_Name Order by T_ID) As RowNum
	    FROM tbl_List_of_Tractorist
	)
DELETE from CTE Where RowNum>1

Но стоит быть весьма осторожным с таблицами на которых навешано n-ое количество индексов или есть тригеры или же количество записей очень большое.
Иногда дешевле будет выполнить Bulk-Insert (SELECT ….. INTO …..) в другую таблицу, которая будет хранить уже отобранные данные, очистить первую и залить данные обратно.

MS SQL. Экспериментируем с ISNULL

Хочу поделится интересным моментом, с которым столкнулся вчера.

Речь пойдет о функции ISNULL в MS SQL.

Напомним ее описание:

функция ISNULL заменяет значение NULL указанным замещающим значением.

Синтаксис следующий:

ISNULL ( check_expression , replacement_value )

Особенностью данной функции есть то, что она вернет значение replacement_value того же типа, что и check_expression.

Читать далее

SSMS директория по умолчанию / SSMS default directory

Добрый день.

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

Сам же конфиг лежит в реестре винды.

В моем случае это Windows 7 и SSMS для 2008 сервера

Вот путь в ветках реестра:

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell

Ищем значение для DefaultFileOpenLocation и меняем его на путь к Вашей папке проектов.

MS SQL. Реализация календаря через рекурсию.

Очень часто нам приходится делать некие временные отчеты в разрезах года, кварталов, месяцев, недель и т.д.

И очень часто нам надо формировать все дни того или иного периода, дабы потом это все соединить с другими данными.

Я приведу пару способов как мы можем сформировать календарь.

Читать далее