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

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


問題情況：
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 Connection、handle leak、memory leak、Kill Process