[SQL Server]Index管理和調教

曾經提到如何使用DMV找出Top 10 IO/CPU Query的SQL指令，

通常會針對前幾名消耗硬體資源較大的SQL指令進行索引調教，

首先先介紹查詢Index使用情況的DMV:sys.dm_db_index_usage_stats

--使用語法如下
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       USER_SEEKS, 
       USER_SCANS, 
       USER_LOOKUPS, 
       USER_UPDATES 
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S 
       INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
       AND S.database_id = DB_ID()

由以上的查詢結果得知，每各Index被讀取(USER_SEEKS+USER_SCANS+USER_LOOKUPS)的次數以及

被更新(USER_UPDATES)的次數，因為Index如果沒有經常被讀取，那代表他是比較沒有效益的，

另外無用的索引建太多，會影響Insert/Update/Delete的效率，因此索引不是建的愈多愈好，

例如:在OLTP系統經常有交易在進行的情況下，反而容易造成鎖定的產生，進而拉長應用程式等待交易的時間!!

[SQL Server]SQL Profiler和SQL Trace的介紹

曾經有人問過我，要如何錄製他的程式對SQL Server所下的SQL指令，

筆者回他用SQL Profiler就可以錄到了，但要切記一點:必須要主動過濾錄製的範圍，

因為如果是正式環境，上面會有大量的指令在進行中，一旦沒有去設定自己要錄的條件，

會錄到許多自己不想要的內容。

在實務上通常會以HostName(主機名稱)、LoginName(登入帳號)、TextData(對SQL Server下的指令)，

這三個欄位來進行過濾，實作步驟如下:

進入SQL Profiler->檔案->新增追蹤
因為只要錄製特定帳號User1下的指令，Events只要選擇TSQL即可

接著再點選資料行篩選，在左邊清單找到LoginName，在右邊類似欄位中輸入%user1%後，
便可以開始執行錄製了!

在SSMS下用user1帳號登入後，輸入以下指令進行查詢

執行後，立刻去SQL Profiler查看，果然己經錄製下來!

[SQL Server]SQL Server 2016支援的資料遮罩

以前在做資料遮罩時，幾乎都是要自己寫些程式才能實作出來，

現在SQL Server 2016就有提供遮罩的功能供大家使用，以下為用實例來為大家介紹用法:

首先先建立範例資料:

CREATE TABLE Cust(
  ID varchar(11) primary key,
        Name nvarchar(10),
  Birth date,
  Mail varchar(50),  
        Credit varchar(19))
GO

INSERT INTO Cust 
 VALUES ('A123456789', N'陳一', '19700101', 'aa@gmail.com', '1111-2222-3333-4444')
      , ('D234567890', N'蔡三', '19790202', 'bb@gmail.com', '5555-6666-7777-8888')
      , ('H987654321', N'老王', '19810303', 'cb@gmail.com', '9999-1111-2222-3333')

建立測試資料後，先針對生日欄位設定預設的遮罩功能

ALTER TABLE Cust ALTER COLUMN Birthday ADD MASKED WITH(FUNCTION='default()')

接著再建立使用者masktester，讓他有select的權限，再用他來查詢資料

CREATE USER masktester WITHOUT LOGIN
GRANT SELECT ON Cust TO masktester

EXEC AS USER = 'masktester'
SELECT * FROM Cust

查詢結果如下，原本的生日都變成1900-01-01了!

以上是基本的遮罩應用。

現在我們來做些進階應用，例如Email、信用卡卡號等重要資訊，

可用partial,email()等參數來進行客製化遮罩。

ALTER TABLE Cust ALTER COLUMN CreditCard ADD MASKED WITH (FUNCTION = 'partial(4,"-XXXX-XXXX-",4)')
ALTER TABLE Cust ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')

EXEC AS USER = 'masktester'
SELECT * FROM Cust
REVERT

遮罩效果如下:

SQL Server 2016提供的遮罩方式是By User來進行控管的，

因此在實際應用上要針對不同的User來設定是否需要開啓或關閉遮罩。

--關閉遮罩
GRANT UNMASK TO masktester
--恢復遮罩
REVOKE UNMASK TO masktester

[SQL Server]使用DMV找出最消耗CPU/IO的SQL指令

在實務上經常會定期找出消耗CPU/IO成本比較大的Top 10 Query來進行語法調教，
以增進DB的效能和AP查詢回應的速度，以下介紹常用到的二種DMV:

--查出前十名總累積IO數最高的的Query
SELECT TOP 10
total_logical_reads+total_logical_writes AS [總IO],
execution_count AS [執行次數],
total_logical_reads AS [讀取IO數],
total_logical_writes AS [寫入IO數],
st.text AS [SQL指令]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE total_logical_reads+total_logical_writes> 0
ORDER BY [總IO] DESC

查詢結果如下:

--查出前十名總使用CPU時間最高的的Query
SELECT  TOP 10 
qs.total_worker_time AS [總執行時間],
qs.execution_count AS [執行次數],
qs.total_worker_time / qs.execution_count AS [平均執行時間],
SUBSTRING(qt.text, qs.statement_start_offset / 2, 
(CASE WHEN qs.statement_end_offset = -1 THEN len(CONVERT (NVARCHAR (MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) AS [SQL指令]
FROM     sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt
ORDER BY [總執行時間] DESC;

查詢結果如下:

SQL指令調教的方法不外乎是新增缺乏的索引或定期維護索引，
再者可以針對擁有大量資料量的表格進行資料定期刪除，
又或者可將少被使用的歷史資料(例如:半年前或上一季)切到其它張表封存，
等到有需要查詢再進行存取，這作法也是可以有效的減少查詢回應的時間。