SQL Server 2016 ->> T-SQL新特性

標籤：實驗   pac   div   斜杠   sch   預設   index   層級   cap   

1） TRUNCATE表分區而不是整表

 

CREATE TABLE dbo.TruncatePartitionTest (    PrtCol INT,    Col2 NVARCHAR(300))ON [myPS1](PrtCol)GOINSERT dbo.TruncatePartitionTest VALUES(1,‘AAA‘), (11,‘AAA‘), (100,‘AAA‘), (101,‘AAA‘)GO-- TRUNCATE partitions 1 to 2TRUNCATE TABLE dbo.TruncatePartitionTest WITH (PARTITIONS(1 TO 2));GOSELECT * FROM dbo.TruncatePartitionTest GOTRUNCATE TABLE dbo.TruncatePartitionTestGOINSERT dbo.TruncatePartitionTest VALUES(1,‘AAA‘), (11,‘AAA‘), (100,‘AAA‘), (101,‘AAA‘)GO-- TRUNCATE partition 1TRUNCATE TABLE dbo.TruncatePartitionTest WITH (PARTITIONS(1));GOSELECT * FROM dbo.TruncatePartitionTest GOTRUNCATE TABLE dbo.TruncatePartitionTestGOINSERT dbo.TruncatePartitionTest VALUES(1,‘AAA‘), (11,‘AAA‘), (100,‘AAA‘), (101,‘AAA‘)GO-- TRUNCATE partitions 1 and 2TRUNCATE TABLE dbo.TruncatePartitionTest WITH (PARTITIONS(1,2));GOSELECT * FROM dbo.TruncatePartitionTest GO

 

2) 新的查詢提示NO_PERFORMANCE_SPOOL

為了實驗這個新的查詢提示關鍵字，我對一個查詢語句啟用和不啟用這個查詢提示後的IO統計資料進行一個對比。

 

添加了NO_PERFORMANCE_SPOOL的語句和IO統計資料

SET STATISTICS IO ONGOSELECT *FROM [dbo].[vwPO]WHERE PODATE BETWEEN ‘2012-01-01‘ AND ‘2014-01-31‘OPTION (NO_PERFORMANCE_SPOOL);  

 

(5448 row(s) affected)Table ‘Workfile‘. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table ‘Worktable‘. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table ‘Address‘. Scan count 5, logical reads 49038, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table ‘PurchaseOrder‘. Scan count 5, logical reads 9267, physical reads 0, read-ahead reads 0, lob logical reads 17936, lob physical reads 0, lob read-ahead reads 0.

 

 

 

未添加查詢提示

SET STATISTICS IO ONGOSELECT *FROM [dbo].[vwPO]WHERE PODATE BETWEEN ‘2012-01-01‘ AND ‘2014-01-31‘;  

 

(5448 row(s) affected)Table ‘Workfile‘. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table ‘Worktable‘. Scan count 1, logical reads 24864, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table ‘tblAddress‘. Scan count 5, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.lob read-ahead reads 0.Table ‘tblPurchaseOrder‘. Scan count 5, logical reads 9267, physical reads 0, read-ahead reads 0, lob logical reads 17936, lob physical reads 0, lob read-ahead reads 0.

 

 

其實最後兩者在時間上的差別基本沒有，做法上的最大差別也顯而易見且可想而知。SPOOL操作符的特點就是建立Work table來供後面的操作符使用。而既然禁用了SPOOL操作符也就意味著不使用Work table，這樣上面的IO Statistics結果也反映了這一點。但是並不意味著這是好事。不使用work table意味著後面每次都需要掃描表，這種開銷也蠻大的。這個例子裡面因為涉及掃描的表小所以差異微乎其微。在我看來，這個功能在現實中的使用情境很少見。只能說有比沒有好。本來執行計畫的東西非不得已都是讓SQL SERVER自己決定。微軟宣稱這個查詢提示可以大大改善高並發情境下使用了SPOOL的查詢語句效能。這點我承認。但是，在我看來，這是屬於治標不治本的做法。一般使用到SPOOL的語句，很大可能是因為沒有適用的索引，所以根本原因很可能是缺乏可用的索引。其次，適用了這個提示可能導致過多的表掃描，效能上未必有提升。真正應該考慮的是審查語句是否存在改寫的可能性來降低複雜性從而改變執行計畫使得預建的索引得以使用最終獲得最優的執行計畫，以及是否需要stage資料來解決高並發問題。

 

3) DROP IF EXISTS 語句

以前要DROP某張表某個預存程序總是需要IF EXISTS(SELECT * FROM sys.objects WHERE name = ‘‘ AND .....)，現在終於有更加簡潔的做法來實現。

 

DROP  <TABLE|PROCEDURE|VIEW|FUNCTION|TRIGGER> IF EXISTS <name>

 

它可以作用於下面這些資料庫物件

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

 

4) 在使用DBCC CHECKTABLE和DBCC CHECKDB這種語句的時候可以使用MAXDOP語句來避免對系統效能造成過多的影響，因為如果有業務關鍵作業正在工作，而伺服器的處理器核心數不多，DBCC CHECKTABLE容易導致頻繁的環境切換。

 

 5）可以使用sp_set_session_context 來設定會話層級的上下文以及使用SESSION_CONTEXT來查看某個會話層級主鍵的上下文內容。

EXEC sp_set_session_context ‘user_id‘, 4;  SELECT SESSION_CONTEXT(N‘user_id‘);  

 

6）sp_execute_external_script 預存程序支援在SQL SERVER 2016中執行R語言指令碼以及通過CREATE EXTERNAL RESOURCE POOL來為R語言建立資源集區

 

7）COMPRESS和DECOMPRESS函數可以使用GZIP壓縮演算法壓縮字串和使用ZIP演算法解壓縮字串

 

8）新的函數DATEDIFF_BIGINT和DATEDIFF作用是一樣，不過區別是傳回值是BIGINT，這樣在處理millisecond這樣的情況是就可以支援更長的時間差。新的函數AT TIME ZONE則可以輸出帶有時區的datetimeoffset。

SELECT getdate() AT TIME ZONE ‘China Standard Time‘;  -- from select * from sys.time_zone_info

 

9）兩個新的字串函數 STRING_SPLIT和 STRING_ESCAPE。前者是個表函數，輸入text和分隔字元就可以分割字串變成表返回。問題是分隔字元只支援1個字元長度的字元。而後者是為了協助json格式的資料轉義特殊字元，比如斜杠。

 

10）新的資料庫選項MIXED_PAGE_ALLOCATION 可以指定資料庫是否預設情況下是否首先對索引分配混合分區，SQL SERVER 2016以前的版本某人情況下是分配混合分區先。

 

SQL Server 2016 ->> T-SQL新特性