SQL Server 2016 RC0

в 16:40, , рубрики: Microsoft SQL Server, sql, sql server, sql server 2016, Администрирование баз данных

SQL Server 2016 RC0 - 1На глаза попалась уже вторая новость на Хабре о том, что скоро Microsoft «подружит» SQL Server и Linux.

Но ни слова не сказано про SQL Server 2016 Release Candidate, который стал доступен для загрузки буквально на днях.

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

Начнем с установки экземпляра SQL Server 2016. Сам инсталлятор претерпел изменения по сравнению с предыдущей версией:

  • Для установки доступна только x64 версия SQL Server-а (последний x86 билд вырезали еще в CTP2.3). Официально все звучит более лаконичнее: «SQL Server 2016 Release Candidate (RC0) is a 64-bit application. 32-bit installation is discontinued, though some elements run as 32-bit components.»
  • Установка SQL Server 2016 на Windows 7 и Windows Server 2008 не поддерживается. Официальный список систем куда можно установить SQL Server: все x64 редакции Windows 8/8.1/10 и Windows Server 2012
  • SSMS теперь не поставляется вместе с SQL Server и развивается отдельно. Скачать standalone редакцию SSMS можно по этой ссылке. Новая редакция SSMS поддерживает работу с SQL Server 2005..2016, поэтому теперь не нужно держать целый парк студий для каждой версии.
  • Добавились два новых компонента, которые реализуют поддержку языка R и PolyBase (мост между SQL Server и Hadoop):

SQL Server 2016 RC0 - 2

Для работы PolyBase требуется предварительно установить JRE7 или более свежую версию:

SQL Server 2016 RC0 - 3

И не забыть потом добавить в исключения Firewall выбранный диапазон портов:

SQL Server 2016 RC0 - 4

Отдельное спасибо Microsoft — теперь не нужно ковыряться в групповой политике, чтобы включить Instant File Initialization:

SQL Server 2016 RC0 - 5

Также немного поменялся диалог по выбору дефолтных путей:

SQL Server 2016 RC0 - 6

Для настройки tempdb сделали отдельную закладку на которой можно автоматически создать нужное число файлов и разнести их при необходимости по разным дискам. Но даже если этого не делать, радует, что при установке по умолчанию параметр Autogrowth будет не 1Mб (как раньше), а 64Mб.

SQL Server 2016 RC0 - 7

При этом максимальный размер файла ограничен 256Мб. Можно задать и больше, но уже после установки:

SQL Server 2016 RC0 - 8

На этом отличия в установке по сравнению с предыдущей версией заканчиваются.

Теперь посмотрим на то что еще поменялось…

Изменились настройки системной базы model, чтобы снизить число AutoGrow событий:

SQL Server 2016 RC0 - 9

Почитать почему это плохо можно тут.

Также важно упомянуть, что некоторый Trace Flag стали на новом SQL Server-е будут включены по умолчанию…

-T1118

SQL Server вычитывает данные с диска кусками по 64Кб (так называемыми экстентами). Экстент – это группа из восьми физически последовательных страниц (по 8Кб каждая) файлов базы данных.

Имеются два типа экстентов: смешанные и однородные. На смешанном экстенте могут храниться страницы с разных объектов. Такое поведение позволяет очень маленьким таблицам занимать минимальное количество места. Но чаще всего таблицы не ограничиваются размером в 64Кб и когда требуется более 8 страниц для хранения данных по одному объекту, то происходит переключение на выделение однородных экстентов.

Чтобы изначально выделять для объекта однородные экстенты был предусмотрен TF 1118, который рекомендовалось включать. И получалось, что работал он глобально для всех баз на сервере.

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

ALTER DATABASE test SET MIXED_PAGE_ALLOCATION OFF

Для системных баз данная опция включена по умолчанию, т.е. все остается, как и было ранее:

SELECT name, is_mixed_page_allocation_on
FROM sys.databases

Исключение сделано лишь для пользовательских баз и tempdb:

name              is_mixed_page_allocation_on
----------------- ---------------------------
master            1
tempdb            0
model             1
msdb              1
DWDiagnostics     0
DWConfiguration   0
DWQueue           0
test              0

Приведу небольшой пример:

IF OBJECT_ID('dbo.tbl') IS NOT NULL
    DROP TABLE dbo.tbl
GO
CREATE TABLE dbo.tbl (ID INT DEFAULT 1)
GO
CHECKPOINT
GO
INSERT dbo.tbl DEFAULT VALUES
GO

SELECT [Current LSN], Operation, Context, AllocUnitName, [Description]
FROM sys.fn_dblog(NULL, NULL)

MIXED_PAGE_ALLOCATION=ON:

SQL Server 2016 RC0 - 10

MIXED_PAGE_ALLOCATION=OFF:

SQL Server 2016 RC0 - 11

-T1117

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

Теперь предположим ситуацию: все файлы, входящие в файловую группу, имеют одинаковый размер. Создается большая временная таблица. Места в файле #1 не достаточно и разумеется происходит AutoGrow. Через время такая же таблица пересоздается, но вставка происходит в файл #2, потому что #1 временно заблокирован. Что в таком случае будет? AutoGrow для #2… и повторная задержка при выполнении запросов. Для таких случаев, был предусмотрен TF 1117. Работал он глобально и при нехватке места в одном файле вызывал AutoGrow для всех файлов в рамках одной файловой группы.

Теперь данный трейс-флаг включен по умолчанию для tempdb и может избирательно настраиваться для пользовательских баз:

ALTER DATABASE test
    MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES
GO
ALTER DATABASE test
    MODIFY FILEGROUP [PRIMARY] AUTOGROW_SINGLE_FILE
GO

Посмотрим на размер файлов:

USE tempdb
GO

SELECT
      name
    , physical_name
    , current_size_mb = ROUND(size * 8. / 1024, 0)
    , auto_grow =
        CASE WHEN is_percent_growth = 1
            THEN CAST(growth AS VARCHAR(10)) + '%'
            ELSE CAST(CAST(ROUND(growth * 8. / 1024, 0) AS INT) AS VARCHAR(10)) + 'MB'
        END
FROM sys.database_files
WHERE [type] = 0

name       physical_name                                       size_mb  auto_grow
---------- --------------------------------------------------- -------- ------------
tempdev    D:DATABASESSQL_2016RC0TEMPtempdb.mdf            8.000000 64MB
temp2      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_2.ndf    8.000000 64MB
temp3      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_3.ndf    8.000000 64MB
temp4      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_4.ndf    8.000000 64MB

Создаем временную таблицу:

IF OBJECT_ID('#t') IS NOT NULL
    DROP TABLE #t
GO

CREATE TABLE #t (
    ID INT DEFAULT 1,
    Value CHAR(8000) DEFAULT 'X'
)
GO

INSERT INTO #t
SELECT TOP(10000) 1, 'X'
FROM [master].dbo.spt_values c1
CROSS APPLY [master].dbo.spt_values c2

Места чтобы вставить данные не хватит и произойдет AutoGrow.

AUTOGROW_SINGLE_FILE:

name       physical_name                                       size_mb     auto_grow
---------- --------------------------------------------------- ----------- ------------
tempdev    D:DATABASESSQL_2016RC0TEMPtempdb.mdf            72.000000   64MB
temp2      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_2.ndf    8.000000    64MB
temp3      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_3.ndf    8.000000    64MB
temp4      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_4.ndf    8.000000    64MB

AUTOGROW_ALL_FILES:

name       physical_name                                       size_mb     auto_grow
---------- --------------------------------------------------- ----------- ------------
tempdev    D:DATABASESSQL_2016RC0TEMPtempdb.mdf            72.000000   64MB
temp2      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_2.ndf    72.000000   64MB
temp3      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_3.ndf    72.000000   64MB
temp4      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_4.ndf    72.000000   64MB

-T2371

До 2016 версии для автоматического пересчета статистики использовалось магическое число «20% + 500 строк». Просто покажу на примере:

USE [master]
GO
SET NOCOUNT ON

IF DB_ID('test') IS NOT NULL BEGIN
    ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [test]
END
GO
CREATE DATABASE [test]
GO
USE [test]
GO

IF OBJECT_ID('dbo.tbl', 'U') IS NOT NULL
    DROP TABLE dbo.tbl
GO
CREATE TABLE dbo.tbl (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Value CHAR(1)
)
GO
CREATE NONCLUSTERED INDEX ix ON dbo.tbl (Value)
GO

INSERT INTO dbo.tbl
SELECT TOP(10000) 'x'
FROM [master].dbo.spt_values c1
CROSS APPLY [master].dbo.spt_values c2

Чтобы обновилась статистика, нужно изменить:

SELECT [>=] = COUNT(1) * .20 + 500
FROM dbo.tbl
HAVING COUNT(1) >= 500

В нашем случае это 2500 строк. При этом не за один раз, а вообще… это значение кумулятивное. Выполняем сперва запрос:

UPDATE dbo.tbl 
SET Value = 'a'
WHERE ID <= 2000

Смотрим:

DBCC SHOW_STATISTICS('dbo.tbl', 'ix') WITH HISTOGRAM

RANGE_HI_KEY RANGE_ROWS    EQ_ROWS       DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
------------ ------------- ------------- -------------------- --------------
x            0             10000         0                    1

Статистика старая… Выполняем еще один запрос:

UPDATE dbo.tbl 
SET Value = 'b'
WHERE ID <= 500

Ура! Статистика обновилась:

RANGE_HI_KEY RANGE_ROWS    EQ_ROWS       DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
------------ ------------- ------------- -------------------- --------------
a            0             1500          0                    1
b            0             500           0                    1
x            0             8000          0                    1

А теперь предположим, что таблица у нас огромная… 10-20-30 миллионов строк. Чтобы пересчиталась статистика нам нужно изменить внушительный объём данных или вручную следить за обновлением статистики.

Начиная с SQL Server 2008R2 SP1 появился TF 2371, который вот тот «магический» процент занижал динамически в зависимости от общего числа строк:

< 25k    = 20%
> 30k    = 18%
> 40k    = 15%
> 100k   = 10%
> 500k   = 5%
> 1000k  = 3.2%

В SQL Server 2016 этот трейс флаг включен по умолчанию.

-T8048

В случае, если в вашей системе более 8 логических процессоров и наблюдается большое число ожиданий CMEMTHREAD и кратковременных блокировок:

SELECT waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type = 'CMEMTHREAD'
    AND waiting_tasks_count > 0

SELECT spins
FROM sys.dm_os_spinlock_stats
WHERE name = 'SOS_SUSPEND_QUEUE'
    AND spins > 0

то использование TF 8048 помогало избавиться от проблем с производительностью. В SQL Server 2016 данный трейс флаг включен по умолчанию.

SCOPED CONFIGURATION

На уровне базы появилась новая группа настроек:

SQL Server 2016 RC0 - 12

Получить их можно из нового системного представления sys.database_scoped_configurations. Лично меня очень радует, что степень параллелизма менять можно не глобально как раньше, а настраивать персонально для каждой базы:

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0

Включать старый Cardinality Estimation (раньше приходилось включать TF 9481 либо понижать compatibility level до 2012):

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON

Отключать Parameter Sniffing (раньше для этого включали TF 4136 или хардкодили OPTIMIZE FOR UNKNOWN)

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF

Также добавили возможность включать TF 4199, который объединяет в себе внушительный список самых разных оптимизаций.

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON

Для любителей вызывать вызывать команду DBCC FREEPROCCACHE предусмотрели команду для очистки процедурного кеша:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

Аналог команды:

DECLARE @id INT = DB_ID()
DBCC FLUSHPROCINDB(@id)

Также думаю будет полезно добавить запрос, по которому можно отслеживать обьем процедурного кеша в разрезе баз:

SELECT db = DB_NAME(t.[dbid]), plan_cache_kb = SUM(size_in_bytes / 1024) 
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
WHERE t.[dbid] < 32767
GROUP BY t.[dbid]
ORDER BY 2 DESC

Теперь рассмотрим новые функции:

JSON_MODIFY

В RC0 добавили возможность модифицировать JSON c помощью функции JSON_MODIFY:

DECLARE @js NVARCHAR(100) = '{
    "id": 1,
    "name": "JC",
    "skills": ["T-SQL"]
}'

SET @js = JSON_MODIFY(@js, '$.name', 'Paul') -- update
SET @js = JSON_MODIFY(@js, '$.surname', 'Denton') -- insert
SET @js = JSON_MODIFY(@js, '$.id', NULL) -- delete
SET @js = JSON_MODIFY(@js, 'append $.skills', 'JSON') -- append

PRINT @js

{
    "name": "Paul",
    "skills": ["T-SQL","JSON"],
    "surname":"Denton"
}

STRING_ESCAPE

Также появилась функция STRING_ESCAPE, которая экранирует спецсимволы в тексте:

SELECT STRING_ESCAPE(N'JS/Denton "Deus Ex"', N'JSON')

------------------------
JS/Denton "Deus Ex"

STRING_SPLIT

Срочно радоваться! Наконец появилась функция STRING_SPLIT, которая избавляет нас от прежних извращений с XML и CTE:

SELECT * FROM STRING_SPLIT(N'1,2,3,,4', N',')

value
---------
1
2
3

4

Но есть и «ложка дегтя», функция работает только с разделителем в один символ:

SELECT * FROM STRING_SPLIT(N'1--2--3--4', N'--')

Msg 214, Level 16, State 11, Line 3
Procedure expects parameter 'separator' of type 'nchar(1)/nvarchar(1)'.

С точки зрения производительности сравним старые методы сплита и новые:

SET STATISTICS TIME ON

DECLARE @x VARCHAR(MAX) = 'x' + REPLICATE(CAST(',x' AS VARCHAR(MAX)), 500000)

;WITH cte AS
(
    SELECT
        s = 1,
        e = COALESCE(NULLIF(CHARINDEX(',', @x, 1), 0), LEN(@x) + 1),
        v = SUBSTRING(@x, 1, COALESCE(NULLIF(CHARINDEX(',', @x, 1), 0), LEN(@x) + 1) - 1)
    UNION ALL
    SELECT
        s = CONVERT(INT, e) + 1,
        e = COALESCE(NULLIF(CHARINDEX(',', @x, e + 1), 0), LEN(@x) + 1),
        v = SUBSTRING(@x, e + 1, COALESCE(NULLIF(CHARINDEX(',',  @x, e + 1), 0), LEN(@x) + 1)- e - 1)
    FROM cte
    WHERE e < LEN(@x) + 1
)
SELECT v
FROM cte
WHERE LEN(v) > 0
OPTION (MAXRECURSION 0)

SELECT t.c.value('(./text())[1]', 'VARCHAR(100)')
FROM 
( 
    SELECT x = CONVERT(XML, '<i>' + REPLACE(@x, ',', '</i><i>') + '</i>').query('.')
) a
CROSS APPLY x.nodes('i') t(c)

SELECT *
FROM STRING_SPLIT(@x, N',')

Результаты выполнения:

(CTE)
 SQL Server Execution Times:
   CPU time = 18719 ms,  elapsed time = 19109 ms.

(XML)
 SQL Server Execution Times:
   CPU time = 4672 ms,  elapsed time = 4958 ms.

(STRING_SPLIT)
 SQL Server Execution Times:
   CPU time = 2828 ms,  elapsed time = 2941 ms.

Live Query Statistics

Что еще понравилось… В новой версии SSMS появилась возможность отслеживать как выполняется запрос в режиме реального времени:

SQL Server 2016 RC0 - 13
Данный функционал поддерживается, не только в SQL Server 2016, но и для SQL Server 2014. На уровне метаданных данный функционал реализован посредством выборки из sys.dm_exec_query_profiles:

SELECT
      p.[sql_handle]
    , s.[text]
    , p.physical_operator_name
    , p.row_count
    , p.estimate_row_count
    , percent_complete = 100 * p.row_count / p.estimate_row_count
FROM sys.dm_exec_query_profiles p
CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle]) s

Фишка эта достаточно крутая. Знаю, что некоторые из компаний уже начали делать аналогичные решения в своих продуктах. Имею ввиду бесплатную версию Plan Explorer и dbForge Studio.

Чтобы сохранить читабельность, за бортом этого обзора я оставил некоторые новые возможности SQL Server (Temporal Tables, Dynamic Data Masking и улучшения в In-Memory), которые планирую добавить после того как выйдет RTM версия.

Собственно на этом все… Всем спасибо за внимание.

Автор: AlanDenton

Источник

Поделиться новостью

* - обязательные к заполнению поля