史丹利好熱

去年底上線前的一次系統轉換演練，碰到某支轉檔SQL執行變慢，表象是執行計畫改變(plan change)，進一步找原因則發現是不太即時的統計值造成Query Optimizer產生較差的執行計畫。

去年底的跨年，終於把駐點14個月的專案上線了，這個客戶和幾年前上線的客戶最近都遇上統計值影響效能，來記錄問題，順便重修統計值(Statistics)。

統計值是描述欄位值與索引欄位值的分佈統計，也是SQL Query Optimizer進行基數估計(cardinality estimate)的分析來源，透過基數預估，進而決策出優秀的執行計畫來擷取或更新資料。

統計值統治的範圍包含一般資料表、#臨時資料表以及幾個新的企業版本@資料表變數都有統計值。

資料庫因為胖胖的讓公司測試環境很擠，但想透過上個月月初備份檔(.bak)還原幾個資料表來比較測試結果，測試環境的空間及資源都不足，當初也沒把幾個資料表放在指定檔案群組(File Group)，這次不能使用還原檔案群組招式。

來筆記付費的Third party工具，透過ApexSQL Recover工具，不需要還原整個資料庫就能還原單一資料表。

在SQL 2016以前，Insert Into Select一直都只能用一條執行緒執行資料表插入，SQL Server先在2014版本時優化了Select Into新增了平行，到了SQL 2016版本也優化了Insert Into Select，只要資料庫相容性層級設置為130(SQL 2016)，搭配WITH (TABLOCK)的Table Hint，我們也可以在非叢集索引資料表平行執行Insert Into Select了。來筆記實驗過程。

在SQL Server 2014以前，Select Into一直都只能用一條執行緒執行資料表插入，即使Into到tempdb也是；不過到了SQL 2014之後，只要資料庫相容性層級設置為120(SQL 2014)就可以在成本大的語法平行執行Select Into了。來筆記實驗過程。

維護SQL2000的系統要作資料移轉時，除了寫AP程式或是用ETL工具轉換資料，其實也能繼續用舊機卸離 x 新機附加直接移轉user databse的資料庫檔案。

上一篇實驗了移轉SQL2005的檔案到SQL2017，實驗結果可以直升；這次實驗SQL2000的user database，事前從ms blog的討論發現需要先轉到SQL2005或SQL2008，再跳到SQL2017。
 

這次不用升級SQL Instance版本(就地升級)，但要移轉幾個User Database到新機器上新版本的SQL Instance。在學校曾上過SQL 2000的課，幾年前重新接觸SQL Server，已經是SQL 2008R2 ，幾年下來慢慢也從SQL 2008移轉到2012/2014/2016，好在SQL資料庫的相容性，每次都很順利，不過這次是自己沒使用過的SQL 2005，試試SQL2005到SQL2017，一個12生肖的距離。

很開心在上周末下班前一起和客戶端的.NET架構師解決了開發人員的NHibernate交易使用問題，好久沒用Hibernate這個老牌ORM武器了，連開保險上膛都很生疏，來筆記Hibernate問題解決，順便回憶。

客戶端開發人員的問題是在同一個Transaction中，有三個資料庫的操作，但後面的操作無法讀取到同一個Transaction先前寫入的資料。

經過上一篇的實驗，磁碟資料表(Disk-based table)啟用讀取認可快照(RCSI)或是快照隔離(Snapshot isolation)都能到使用資料列版本讀取到前一版的資料而避免封鎖(blocked)，特別兩者在處理”資料一致性的層級”以及”交易發生衝突的處理上”有些不同；那麼到了記憶體資料表(Memory-optimized table)?

SQL Server在磁碟資料表(Disk-Base)提供了兩種與快照有關的樂觀鎖定機制: RCSI(Read Committed Snapshot Isolation)及Snapshot Isolation，他們都是減少查詢交易被封鎖的武器之一，當資料被其他交易更新時，這兩種機制都可以透過Tempdb加上row version查詢到資料的前一版，讓交易免於被封鎖(blocked)的命運。明晚要參加SQL Pass，Rico大的主題是進擊的In-Memory OLTP，學習記憶體資料表交易前，先來預習傳統磁碟資料表在這兩種機制下的查詢一致性。

想移除歷史交易資料時，很直覺想到串DML Delete；但如果刪除交易量大，怕影響線上交易時，辛苦的攻城師需要改成預存程序分批刪除，每次刪2,000筆還放慢半秒，但鎖定物件較多，批次時間也久；如果資料表能獲得DBA大人同意設置成Partition Table，用上Switch Partition可以飛快的轉出資料；不過Switch Partition手續有些繁瑣，得先建立相同分割檔案群組、相同結構的空資料表再轉移。

SQL Server 2016更方便了，TRUNCATE TABLE支援指定分割編號，秒殺的概念，來試試。

REVERSE是SQL字串函數的一種，可以傳回字串值的反轉順序結果，輸入值的型別可以是字串、數值還有二進位資料(Binary)，最近發現反轉二進位時，偶而會失效，原因是定序(Collate)，筆記問題和解決方式。

延續上週新增資料表欄位Default Value + Not Null是否會給舊資料預設值的題目。上一篇確認Default Value + Not Null會給也必須要給舊資料預設值，但為何相同資料量測試環境飛快但正式環境跑1小時?這種飛快，感覺不太真實，資料分頁真的有修改到?還是只有改定義?

想透過cached plan抓最近經常被使用，而且SQL子樹成本(SubTreeCost)較高的執行計畫作年底上線前的最後衝刺。排進Backlog之前，希望有SQL Text、資料列筆數、執行時間、cpu時間以及圖形執行計畫的資訊綜合輔助自己開Jira Issue。

可惜這家客戶使用的版本不是SQL 2016，超想試試Query Store，今晚我們先組合幾個動態管理檢視(DMV)解任務。

今晚的程式過版中，有一部分是大型資料表要加開10幾個欄位來跟上行動支付潮流，到家後接到客戶電話，DBA大人執行DDL Alter Table欄位新增語法(Add Column)時，執行了1小時(....)。印象中同事的語法沒有加With Values，應該只修改到定義，沒有改到原始的資料分頁，另外，測試環境類似的交易量跑起來也很快，呼~~~

卡住的時候就是該重新測試填補SQL知識了，這篇先確認SQL是否會將舊資料給預設值，下一篇來查效能問題。

SQL Server安裝後，在本機安全性原則中，除了執行磁碟區維護工作權限外，另一個最佳化的設定就是鎖定記憶體分頁了，他可以防止Windows系統將記憶體資料傳送到磁碟上的虛擬記憶體，這邊可以參考德瑞庫克老師或是Rico老師的文章都有更詳盡的說明，但我們想知道怎麼確認安裝廠商已經有正確設定LPIM了?除了監控Paging File查看虛擬記憶體使用，來找找LPIM在SQL的出生證明。

為了減輕查詢交易被大型更新交易封鎖(Blocked)，也理解樂觀鎖定(Optimistic Locking)可能的交易衝突後，也許我們會在SQL Server上使用Oracle預設相同的隔離層級: Read Committed Snapshot Isolation(RCSI)，她是一種Row Version Base的隔離層級，與snapshot isolation不太相同的是她是單一版本。當交易更新資料時，系統會自動將修改前的版本放到Tempdb提供給可能發生的查詢交易。因為也有搬分頁到其他資料庫的工作會執行，在效能上會不會有明顯的副作用?

快照資料庫(Database Snapshots)可以提供資料庫特定時間點的靜態檢視，她是唯讀的資料庫，經常被使用在報表用途，由於資料靜止在指定的時間點，可以有效避免新交易造成報表間的數字差異。多年以後發現有個副作用，一直以為搬Data Page到快照資料庫只會影響一點點點效能，沒想到批次型的大量更新或刪除的交易，影響很明顯。

今天下雨不能練跑，繼續在家認識神話故事中的SQLDiag，上一集用SQLDiag結合Profiler收集資訊，這一集來混搭Perfmon效能監視器，透過Profiler工具整合效能資訊找出效能問題。

交易一多起來，慢慢會碰上交易封鎖(Block)，人若剛好在案發現場能下SQL Query鑑識，透過DMV、sp_trace、profiler、sqldiag等都能快速釐清交易間不單純的案情；但如果想長期追蹤，可以試試SQL Server 2008推出的擴充事件(Extended events)，利用blocked_process_report事件幫我們紀錄過長時間的封鎖(block)。