[SQL] 透過擴充事件來捕捉 DeadLock

擴充事件預設啟用的 System_Health ，就包含 DeadLock 相關資訊

官方文章內容 - System_Health 所收集的資訊

• The sql_text and session_id for any sessions that encounter an error that has a severity >= 20.
• The sql_text and session_id for any sessions that encounter a memory-related error. The errors include 17803, 701, 802, 8645, 8651, 8657 and 8902.
• A record of any non-yielding scheduler problems. These appear in the SQL Server error log as error 17883.
• Any deadlocks that are detected, including the deadlock graph.
• The callstack, sql_text, and session_id for any sessions that have waited on latches (or other interesting resources) for > 15 seconds
• The callstack, sql_text, and session_id for any sessions that have waited on locks for > 30 seconds.
• The callstack, sql_text, and session_id for any sessions that have waited for a long time for preemptive waits. The duration varies by wait type. A preemptive wait is where SQL Server is waiting for external API calls.
• The callstack and session_id for CLR allocation and virtual allocation failures.
• The ring buffer events for the memory broker, scheduler monitor, memory node OOM, security, and connectivity.
• System component results from sp_server_diagnostics.
• Instance health collected by scheduler_monitor_system_health_ring_buffer_recorded.
• CLR Allocation failures.
• Connectivity errors using connectivity_ring_buffer_recorded.
• Security errors using security_error_ring_buffer_recorded.

透過之前筆記 - [SQL] 模擬死結產生 來產生死結，並在擴充事件內觀察 DeadLock，以下為 SSMS 操作步驟

利用 sys.fn_xe_file_target_read_file 來抓取 DeadLock 相關資訊

SELECT 
	-- DeadLock XML 內容
	CONVERT(xml,event_data).query('/event/data/value/deadlock') as DeadLockGraph ,
    
	-- 直接抓 DeadLock XML 內的 timestamp
	CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime')  AS Execution_Time ,
    
	-- timestamp_utc 欄位資訊在 SQL Server 2017 才開始提供
	timestamp_utc
FROM sys.fn_xe_file_target_read_file('system_health*.xel', null, null, null)
WHERE [object_name] = 'xml_deadlock_report'
ORDER BY timestamp_utc DESC

sys.fn_xe_file_target_read_file 抓出來的 DeadLock XML 檔案，另存為 xdl 後，再利用 SSMS 開啟，就可以看見 DeadLock Graph

• 參考資料
• 使用 system_health 工作階段
• sys.fn_xe_file_target_read_file
• [SQL Server][DeakLock]觀察死結的工具(四)擴充事件(Extended events)
• Extracting Deadlock information using SYSTEM_HEALTH Extended Events

[C#] 以系統管理員身分執行此程式

要讓應用程式可以以系統管理員身分來執行，可以在 [應用程式清單檔案 (app.manifest)] 內設定requestedExecutionLevel 參數

先在 Project 內新增  應用程式清單檔案 (app.manifest)

應用程式清單檔案 (app.manifest) 內就可以看到 requestedExecutionLevel 參數，預設為 asInvoker，變更為 requireAdministrator 就行，其實 comment 備註內都有說明，下面只擷取 requestedExecutionLevel 參數內容而已

<?xml version="1.0" encoding="utf-8"?>
<assembly manifestVersion="1.0" xmlns="urn:schemas-microsoft-com:asm.v1">
  <assemblyIdentity version="1.0.0.0" name="MyApplication.app"/>
  <trustInfo xmlns="urn:schemas-microsoft-com:asm.v2">
    <security>
      <requestedPrivileges xmlns="urn:schemas-microsoft-com:asm.v3">
        <!-- UAC 資訊清單選項
             如果要變更 Windows 使用者帳戶控制層級，請將 
             requestedExecutionLevel 節點以下列其中之一取代。

        <requestedExecutionLevel  level="asInvoker" uiAccess="false" />
        <requestedExecutionLevel  level="requireAdministrator" uiAccess="false" />
        <requestedExecutionLevel  level="highestAvailable" uiAccess="false" />

            指定 requestedExecutionLevel 項目會停用檔案及登錄虛擬化。
            如果您的應用程式需要針對回溯相容性進行這項虛擬化，請移除這個
            項目。
        -->
        <requestedExecutionLevel  level="requireAdministrator" uiAccess="false" />
      </requestedPrivileges>
    </security>
  </trustInfo>
</assembly>

設定完成後，執行檔案圖示右下角就會有個盾牌出現

不透過應用程式清單檔案 (app.manifest) 來設定的話，也可以直接去執行檔內進行設定 

 

 

• 延伸閱讀
• [RV] ReportViewer 和 DPI 感知

• 參考資料
• [C#] 讓程式以系統管理員身分執行(UAC)

[C#] 更換執行檔參考 dll 位置

專案內會參考很多 dll 或套件，VS 建置預設 exe 和 dll 會放置在 debug 或 release 資料夾內，假如希望把相同功能 dll 整理在同資料夾，就需要在 App.Config 內明確指定 dll 引用位置

建立 DllLocation Solution 內 Core 和 ERP 兩個 Project 來模擬，build 至 release 資料夾後，建立 Lib 資料夾並把 Core.dll 搬移過去，如下圖

整理過後執行 exe 檔案，會跳出下圖

參考該篇官方文章 - <probing> 項目 ，在 AppConfig 內加入設定 probing

<?xml version="1.0" encoding="utf-8" ?>
<configuration>

  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <probing privatePath="Lib"/>
    </assemblyBinding>
  </runtime>

  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
  </startup>
</configuration>

這樣執行檔就可以正常引用 dll 囉

• 官方範例說明 - 指定組件的位置

<configuration>  
   <runtime>  
      <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">  
         <probing privatePath="bin;bin2\subbin;bin3"/>  
      </assemblyBinding>  
   </runtime>  
</configuration>  

PrivatePath 屬性包含執行時間應搜尋元件的目錄。如果應用程式位於 C:\Program Files\MyApp，則執行時間會尋找在 C:\Program Files\MyApp\Bin、C:\Program Files\MyApp\Bin2\Subbin 和 C:\Program Files\MyApp\Bin3. 中指定程式碼基底的元件 PrivatePath 中指定的目錄必須是應用程式基底目錄的子目錄。

• 參考資料
• 透過程式碼基底或探查找出組件

[C#] BackgroundWorker - Exception

使用 BackgroundWorker 時發現執行到一半就結束的情況，沒有 Exception 拋出，直覺是 Exception 被吃掉了，查之前筆記 - [C#] BackgroundWorker 範例4 發現，Exception 必須在 RunWorkerCompleted Event 內處理，之前筆記時沒有意識到這點，單獨在紀錄一下

官方文章說明 - BackgroundWorker.DoWork

If the operation raises an exception that your code does not handle, the BackgroundWorker catches the exception and passes it into the RunWorkerCompleted event handler, where it is exposed as the Error property of System.ComponentModel.RunWorkerCompletedEventArgs. If you are running under the Visual Studio debugger, the debugger will break at the point in the DoWork event handler where the unhandled exception was raised. If you have more than one BackgroundWorker, you should not reference any of them directly, as this would couple your DoWork event handler to a specific instance of BackgroundWorker. Instead, you should access your BackgroundWorker by casting the sender parameter in your DoWork event handler.

namespace BackgroundWorkerException
{
    public partial class Form1 : Form
    {
        private BackgroundWorker worker = new BackgroundWorker();

        public Form1()
        {
            InitializeComponent();
            worker.DoWork += Worker_DoWork;
            worker.RunWorkerCompleted += Worker_RunWorkerCompleted;
        }

        private void Worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            // DoWork 內發生 Exception，會存放在 RunWorkerCompletedEventArgs.Error 內，
            // 所以 BackgroundWorker Exception 必須在 RunWorkerCompleted 內處理
            if (e.Error != null)
                MessageBox.Show(e.Error.Message);
        }

        private void Worker_DoWork(object sender, DoWorkEventArgs e)
        {
            throw new Exception("從 DoWork 內拋出 Exception");
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            worker.RunWorkerAsync();
        }
    }
}

[C#] 利用 ZipFile 進行壓縮與解壓縮

根據官方文章 - 操作說明：壓縮與解壓縮檔案 了解 ZipFile Class 使用，練習重點在於

• 壓縮
• 解壓縮
• 從 Zip 檔案內解壓縮特定格式檔案

在 .Net Framework 內必須把 System.IO.Compression 和 System.IO.Compression.ZipFile 加入參考

C# Code 練習整理

using System;
using System.IO.Compression;
using System.IO;
using System.Linq;

namespace ZipFileSample
{
    class Program
    {
        static void Main(string[] args)
        {
            // 相關路徑
            string mainPath = @"D:\ZipFileSample";
            string startPath = Path.Combine(mainPath, "Files");
            string zipPath = Path.Combine(mainPath, "result.zip");
            string extractAllPath = Path.Combine(mainPath, @"Extract\All");
            string extractImagePath = Path.Combine(mainPath, @"Extract\Image");

            // 針對資料夾進行壓縮
            if (File.Exists(zipPath)) File.Delete(zipPath);
            ZipFile.CreateFromDirectory(startPath, zipPath);

            // 確認路徑最後有 [\] 符號存在，避免 Path Traversal Attack
            if (!extractAllPath.EndsWith(Path.DirectorySeparatorChar.ToString(), StringComparison.Ordinal))
                extractAllPath += Path.DirectorySeparatorChar;

            // 針對 Zip File 進行解壓縮
            if (Directory.Exists(extractAllPath)) Directory.Delete(extractAllPath ,true);
            ZipFile.ExtractToDirectory(zipPath, extractAllPath);

            // 針對 ZipFile 內的 bmp 檔案進行解壓縮，把範例內的 foreach 轉為 LINQ 練習
            using (ZipArchive archive = ZipFile.OpenRead(zipPath))
            {
                archive.Entries
                    .Where(entry => entry.FullName.EndsWith(".bmp", StringComparison.OrdinalIgnoreCase))
                    .Select(entry => new
                    {
                        entry,
                        DestinationPath = Path.GetFullPath(Path.Combine(extractImagePath, entry.FullName))
                    })
                    .ToList()
                    .ForEach(entryVar => 
                    {
                    	// Ordinal match is safest, case-sensitive volumes can be mounted within volumes that are case-insensitive.
                        if (entryVar.DestinationPath.StartsWith(extractImagePath, StringComparison.Ordinal))
                        {
                            entryVar.entry.ExtractToFile(entryVar.DestinationPath , true);
                        }
                    });
            }
        }
    }
}

文章內 Path Traversal Attack 說明

當您在解壓縮檔案時，必須尋找惡意的檔案路徑，以免它逸出您解壓縮的目的目錄。 這是所謂的路徑周遊攻擊。 以下範例示範如何檢查惡意的檔案路徑，並提供安全的方式來解壓縮。

• 參考資料
• ZipFile 類別

[VS] 使用 Configuration Transform 依組態切換 App.Config

在 Console 或 WinForm 中，可以使用 Configuration Transform 依組態切換 App.Config，來達到不同環境使用不同連線字串或參數設定來 run

先安裝 Configuration Transform 套件

安裝完成後，可以在 App.Config 上看見功能選項

安裝完成後會出現 App.Debug.Config 和 App.Release.Config 兩個設定檔案

App.config 內建立連線字串和自訂變數選項

<?xml version="1.0" encoding="utf-8" ?>
<configuration>

  <!--連線字串-->
  <connectionStrings>
    <add name="default" connectionString="" providerName="System.Data.SqlClient" />
  </connectionStrings>

  <!--自訂變數-->
  <appSettings>
    <add key="Path" value=""/>
  </appSettings>

</configuration>

App.Debug.config 內設定 Debug 環境下

• 連線字串：利用 Windows 驗證連線 Local SQL Server
• 自訂變數值：D:\DebugDir

<?xml version="1.0"?>
<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">

  <connectionStrings>
    <add name="default" connectionString="Data Source=.\SQL2017;Initial Catalog=AdventureWorks;Integrated Security=True" providerName="System.Data.SqlClient" xdt:Locator="Match(name)" xdt:Transform="Replace"/>
  </connectionStrings>

  <appSettings>
    <add key="Path" value="D:\DebugDir\" xdt:Locator="Match(key)" xdt:Transform="Replace" />
  </appSettings>

</configuration>

App.Release.config 內設定 Release 環境下

• 連線字串：利用 SQL 驗證連線 Product 環境上的 SQL Server
• 自訂變數值：D:\ReleaseDir

<?xml version="1.0"?>
<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">

  <connectionStrings>
    <add name="default" connectionString="Data Source=IPAddress,PortNumber;Initial Catalog=AdventureWorks;UID=LabDemo;PWD=P@ssword;" providerName="System.Data.SqlClient" xdt:Locator="Match(name)" xdt:Transform="Replace"/>
  </connectionStrings>

  <appSettings>
    <add key="Path" value="D:\ReleaseDir\" xdt:Locator="Match(key)" xdt:Transform="Replace"/>
  </appSettings>

</configuration>

設定完成後，可以透過 [Preview Config Transforms] 來比對差異

練習重點紀錄

• 在官方網站教學是只設定 App.Config 和 App.Debug.Config 內容，App.Release.Config 沒有任何設定，就會以預設的 App.Config 為主
• xdt:Locator 和 xdt:Transform 設定可以透過該文章 -  ASP.NET Web Deployment using Visual Studio: Web.config File Transformations 來了解，xdt 參數在 App.Debug.Config 和 App.Release.Config 內一定要有喔

最後測試一下 debug 和 release 模式下的 App.Config 設定值

using System;
using System.Configuration;

namespace ConfigurationTransform
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine(ConfigurationManager.ConnectionStrings["default"]);
            Console.WriteLine(ConfigurationManager.AppSettings["Path"]);
        }
    }
}

Debug 模式

Release 模式

• 參考資料
• [C#] 如何依組態切換App.config中的設定值
• 【Visual Studio】透過Configuration Transform套件快速依照建置組態切換App.Config
• [.NET] Web.config / App.config Transformation (switch Debug or Release mode)
  

[HyperV] vmcx 檔案刪除

偶而用 Hyper-V 來建立模擬環境，發現常放置 VM 的資料夾內，不知何故還存在已刪除 VM 的 vmcx 檔案

透過該文章 - Hyper-V Old VM folders cannot be deleted 發現，先把 Hyper-V 服務停掉，就可以順利刪除檔案

[SQL] 資料表值參數 (Table Value Parameters)

SQL Server 2008 功能 - 資料表值參數 (Table Value Parameters)，簡稱 TVP，紀錄 MS SQL Table Type 建立和三種程式呼叫方式，基本上程式端都是透過對 Store Procedure 傳入 DataTable 來執行

官方文件內的資料表值參數的限制

1. 無法將資料表值參數傳遞至 CLR 使用者定義函式。
2. 資料表值參數索引只支援 UNIQUE 或 PRIMARY KEY 條件約束。 SQL Server 不會維護資料表值參數的統計資料。
3. 資料表值參數在 Transact-SQL 程式碼中處於唯讀狀態。 您無法更新資料表值參數資料列中的資料行值，也無法插入或刪除資料列。 若要在資料表值參數中修改傳遞至預存程序或參數化陳述式的資料，您必須將資料插入至暫存資料表或資料表變數中。
4. 無法使用 ALTER TABLE 陳述式來修改資料表值參數的設計

MS SQL Table Type 和 Store Procedure 設定方式

------ Step1：建立 Table Type
CREATE TYPE dbo.ProductCategoryTableType AS TABLE  
    ([Name] nvarchar(50)) 

------ Step2：建立 Store Procedure
-- 建立 Insert 相關
CREATE PROCEDURE uspProductCategories_Insert
    (@tvpProductCategories dbo.ProductCategoryTableType READONLY)  
AS
	BEGIN
		INSERT INTO [Production].[ProductCategory] 
			(
				[Name] , 
				rowguid  , 
				ModifiedDate
			)
		SELECT 
			[Name] , 
			NEWID()  , 
			GETDATE()
		FROM @tvpProductCategories ;
	END

-- 建立 Delete 相關
-- [ProductCategoryID] 才是 PK，方便記錄就用 Name 來進行 JOIN 
CREATE PROCEDURE uspProductCategories_Delete
    (@tvpProductCategories dbo.ProductCategoryTableType READONLY)  
AS
	BEGIN
		DELETE P
		FROM [Production].[ProductCategory] AS P
			JOIN @tvpProductCategories AS tvp ON P.[Name] = tvp.[Name]
	END

透過 Console 來記錄三種呼叫方式，方別為 ADO.NET、Dapper 和 EF

using System;
using System.Data;
using System.Data.SqlClient;
using Dapper;

namespace TVPDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = @"Data Source=.\SQL2017;Initial Catalog=AdventureWorks2017;Integrated Security=True;";
            DataTable dtProductCategory = GetDataTableProductCategory();
            // 透過變更 CRUDMode 來決定 insert 或 delete
            string crudModel = CRUDMode.Insert.ToString();

            string spName = $"uspProductCategories_{crudModel}";

            #region ADO.NET 呼叫 TVP
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(spName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter tvpParam = cmd.Parameters.AddWithValue("@tvpProductCategories", dtProductCategory);
                tvpParam.SqlDbType = SqlDbType.Structured;

                conn.Open();
                cmd.ExecuteNonQuery();
            }
            #endregion

            #region Dapper 呼叫 TVP
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                conn.Execute
                    (
                        spName,
                        // tvpProductCategories 為 SP 參數，不用前面的 @ 符號
                        // ProductCategoryTableType 為 Table Type
                        new { tvpProductCategories = dtProductCategory.AsTableValuedParameter("ProductCategoryTableType") },
                        commandType: CommandType.StoredProcedure
                    );
            }
            #endregion

            #region EF 呼叫 TVP
            using (AdventureWorksContext context = new AdventureWorksContext())
            {
                // @tvpProductCategories 為 SP 參數
                string cmdText = $"EXEC {spName} @tvpProductCategories";
                SqlParameter tvpParam = new SqlParameter("@tvpProductCategories" , SqlDbType.Structured);
                // ProductCategoryTableType 為 Table Type
                tvpParam.TypeName = "dbo.ProductCategoryTableType";
                tvpParam.Value = dtProductCategory;

                context.Database.ExecuteSqlCommand(cmdText, tvpParam);
            } 
            #endregion
        }

        enum CRUDMode
        {
            Insert,
            Delete
        }

        private static DataTable GetDataTableProductCategory()
        {
            DataTable dtProductCategory = new DataTable();
            dtProductCategory.Columns.Add("Name", typeof(string));
            dtProductCategory.Rows.Add("分類1");
            dtProductCategory.Rows.Add("分類2");
            dtProductCategory.Rows.Add("分類3");
            dtProductCategory.Rows.Add("分類4");
            dtProductCategory.Rows.Add("分類5");
            return dtProductCategory;
        }
    }
}

• 參考資料
• 資料表值參數
• 使用資料表值參數
• 使用者定義資料表類型
• 其他資料表值參數中繼資料
• 資料表值參數(Table Value Parameters)簡介與實作
• Dapper - Parameter TVP
• Pass Dapper Parameter With TVP
• Using Table Valued Parameters in Entity Framework
• [BUG] OBJECTSTORE_LBSS
• How TVP Affect SQL Server Memory
• SQL -善用EF Core搭配TVP提高查詢效能