[SQL Server]主動通知SQL Server發生錯誤的機制

當SQL Server有特定錯誤發生時，管理人員也沒有時間經常去查看SQL Server錯誤檔，

於是有時特定錯誤可能很重要時，但卻沒有人被通知到，現在小弟實作一個主動通知的機制。

首先先新增警示，在這裡選擇想要被通知的錯誤訊息的等級或其它設定


接著在回應頁籤中，勾選執行作業->選擇作業

當有我們指定的錯誤層級發生時，這個作業會立刻被執行，

因此這個作業內容就是要來撰寫發送通知的相關SQL Script，

基本上排程Script內容大致上會先去抓取SQL錯誤檔的內容，

接著再透過系統SP:sp_send_dbmail來發送EMail通知管理者。

檢查資料庫中每張資料表的使用狀況

通常在系統上線後，隨著User日積月累地去操作系統的次數，
你會發現有些資料其實不需要經常被User存取到，或者是根本User也不需要這些資料了，
此時為了增進系統的效能，應該要著手進行資料搬移或刪除等動作，俗稱HouseKepping的作業。

舉例來說:
1.User有可能不需要經常的存取到上一季的資料，那麼可以寫個排程去定期將上一季的資料，
搬至另一個表格存檔封存，類似E-Mail封存的概念，而原本這張表格只需要保留本季的資料即可。
2.User可能再也不需要存取一年前的舊資料，那麼即可寫個排程去定期刪除超過一年的舊資料。

因此定期查看每張資料表的使用情況就變的重要了，此類查詢SQL指令如下:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    t.modify_date AS ModifyDate,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB    
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, t.modify_date, p.Rows
ORDER BY 
    t.Name

由上圖可看出那些表格擁有比較多筆資料和所佔的容量，以及他的表格中資料最後的修改日期，
那麼就可以先針對最近修改日期而且有數萬筆以上資料的表格，進行舊資料的搬移或刪除。

log檔和tempdb檔案大小一直增加時，該如何縮小?

在管理SQL Server經常會遇到此類的問題，如果這兩類檔案長的太大的話，
也會造成SQL Server緩慢，因此要控管這兩類檔案的大小，首先來針對log檔的縮小進行說明。

因為資料庫運行時會有大量的新增、刪除、修改等交易，導致log檔必須是記錄這些動作，
在log檔持續長大的情況下，此時要縮小log檔有兩種方式:
1.寫個數小時備份log檔的job排程
2.直接下指令DBCC SHRINKDATABASE

如果這樣子都縮小不下來，那麼就必須查看是否有大量交易正在進行中佔住Log檔案。

接下來是如何縮小tempdb檔案大小，因為此類檔案是主要供Store Procedure建立的暫存物件(#,##開頭的)所使用的
，因此要先下CHECKPOINT指令將在記憶體的資料回寫到資料檔裡，接著再下以指令

--釋放佔用的記憶體
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
DBCC FREESESSIONCACHE
GO

要特別注意以上這幾行指令只能在系統的離峰時間進行，
因為有許多SQL指令的執行計劃快取都存在記憶體裡，一旦清空將導致CPU需要重新編譯執行計劃，有可能造成CPU使用率拉高並持續一段時間，進而使系統緩慢。

以上指令都做好後，接著再DBCC SHRINKDATABASE(N'tempdb')即可。

通常會讓tempdb暴增的原因是在Store Procedure中大量使用暫存表格和物件，使用後也沒有立刻刪除(drop)，隨著SP執行次數的增加，暫存物件會持續增加，進而使tempdb檔案愈來愈大。
所以養成良好的撰寫Store Procedure的習慣是很重要的!

使用T-SQL分析IIS Log

相信有不少攻城獅都有管理IIS站台的經驗，有時會遇到IIS突然無法連線等狀況發生，

大概大部份的人會在Command視窗下iisreset /restart指令，讓IIS重啓看看，真的再不行就重開機，

也有人會針對消耗資源多的應用程式集區，在User的離峰時間去設定自動回收重啓。

以上都是治標不治本的方法...

因為系統有了問題要去釐清可能的問題點，而不是妄加猜測是資料庫、應用程式、網路所導致的，

小弟建議可先從IIS本身提供的Log來進行分析，

開啟"Internet Information Services(IIS)管理員"->"記錄"，查看記錄檔所存放的目錄。

點"選取欄位"，可勾選要紀錄的資料欄位

由於IIS Log檔中的資料是很繁雜的，僅用記事本來查看要分析出有用的資訊是有難度的，

因此小弟介紹使用BULK INSERT的功能將IIS Log大量匯入至表格中再來找尋有用的資料，

這樣子會便捷許多。

IIS Log檔的內容如下

首先，先依據Log檔裡資料的標頭來建立張資料表供匯入資料使用

CREATE TABLE IIS_Log(
[date] date null,
....
....
)

接著使用以下script將IIS Log檔匯入剛建的表格裡

BULK INSERT IIS_Log FROM 'C:\inetpub\logs\Logfiles\W3SVC1\u_ex18023.log'
WITH (
        FIELDTERMINATOR = ' ',
        ROWTERMINATOR = '\n'
     }

順利匯入後，即可使用SQL來分析各網頁的歷程紀錄。

通常在網站開發時測不出來的問題，可能在網站上線後一段時間後爆發出來，

此時善用Log檔可以有助於了解整個網站的運作情況以及問題點的釐清。

[SQL Server] Performance DashBoard Report的介紹

今天小弟介紹一個實用的SQL Server管理報表，由於他是透過DMV搜集資料的，

所以不用安裝Reporting Service，

可以到https://www.microsoft.com/en-us/download/details.aspx?id=29063

來下載使用，那麼現在話不多說就先來安裝，

安裝完後接著去安裝的目錄裡(C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard)

開啓setup.sql檔執行安裝sql物件。

接著去新增自訂報表

開啓C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\performance_dashboard_main.rdl
接著就可以看到DashBoard報表的主畫面，其中有藍色的連結點入都是各式不同資訊的報表

例如:當下的查詢指令

各式Wait的累積數量

目前在背景跑的Trace詳細資料

相信有了這些資訊，對於SQL Server調教效能或問題解決時，應該有不錯的益處。

[Windows]如何建立重複使用Hyper-V VM的虛擬硬碟檔

有時侯在建Lab環境時，需要一次建立數台VM Guest，

此時為了避免重複地去安裝數台VM Guest的OS，

因此只要裝好一台VM後，把這個當成基礎的VM虛擬硬碟檔，以後有新VM即可複製使用。

在VM灌好OS後，立即進入命令視窗畫面輸入以下指令

cd c:\Windows\System32\Sysprep
sysprep /generalize /oobe /mode:vm /shutdown

執行完sysprep後將會自動關機，接著至存放磁碟檔案的目錄(ex:d:\VM Files\Win2016_Host01)，

直接複製到另一個地方並改目錄名稱(ex:d:\VM Files\Win2016_Host02)，

接著在建新的VM時把磁碟檔案路徑指到新的目錄即可。

[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系統經常有交易在進行的情況下，反而容易造成鎖定的產生，進而拉長應用程式等待交易的時間!!