2015年6月28日 星期日

SQL Server 2014 In-Memory OLTP測試與使用心得

在SQL Server 2014的所有新功能中,最被官方與大家所注視的,就是In-Memory的功能,本篇我們就來介紹此新功能,並將個人的使用心得與搜集到的資訊整理出來,也歡迎大家一定討論與分享。

一開始我們先來簡單的說明何謂In-Memory,一般來說資料庫的資料皆存在磁碟上,來要進行存取時,再透過SQL語法進行處理,並將資料撈取至記憶體中,再進行顯示或處理,在以往如果要加快處理,所以就有許多的軟體或硬體的搭配進而推出RAM-Disk的機制,雖然可以加快處理,但最大的問題是,如果遇到突然斷電時,會造成資料的損失,所以大部份的使用者只敢架構在TempDB的部份。而此次SQL Server整合了此功能(In-Memory),即可解決此問題又可以達到加速處理的效果。

接下來我們就來建立範例並初步看整體In-Memory的運作情況,下列的範例是來自MSDN官網。

1、建立資料庫
CREATE DATABASE imoltp
ON PRIMARY (name = [imoltp_data], filename = 'c:\data\imoltp_mod1.mdf', size=500MB)
LOG ON (name = [imoltp_log], filename='C:\data\imoltp_log.ldf', size=500MB)
GO

ALTER DATABASE imoltp ADD FILEGROUP [imoltp_mod] CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE imoltp ADD FILE (name = [imoltp_dir], filename= 'c:\data\imoltp_dir') TO FILEGROUP imoltp_mod;
GO

上述中我們特別建立了一個FileGroup(imoltp_mod)進行儲存,藉以存放Transaction Log與暫存檔,如下圖所示。

2、建立資料表與新的原生編譯的預存程序
USE imoltp;
GO

IF EXISTS (SELECT NAME FROM sys.objects  WHERE NAME = 'xx')
   DROP PROCEDURE xx
GO

IF EXISTS (SELECT NAME FROM sys.objects WHERE NAME = 'sql')
   DROP TABLE sql
GO

IF EXISTS (SELECT NAME FROM sys.objects WHERE NAME = 'hash')
   DROP TABLE hash
GO

IF EXISTS (SELECT NAME FROM sys.objects  WHERE NAME = 'hash1')
   DROP TABLE hash1
GO


CREATE TABLE [dbo].[sql] (
  c1 INT NOT NULL PRIMARY KEY,
  c2 NCHAR(48) NOT NULL
)
GO

CREATE TABLE [dbo].[hash] (
  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
  c2 NCHAR(48) NOT NULL
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);
GO

CREATE TABLE [dbo].[hash1] (
  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
  c2 NCHAR(48) NOT NULL
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);
GO

CREATE PROCEDURE xx
  @rowcount INT,
  @c NCHAR(48)
  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
  AS
  BEGIN ATOMIC
  WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
  DECLARE @i INT = 1;

  WHILE @i <= @rowcount
  BEGIN;
    INSERT INTO [dbo].[hash1] VALUES (@i, @c);
    SET @i += 1;
  END;
END;
GO


原生編譯的預存程序與傳統的預存程序差別在於原生的預存程序是建立的當下即進行編譯,而傳統的即是在第一次才進行,而且即載入記憶體中,所以可以快速的重覆執行,但原生的預存程序無法進行修改,進行修改時會得到下列的錯誤訊息。

Msg 10794, Level 16, State 25, Procedure xx, Line 52
The operation 'ALTER PROCEDURE' is not supported with natively compiled stored procedures.

3、測試執行差異
SET STATISTICS TIME OFF;
SET NOCOUNT ON;

-- inserts - 1 at a time

DECLARE @starttime datetime2 = sysdatetime();
DECLARE @timems INT;
DECLARE @i INT = 1
DECLARE @rowcount INT = 100000
DECLARE @c NCHAR(48) = N'12345678901234567890123456789012345678'

--- disk-based table and interpreted Transact-SQL

BEGIN TRAN;
  WHILE @I <= @rowcount
  BEGIN
    INSERT INTO [dbo].[sql] VALUES (@i, @c)
    SET @i += 1
  END;
COMMIT;

SET @timems = datediff(ms, @starttime, sysdatetime());
SELECT 'Disk-based table and interpreted Transact-SQL: ' + CAST(@timems AS VARCHAR(10)) + ' ms';

--- Interop Hash
SET @i = 1;
SET @starttime = sysdatetime();

BEGIN TRAN
  WHILE @i <= @rowcount
    BEGIN
      INSERT INTO [dbo].[hash] VALUES (@i, @c);
      SET @i += 1;
    END;
COMMIT;


SET @timems = datediff(ms, @starttime, sysdatetime());
SELECT ' memory-optimized table w/ hash index and interpreted Transact-SQL: ' + CAST(@timems as VARCHAR(10)) + ' ms';

--- Compiled Hash
SET @starttime = sysdatetime();

EXEC xx @rowcount, @c;

SET @timems = datediff(ms, @starttime, sysdatetime());
SELECT 'memory-optimized table w/hash index and native SP:' + CAST(@timems as varchar(10)) + ' ms';


在此次的測試中主要進行三個動作:
a. 新增100000筆資料到傳統的資料庫中
b. 新增100000筆資料到記憶體優化的資料表(memory-optimized table)中。
c. 透過原生編譯的預存程序將100000筆資料新增到記憶體優化的資料表(memory-optimized table)中。

執行結果

從上述的測試中,我們發現在透過記憶體優化的資料表(memory-optimized table)進行處理(第二個測試),效能其實並沒有明顯的差異,但是在透過原生編譯的預存程序一同配合後,效能即約有3~4成的優化成長。

其實在In-Memory的部份目前限制還滿多的,而且並不是每一種資料庫都適用,根據官方的說法,目前只有下列的幾種情境倒是可以一試,但是從我的測試來看,如果沒有配合原生編譯的預存程序,效能改善 "可能" 有限,所以建議大家可以再多多的評估。

實作案例 實作案例 In-Memory OLTP 的優點
來自多個並行連接的高度資料插入比率。 主要的附加專用存放區。
跟不上插入工作負載。
排除競爭。
減少記錄。
定期批次插入和更新的讀取效能與比例調整。 高效能讀取作業,特別是在每個伺服器要求都有數個讀取作業要執行時。
無法符合相應增加的要求。
在新資料到達時排除競爭。
較低延遲的資料擷取。
將程式碼執行時間縮到最短。
資料庫伺服器中密集的商務邏輯處理。 插入、更新及刪除工作負載。
預存程序內部的大量計算。
讀寫競爭。
排除競爭。
將程式碼執行時間縮到最短,以減少延遲並提高輸送量。
低度延遲。 要求一般資料庫解決方案無法達成的低度延遲商務交易。 排除競爭。
將程式碼執行時間縮到最短。
低度延遲的程式碼執行。
有效率的資料擷取。
工作階段狀態管理。 經常性插入、更新及點查閱。
從許多無狀態的 Web 伺服器大範圍載入。
排除競爭。
有效率的資料擷取。
使用非持久性的資料表時,選擇性地減少或移除 IO

以上的資訊來自已MSDN:
In-Memory OLTP (記憶體中最佳化)
https://msdn.microsoft.com/zh-tw/library/dn133186(v=sql.120).aspx


*關於限制的部份,我這邊參考下列的網站整理成下列的列表:

參考網站:
SQL Server 2014, Part 3: Limitations of SQL Server 2014 In-Memory OLTP
http://blog.safepeak.com/sql-server-2014-part-3-limitations-sql-server-2014-memory-oltp/

1、Server and Database limitations
  • REPLICATION is not supported – Officially not supported, although in-memory tables can be defined as Subscribers (but not Publishers)
  • MIRRORING is not supported
  • DATABASE SNAPSHOT is not supported
  • 250GB limit per server – Total data in all loaded in-memory tables cannot exceed 250GB
  • MARS is not supported – Multiple Active Result Sets (MARS) is not supported with natively compiled stored procedures, so your application can’t use MARS connection to talk with the database
  • Change Data Capture (CDC) is not supported
  • DTC (distributed transactions) are not supported
  • RTO (Recovery Time Objective) of your High Availability – the Starting and Recovery time is slower – For every database object (table, stored procedure) SQL Server has to compile and link the corresponding DLL file (that is loaded afterwards into the process space of sqlservr.exe), and this also takes some time. The compilation and linking is also performed when you are restarting your SQL Server, or when you perform a cluster failover.

2、SCHEMA, KEYS, INDEXes, TRIGGERS  limitations:
  • FIXED Schema – You have to design your in-memory tables with knowledge of your data size. Indexes, statistics, and blocks cannot be changed / applied later.
  • ALTER TABLE for existing disk table is not supported – You can’t alter existing tables to become memory-optimized. You have to create new tables that are memory-optimized.
  • ALTER TABLE for in-memory table is not supported – You cannot add another column to a memory-optimized table in your production.
  • Row size limited to 8060 bytes
  • FOREIGN KEY’s and CHECK CONSTRAINTS are not supported
  • Datatypes:
  • Datetimeoffset, Geography, Hierarchyid, Image, Ntext, Text, Sql_variant, Varchar(max), Xml, User data types (UDTs) – not supported
  • CHAR and VARCHAR – Should be replaced to n(var)char
  • Various not supported TABLE and COLUMN definitions: IDENTITY, ON (file group or partition), Data type [name], Computed Columns, FILESTREAM, SPARSE, ROWGUIDCOL, UNIQUE
  • INDEX limitations: no COLUMNSTORE, CLUSTERED INDEX, no LOB datatypes
  • DDL TRIGGERS and Event Notifications (Server and Database level) – Have to be removed before creating or dropping memory-optimized tables and/or natively compiled procedures
  • LOGON TRIGGERS do not affect memory-optimized tables
  • DML TRIGGERS cannot be defined on memory-optimized tables – You can explicitly use stored procedures to insert, update, or delete data to simulate the effect of DML triggers.

3、T-SQL non supported operators:
  • Classic: OR, NOT, IN, LIKE, BETWEEN, CONTAINS, PERCENT, DISTINCT, NEXT VALUE FOR
  • UNION’s are  not supported
  • MIN, MAX – limited to non strings
  • LEFT / RIGHT / FULL OUTER JOIN – Outer joins return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions – not supported (only Inner Joins are supported).
  • FOR XML, FOR BROWSE
  • Dynamic SQL (EXECUTE, EXEC) not supported
  • CURSORs are not supported
  • Sub-Queries are not supported

4、Transactions and Cross Database queries
  • BEGIN, COMIT, ROLLBACK are not supported – “Atomic Blocs” are an alternative
  • Cross Database queries and transactions are limited – You cannot access another database from the same transaction or the same query that also accesses a memory-optimized table. You can create Table Variables, create two transactions: 1) insert the data from the remote table into the variable; 2) Insert the data into the local memory-optimized table from the variable.
  • MERGE – A useful feature with performing insert, update, or delete operations on a target table based on the results of a join with a source table.

5、Functions, Views and Stored Procedures
  • User-defined functions cannot be used in natively compiled stored procedures
  • No In-Memory (“Native”) Functions
  • VIEWs – Views cannot be accessed from natively compiled stored procedures.
  • Disk-based tables cannot be accessed from natively compiled stored procedures. 

6、T-SQL window functions are not supported at all. Examples:
  • ROW_NUMBER()
  • RANK()
  • OVER (PARTITION BY …) or OVER (PARTITION BY …)

參考資料:
Demonstration: Performance Improvement of In-Memory OLTP
https://msdn.microsoft.com/en-us/library/dn530757.aspx
In-Memory OLTP (記憶體中最佳化)
https://msdn.microsoft.com/zh-tw/library/dn133186(v=sql.120).aspx
SQL Server 2014, Part 3: Limitations of SQL Server 2014 In-Memory OLTP
http://blog.safepeak.com/sql-server-2014-part-3-limitations-sql-server-2014-memory-oltp/
Transact-SQL Constructs Not Supported by In-Memory OLTP
https://msdn.microsoft.com/en-us/library/dn246937.aspx


關鍵字:In-Memory記憶體資料表Memory-Optimized TableNatively Compiled Stored Procedures

2015年6月27日 星期六

如何解決檔案被鎖定而無法刪除的問題

        最近要重整檔案,希望將一些舊有的備份檔進行刪除,但是卻發現在刪除時,無法進行,並且發生檔案無法讀取的錯誤,由於檔案數量過多,所以如果要透過排除法進行時,會較為耗時,所以我先將處理的過程整理如下。

首先我們先介紹一個解除鎖定的軟體,此軟體名為 "LockHunter",這是一個免費的軟體而且只有約3MB左右的大小,透過此軟體即可找出檔案是被誰所鎖住,詳細的說明與下載,可以參考官網說明。

LockHunter
http://lockhunter.com/download.htm


1、透過指令的方式進行。
由於透過檔案總管進行時,會發生檔案無法讀取的錯誤,所以我先嘗試透過Dos Command視窗輸入下列的指令進行。

del yourdeletefolder /S /Q

/S -> 由子目錄逐一刪除檔案
/Q -> 安靜模式,不要詢問是否要進行檔案的刪除

指令進行後,發現仍然會有空目錄無法刪除,所以再次透過下列的指令進行空目錄的刪除。

rmdir yourdeletefolder /S /Q

/S -> 由子目錄逐一刪除檔案
/Q -> 安靜模式,不要詢問是否要進行檔案的刪除

在指令進行後發現下列的錯誤畫面,發現主要就是這個資料夾造成的。


2、由於此檔案被鎖住,所以我們就透過 LockHunter 來進行解除,選擇目錄資料夾後,點選滑鼠右鍵 -> What is locking this file?


3、此時即會顯示出此檔案被那一個程式給鎖定,再點選下方的Unlock It!後,即可進行解除。


後續上你即可將此資料夾進行刪除。


關鍵字:LockHunterFile LockFolder LockUnlock檔案鎖定

2015年6月16日 星期二

效能調整 - network packet size 設定討論與調整效益

本篇我們來討論關於SQL Server上的Network Packet Size的設定值,主因由於我的上一篇文章中發現此值的調整會影響到系統的運作,所以我們藉由本篇來說明並探討此設定值。

此設定值預設值為4096(Byte),如同下列的說明,在傳送或接收大量的文字與影像資料時,可以設定大於預設值的封包,將有助於改善效能,因為可以減少網路封包的傳遞,但大部份的情況下預設值已可符合。


下列文字來自MSDN:
  1. 這個選項是進階選項,只有有經驗的資料庫管理員或通過認證的 SQL Server 技術人員才可變更。
  2. 如果應用程式進行大量複製作業,或是傳送或接收大量的文字或影像資料,則使用大於預設值的封包有助於改善效能,因為這樣可以減少網路讀取與寫入的作業。 如果應用程式傳送與接收的資訊量很少,可以將封包大小設定為 512 位元組,這對大部分資料傳輸而言已經足夠。
  3. 在使用不同網路通訊協定的系統上,請針對最常用的通訊協定設定 network packet size。 當網路通訊協定支援大型封包時,network packet size 選項可以改善網路效能。 用戶端應用程式可以覆寫此值。
  4. 您也可以呼叫 OLE DB、開放式資料庫連接 (ODBC) 及 DB-Library 函數來要求變更封包大小。 如果伺服器無法支援要求的封包大小,Database Engine 將會傳送警告訊息給用戶端。 在某些情況下,變更封包大小可能會導致通訊連結失敗,例如以下狀況:

    Native Error: 233, no process is on the other end of the pipe.


實驗一:
我們透過簡單的Ping的指令來嘗試確認如果透過大封包的傳遞時,是否有效的改善效能。

1、ping www.microsoft.com -l 32767 -> 平均約要34ms。
2、ping www.microsoft.com -l 32767 -f -> 由於指定不切割封包,所以無法正常傳遞。
3、ping www.microsoft.com -> 平均時間約要8ms的時間即可。


此篇測試你可以看到在少量資料進行傳遞時,設定大封包時,並不會有所幫助。


實驗二:
我們透過SQL Server Management Studio來進行查詢的測試,我們在連線前,先在網路封包大小的部份份別設定成4096(Byte)與32767(Byte)來進行比較。


測試語法,我們透過DMV查詢並依CPU使用量最高進行排序,詳細的語法可以透過我的另一篇文章進行。

SQL Server - 如何透過DMV進行效能評估與監控
http://caryhsu.blogspot.tw/2012/11/sql-server-dmv.html

1、透過預設值4096(Byte)進行查詢


 2、將設定值更改至32767進行

從二張圖我們可以看出在傳輸的資料量約在7129K左右,但在將封包的傳輸量設定至較大時,在傳遞次數上就從原先的1746降低至219,而總執行時間也從216(ms)降至187(ms),所以在傳輸較大量的資料時,更改此設定值的確可以提升些許效能。


總結:
從上述的二個實驗來看,在大量資料量的傳輸上,更改網路封包大小的設定值,的確是可以些許的提升效能,但是我們要怎麼動態的調整此設定值,根據官方的文件上說明,我們可以從Application Level進行(這也是官方推薦的方式),只需要在連線字串上加入Packet Size的關鍵字設定即可,範例如下:

ConnectionString:
Server=myServerAddress;Database=myDataBase;User ID=myUsername;
Password=myPassword;Trusted_Connection=False;Packet Size=4096;



參考連結:
  • SQL Server連線通訊問題處理(Communication link failure)
    http://caryhsu.blogspot.tw/2015/06/sql-servercommunication-link-failure.html
  • Tabular Data Stream Protocol
    https://msdn.microsoft.com/en-us/library/aa174503(SQL.80).aspx
  • SQL Server TDS or Network Packet Size
    https://sqlconsultant.wordpress.com/2010/08/15/sql-server-tds-or-network-packet-size/
  • 設定 network packet size 伺服器組態選項
    https://msdn.microsoft.com/zh-tw/library/ms177437.aspx
  • Network Packet Size: to Fiddle With or Not to Fiddle With
    http://www.sqlsoldier.com/wp/sqlserver/networkpacketsizetofiddlewithornottofiddlewith
  • Performance Best Practice: Network Packet Size
    http://microsoft-ssis.blogspot.tw/2013/04/performance-best-practice-network.html
  • SqlConnection.ConnectionString Property
    https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

關鍵字:Network Packet SizePerformance Best Practice

2015年6月14日 星期日

SQL Server連線通訊問題處理(Communication link failure)

近日客戶反應發現原本的備份排程無法使用,查看計畫紀錄後,發現下列的錯誤訊息:

錯誤訊息:
日期 2015/6/14 上午 08:04:52
記錄檔 作業記錄 (EverydaysBackup.子計畫_1)

步驟識別碼 1
伺服器 ASUS
作業名稱 EverydaysBackup.子計畫_1
步驟名稱 子計畫_1
持續時間 00:00:00
SQL 嚴重性 0
SQL 訊息識別碼 0
已傳送電子郵件通知操作員
已使用網路傳送通知操作員
已呼叫通知操作員
嘗試的重試次數 0

訊息
以下列使用者的身分執行: ASUS\SYSTEM。Microsoft (R) SQL Server 執行封裝公用程式  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    已啟動:  上午 08:04:52  因為發生錯誤 0xC0014062,所以無法載入封裝 "Maintenance Plans\EverydaysBackup"。  描述: LoadFromSQLServer 方法發現 OLE DB 錯誤碼 0x80004005 (Communication link failure)。發出的 SQL 陳述式失敗。  來源:   已啟動:  上午 08:04:52  已完成: 上午 08:04:52  經過時間:  0.031 秒.  無法載入封裝。.  步驟失敗。

嘗試進行手動執行時,發現仍然有相同的錯誤訊息。


由於此備份是透過維護精靈產生,所以當下再嘗試進行修改,但是卻發生了下列的錯誤。

錯誤訊訊:
Microsoft SQL Server Management Studio 無法載入此文件:
LoadFromSQLServer 方法發現OLE DB 錯誤碼 0x80004005 (Communication link failure)。發出的 SQL 陳述式失敗。

值不能為 null
參數名稱: component (System.Design)


解決方法:
後續上,發現主要是由於此伺服器上,將Network Packet Size的設定為32767(Byte),所以造成此問題,只需將設定更改為原先的4096(Byte)後,即可解決。發生此問題的原因,如下列的文章說明,主要是由於SQL Server的通訊協定TLS(Transport Layer Security(TLS)最大的data fragments只能到16K(16388),所以才會造成此問題,當然如同此篇文章所說明,你可以將此值最大設定成如下,也是可以解決此問題。

另外關於此Network Packet Size設定值的調整,也強烈建議盡量不要更改設定值,詳細我會在另一篇的文章進行說明。


設定語法:
EXEC sp_configure 'network packet size', 16383
RECONFIGURE WITH OVERRIDE
GO


參考連結:
"Communication link failure" error message is reported for SSIS packages on SQL servers configured to use encryption and a large network packet size
https://support.microsoft.com/en-us/kb/2006769


關鍵字:Communication link failurenetwork packet size0x80004005

問題處理案例 - 網頁瀏覽顯示過慢問題處理

        近日遇到使用者不斷的反應網頁系統連線過慢,後續經過不斷的追查,發現主要是由於前端網頁的連線數持續增加,而且並未釋放,所以造成此問題的發生,我將相關的處理心得與過程整理成下列的文章,再提供給大家參考。


問題情況:
1、網頁開啟時會越來越慢,到後來會打不開。
2、許多其他此網頁上的系統網頁執行很慢。

問題處理
1、檢查IIS上的Event Log(Application、System),發現有許多的例外發生(Exception)。
2、檢查DB上的DB Log與Event Log(Application、System),沒有發生任何的錯誤。
3、檢查資料庫沒有異常的Blocking。
4、資料庫嘗試開啟Trace Flag 1204與透過SQL Profiler進行Dead Lock偵測,但沒有發現。
5、嘗試關閉防毒與監控軟體,但情況仍相同。

Detecting and Ending Deadlocks
https://technet.microsoft.com/en-us/library/ms178104%28v=sql.105%29.aspx

6、資料庫上的可用記憶體偏低(低於200MB以下)。
7、資料庫上有許多連線很久未使用的部份,最高達到16000~18000筆左右。
8、IIS端仍有許多的可用資源。
9、嘗試重啟IIS上的Application Pool,但會遇到下列的錯誤訊息,經進一步的查詢後,發現主要是資源鎖定(Dead Lock),所以造成無法重啟。



10、嘗試重新啟動IIS後,發現也有相同的情況,無法正常啟動。
11、透過Process Explorer發現有許多的Handle Count未釋放,而且效能監視器中的Private Byte持續升高,所以懷疑與Handle Leak有關。

關於Handle Leak的部份,可以參考,下列的文章參考。

How to troubleshoot a handle leak?
http://blogs.technet.com/b/yongrhee/archive/2011/12/19/how-to-troubleshoot-a-handle-leak.aspx

12、後續透過程式將未使用的資料庫連線刪除後,發現系統就恢復正常了,我將程式分享如下,再提供給大家參考。

作法說明:
1、找出最後執行的時間與目前時間超過30鐘以上的連線。
2、找出目前未開啟交易的連線。
3、找出非系統連線的部份。
4、查出此連線使用的語法。
5、將刪除的連線寫入到資料表中。

建立資料表
CREATE TABLE [dbo].[kill_proc_backup](
[ssn] [int] IDENTITY(1,1) NOT NULL,
[spid] [smallint] NOT NULL,
[lastwaittype] [nchar](32) NOT NULL,
[dbid] [smallint] NOT NULL,
[cpu] [int] NOT NULL,
[physical_io] [bigint] NOT NULL,
[login_time] [datetime] NOT NULL,
[last_batch] [datetime] NOT NULL,
[status] [nchar](30) NOT NULL,
[hostname] [nchar](128) NOT NULL,
[program_name] [nchar](128) NOT NULL,
[cmd] [nchar](16) NOT NULL,
[loginame] [nchar](128) NOT NULL,
[sql_stat] [nvarchar](max) NULL,
[del_time] [datetime] NULL,
 CONSTRAINT [PK_kill_proc_backup] PRIMARY KEY CLUSTERED
(
[ssn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[kill_proc_backup] ADD  CONSTRAINT [DF_kill_proc_backup_del_time]  DEFAULT (getdate()) FOR [del_time]
GO


Store Procedure程式碼: -- =============================================
-- Author: <Cary Hsu>
-- Create date: <2015/06/04>
-- Description: <delete unused session over 1 hours>
-- =============================================
ALTER PROCEDURE [dbo].[kill_unused_proc]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @spid smallint,
@sqlstring varchar(100)

insert into MonitorDB.dbo.kill_proc_backup(spid, lastwaittype, qs.dbid, cpu, physical_io, login_time, last_batch, status, hostname, program_name, cmd, loginame, sql_stat)
select spid, lastwaittype, qs.dbid, cpu, physical_io, login_time,
last_batch, status, hostname, program_name, cmd, loginame, qt.[text]
from sys.sysprocesses qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt
where open_tran = 0
and Datediff(minute, last_batch, GETDATE()) >= 30
and spid >= 50

DECLARE kill_proc_cursor CURSOR FOR
select spid
from sys.sysprocesses qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt
where open_tran = 0
and Datediff(minute, last_batch, GETDATE()) >= 30
and spid >= 50

OPEN kill_proc_cursor

FETCH NEXT FROM kill_proc_cursor
INTO @spid

WHILE @@FETCH_STATUS = 0
BEGIN
set @sqlstring = 'Kill ' + CONVERT(varchar(4), @spid)
exec(@sqlstring)

FETCH NEXT FROM kill_proc_cursor
INTO @spid
END
CLOSE kill_proc_cursor;
DEALLOCATE kill_proc_cursor;
END



關鍵字:SQL Server Connectionhandle leakmemory leakKill Process