🤙🏿 🥧 ✊🏼 SQL Server 2016 RC0 🍭 🖐🏾 👙

Habréに関する2番目のニュースは、 Microsoftが 「すぐに友達になる」というSQL ServerとLinuxがすぐに目を引きました。しかし、先日ダウンロードできるようになったSQL Server 2016リリース候補については一言も言われていません。

来月、 RTMのリリースが予定されているので、猫の下でさらに新しいバージョンの一部として利用できるイノベーションの一部を分析します：インストールの違い、デフォルトのトレースフラグ、新機能、実行計画を分析するためのキラー機能 。

まず、 SQL Server 2016のインスタンスをインストールします。インストーラー自体は、以前のバージョンと比較して変更されています。

x64バージョンのSQL Serverのみがインストールに使用できます（最後のx86ビルドはCTP2.3で削減されました ）。公式には、すべてがより簡潔に聞こえます。 「SQL Server 2016リリース候補（RC0）は64ビットアプリケーションです。 一部の要素は32ビットコンポーネントとして実行されますが、32ビットインストールは中止されます。
Windows 7およびWindows Server 2008へのSQL Server 2016のインストールはサポートされていません。 SQL Serverをインストールできるシステムの公式リスト： Windows 8 / 8.1 / 10およびWindows Server 2012のすべてのx64エディション
SSMSはSQL Serverに同梱されなくなり、個別に開発されています。このリンクからSSMSの スタンドアロン版をダウンロードしてください 。 SSMSの新しいエディションでは、 SQL Server 2005..2016の使用がサポートされているため、バージョンごとにスタジオ群を維持する必要はありません。
RおよびPolyBase言語サポート（ SQL ServerとHadoopの間のブリッジ）を実装する2つの新しいコンポーネントが追加されました。

PolyBaseを機能させるには、まずJRE7またはそれ以降のバージョンをインストールする必要があります。

また、選択したポート範囲をファイアウォールの例外に追加することを忘れないでください：

マイクロソフトに 感謝し ます - インスタントファイルの初期化を有効にするためにグループポリシーを調べる必要はありません。

デフォルトパスの選択に関するダイアログも少し変更されました。

tempdbを構成するために、必要な数のファイルを自動的に作成し、必要に応じて別のディスクに配布できる個別のブックマークを作成しました。ただし、これを行わなくても、デフォルトでインストールされた場合、 Autogrowthパラメーターは1 MBではなく（以前のように） 64 MBになるのは良いことです。

最大ファイルサイズは256MBに制限されています。さらに指定できますが、インストール後：

以前のバージョンと比較したインストールの違いはここで終わりです。

それでは、他に何が変わったのか見てみましょう...

AutoGrowイベントの数を減らすために、 モデルのベースシステム設定が変更されました。

ここでなぜこれが悪いのか読んでください。

また、新しいSQL Serverの一部のトレースフラグ gsがデフォルトで有効になることに言及することも重要です...

-T1118

SQL Serverは、ディスクから64KBチャンク （エクステントと呼ばれる）でデータを読み取ります。エクステントは、物理的に連続する8つのデータベースファイルのグループ（各8 KB ）です。

エクステントには、混合と同種の2つのタイプがあります。混合エクステントは、異なるオブジェクトのページを保持できます。この動作により、非常に小さなテーブルが最小限のスペースを占有できます。しかし、ほとんどの場合、テーブルのサイズは64 KBに制限されておらず、1つのオブジェクトのデータを保存するために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 ：

MIXED_PAGE_ALLOCATION = OFF ：

-T1117

1つのファイルグループ内に複数のファイルを作成できます。たとえば、 tempdbデータベース用に複数のファイルを作成することをお勧めします。これにより、シナリオによってはシステムのパフォーマンスが向上する場合があります。

ここで、ファイルグループ内のすべてのファイルが同じサイズである状況を想定します。大きな一時テーブルが作成されます。ファイル＃1に十分なスペースがなく、もちろんAutoGrowが発生します。しばらくすると、同じテーブルが再作成されますが、ファイル1が一時的にロックされているため、ファイル2で挿入が発生します。それでは何が起こるでしょうか？ ＃2の AutoGrow ...および再遅延クエリ。そのような場合、 TF 1117が提供されました。グローバルに機能し、1つのファイルに十分なスペースがない場合、同じファイルグループ内のすべてのファイルに対して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:\DATABASES\SQL_2016RC0\TEMP\tempdb.mdf 8.000000 64MB temp2 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_2.ndf 8.000000 64MB temp3 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_3.ndf 8.000000 64MB temp4 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_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:\DATABASES\SQL_2016RC0\TEMP\tempdb.mdf 72.000000 64MB temp2 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_2.ndf 8.000000 64MB temp3 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_3.ndf 8.000000 64MB temp4 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_4.ndf 8.000000 64MB

AUTOGROW_ALL_FILES ：

 name physical_name size_mb auto_grow ---------- --------------------------------------------------- ----------- ------------ tempdev D:\DATABASES\SQL_2016RC0\TEMP\tempdb.mdf 72.000000 64MB temp2 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_2.ndf 72.000000 64MB temp3 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_3.ndf 72.000000 64MB temp4 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_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

統計は古いです...もう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

ここで、巨大なテーブルがあると仮定します... 1000万から3000万行。統計を再計算するには、膨大な量のデータを変更するか、統計の更新を手動で監視する必要があります。

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では、このトレースフラグはデフォルトで有効になっています。

スコープ設定

基本レベルでは、設定の新しいグループが登場しました：

新しいsys.database_scoped_configurationsシステムビューから取得できます。個人的には、並列度を以前のようにグローバルにではなく、データベースごとに個別に変更できることを非常に嬉しく思います。

 ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0

古いカーディナリティ 推定をオンにします（以前は、 TF 9481をオンにするか、 互換性レベルを2012に下げる必要がありました ）。

 ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON

パラメータスニッフィングを無効にします （以前はこのために、 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_MODIFY関数を使用してJSONを変更する機能を追加しました。

 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'--')

メッセージ214、レベル16、状態11、行3

プロシージャは、タイプ 'nchar（1）/ nvarchar（1）'のパラメータ 'separator'を予期しています。

パフォーマンスの観点から、古い分割方法と新しい分割方法を比較します。

 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 tcvalue('(./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.

ライブクエリ統計

私が気に入った他のこと... SSMSの新しいバージョンでは、リクエストがリアルタイムでどのように実行されるかを追跡することが可能になりました。

この機能は、 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

読みやすさを維持するために、 RTMバージョンのリリース後に追加する予定のいくつかの新しいSQL Server機能（ 一時テーブル 、 動的データマスキング、およびインメモリの改善）を残しました。

この記事を英語圏の聴衆と共有したい場合：

SQL Server 2016 RC0

SQL Server 2016 RC0