TechDays 2010 研討會：DAT302 資料庫管理：善用 SQL Server 2008 R2 工具進行 SQL Server 管理及常用問題排解

本場次將完整介紹如何利用 SQL Server 內建工具：
 Activity Monitor, Data Collector, Utility Control Point, Policy-Based Management, 及 PowerShell 來找出、追蹤 及解決資料庫常見問題並進行問題排解。

專為資料庫管理員設計的主題課程

時間：2010年9月28日 (二) 16:50-18:00
教室：201DEF
地點：台北國際會議中心 (台北市信義區信義路五段一號)

筆者的主題是：DAT302 資料庫管理：善用 SQL Server 2008 R2 工具進行 SQL Server 管理及常用問題排解

---

下載資料

---

Microsoft Tech‧Days 2010
http://www.microsoft.com/taiwan/techdays2010/default.aspx

對啟用「資料壓縮(Data Compression)」的資料表，執行大量匯入、修改作業之建議

「資料壓縮(Data Compression)」包含了：「頁面壓縮(Page Compression)」、「資料列壓縮(Row Compression)」。


您可以針對每個資料分割個別設定資料分割資料表和索引的壓縮。

假若需要對這類壓縮資料表執行大量匯入、修改等作業，經過測試，建議：使用分段式的作法，將可以大幅縮減維護時間。

所謂的分段式，其作法流程如下：
1. 先對資料表執行解壓縮作業。
2. 再執行大量的匯入、修改等作業。
3. 最後，對資料表執行壓縮作業。

方式	耗用時間(秒)
採取分段式作法，對啟用壓縮資料表的執行大量的 UPDATE 作業	4
直接對啟用壓縮資料表的執行大量的 UPDATE 作業	43

相差快10倍的時間。應採用分段式。

---

請參考以下的範例程式碼：

-- 建立範例資料表：DC01 與 DC02
USE tempdb
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DC01]') AND type in (N'U'))
DROP TABLE [dbo].[DC01]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DC02]') AND type in (N'U'))
DROP TABLE [dbo].[DC02]
GO
SELECT  TransactionID+0 TransactionID,ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, 
 TransactionType, Quantity, ActualCost, ModifiedDate
 INTO DC01
FROM AdventureWorks2008.Production.TransactionHistory
GO
--
DECLARE @CNT INT =1
WHILE @CNT<=2
BEGIN
 INSERT DC01
  SELECT  *
  FROM DC01
 
 SET @CNT +=1
END

--
SELECT *
 INTO DC02
FROM DC01

--02 查詢範例資料表的使用空間(初估)資訊：DC01 與 DC02
SELECT a3.name AS N'結構描述', a2.name AS N'資料表', a1.rows AS N'資料列筆數', 
 (a1.reserved + ISNULL(a4.reserved,0))* 8 AS N'已保留(KB)', a1.data * 8 AS '資料使用空間(KB)',
 (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS N'索引(KB)',
 (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS N'未使用(KB)'
FROM (
 SELECT ps.object_id, SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows],
  SUM (ps.reserved_page_count) AS reserved,
  SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
   ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data,
  SUM (ps.used_page_count) AS used
 FROM sys.dm_db_partition_stats ps
 GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN 
 (SELECT it.parent_id, SUM(ps.reserved_page_count) AS reserved, SUM(ps.used_page_count) AS used
  FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
  WHERE it.internal_type IN (202,204)
  GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY 3 DESC
GO

請參考下圖所示：

--03 啟用壓縮：頁面壓縮(PAGE compression)：DC01 與 DC02
ALTER TABLE DC01 
 REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO
ALTER TABLE DC02
 REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO

--04 查詢資料表的壓縮狀態
/*
data_compression 資料行，表示每個資料分割的壓縮狀態：
0 = NONE
1 = ROW
2 = PAGE
*/
SELECT OBJECT_NAME(object_id) N'資料表', MAX(rows) N'資料列筆數', MAX(data_compression) N'壓縮狀態',
 MAX(data_compression_desc) N'每個資料分割的壓縮狀態'
FROM sys.partitions
WHERE object_id>100
GROUP BY OBJECT_NAME(object_id)
ORDER BY 3 DESC,2 DESC

請參考下圖所示：

--05 查詢範例資料表的使用空間(初估)資訊：DC01 與 DC02
SELECT a3.name AS N'結構描述', a2.name AS N'資料表', a1.rows AS N'資料列筆數', 
 (a1.reserved + ISNULL(a4.reserved,0))* 8 AS N'已保留(KB)', a1.data * 8 AS '資料使用空間(KB)',
 (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS N'索引(KB)',
 (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS N'未使用(KB)'
FROM (
 SELECT ps.object_id, SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows],
  SUM (ps.reserved_page_count) AS reserved,
  SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
   ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data,
  SUM (ps.used_page_count) AS used
 FROM sys.dm_db_partition_stats ps
 GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN 
 (SELECT it.parent_id, SUM(ps.reserved_page_count) AS reserved, SUM(ps.used_page_count) AS used
  FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
  WHERE it.internal_type IN (202,204)
  GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY 3 DESC
GO

請參考下圖所示：

---

EX1 直接對啟用壓縮資料表的執行大量的 UPDATE 作業：DC01

--EX1 直接對啟用壓縮資料表的執行大量的 UPDATE 作業：DC01
--01
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS 
SET STATISTICS TIME ON
GO

UPDATE DC01
SET TransactionType='A'
GO

/*
SQL Server parse and compile time: 
   CPU time = 16 ms, elapsed time = 25 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 37206 ms,  elapsed time = 43283 ms.
*/

請參考下圖所示：

SQL Server 2008 支援針對資料表、索引和索引檢視表，以資料列和頁面格式進行磁碟內存儲存壓縮。

可以觀察到，直接對啟用壓縮資料表的執行大量的 UPDATE 作業，耗用了 43 多秒的時間。

---

EX2. 採取分段式作法，對啟用壓縮資料表的執行大量的 UPDATE 作業：DC02

--EX2. 採取分段式作法，對啟用壓縮資料表的執行大量的 UPDATE 作業：DC02
/*
1. 先對資料表 DC02 執行解壓縮作業。
2. 再執行大量的匯入、修改等作業。
3. 最後，對資料表 DC02 執行壓縮作業。
*/
--01 執行解壓縮作業：DC02
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS 
SET STATISTICS TIME ON
GO
ALTER TABLE DC02 
 REBUILD
WITH (DATA_COMPRESSION = NONE);
GO

--02 執行大量的 UPDATE 作業：DC02
UPDATE DC02
SET TransactionType='A'

--03 執行壓縮作業：DC02
ALTER TABLE DC02 
 REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO

/*
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 15 ms.

 SQL Server Execution Times:
   CPU time = 1046 ms,  elapsed time = 924 ms.

 SQL Server Execution Times:
   CPU time = 1046 ms,  elapsed time = 992 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 952 ms,  elapsed time = 1178 ms.

(453772 個資料列受到影響)
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.

 SQL Server Execution Times:
   CPU time = 4555 ms,  elapsed time = 2391 ms.

 SQL Server Execution Times:
   CPU time = 4555 ms,  elapsed time = 2395 ms.

*/

請參考下圖所示：

可以觀察到，採取分段式作法，對啟用壓縮資料表的執行大量的 UPDATE 作業，耗用約 4 秒多的時間。

---

參考資料：
建立壓縮資料表及索引
http://msdn.microsoft.com/zh-tw/library/cc280449.aspx

Creating Compressed Tables and Indexes
http://msdn.microsoft.com/en-us/library/cc280449.aspx

停用(Disable)與啟用(Enable)「觸發程序(Trigger)」

「觸發程序(Trigger)」是一種在資料庫伺服器發生事件時，會自動執行的特殊預存程序。

「觸發程序」在 SQL Server 2005 版本上，可以分成為：
DDL 「觸發程序」。
DML 「觸發程序」。

其中，DML 「觸發程序」又可以在細分為 After 與 Instead of 「觸發程序」。


依預設，在建立觸發程序時，它是在啟用狀態。

當使用者試圖透過資料操作語言 (DML) 事件來修改資料時，便會執行 DML 觸發程序。
DML 事件包括資料表或檢視的 INSERT、UPDATE 或 DELETE 陳述式。


DDL 觸發程序則是為了回應各種資料定義語言 (DDL) 事件而執行的。
這些事件主要是對應到 Transact-SQL CREATE、ALTER 和 DROP 陳述式，以及執行類似 DDL 作業的特定系統預存程序。


登入觸發程序會引發來回應使用者工作階段建立時所引發的 LOGON 事件。

---

有時會有需求要停用或是再度啟用「觸發程序」，可以使用 DISABLE TRIGGER 與 ENABLE TRIGGER 的 T-SQL 陳述式來完成。

請參考以下的範例程式碼：
以伺服器層級為例：

-- EX1. DDL 「觸發程序」：伺服器層級
--01 建立伺服器層級的 DDL 觸發程序
USE master
GO
IF  EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'trSrv01')
DROP TRIGGER [trSrv01] ON ALL SERVER
GO
CREATE TRIGGER trSrv01
 ON ALL SERVER
 FOR DDL_LOGIN_EVENTS
AS
 SELECT N' 執行伺服器層級的 DDL 觸發程序'
GO

--02 查詢伺服器層級的 DDL 觸發程序的相關狀態
SELECT st.name N'觸發程序',  ste.type_desc N'事件描述', ste.event_group_type_desc N'事件群組的描述',  st.type_desc N'物件類型', st.is_disabled N'是否已停用', st.create_date N'建立日期', st.modify_date N'修改日期'
FROM sys.server_triggers st INNER JOIN sys.server_trigger_events ste
 ON st.object_id=ste.object_id

--03 停用此伺服器層級的 DDL 觸發程序
DISABLE TRIGGER trSrv01
ON ALL SERVER
GO

--04 啟用此伺服器層級的 DDL 觸發程序
ENABLE TRIGGER trSrv01
ON ALL SERVER
GO

--05 刪除此伺服器層級的 DDL 觸發程序
IF  EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'trSrv01')
DROP TRIGGER [trSrv01] ON ALL SERVER
GO

---

以資料庫層級為例：

-- EX2. DDL 「觸發程序」：資料庫層級
--01 建立資料庫層級的 DDL 觸發程序

USE Northwind -- 先切換到指定的資料庫
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'trDB01')DROP TRIGGER [trDB01] ON DATABASE
GO
CREATE TRIGGER trDB01
 ON DATABASE
 FOR CREATE_TABLE
AS
  SELECT N' 執行資料庫層級的 DDL 觸發程序'
GO

--02 查詢資料庫層級的 DDL 觸發程序的相關狀態
SELECT st.name N'觸發程序',  ste.type_desc N'事件描述', ste.event_group_type_desc N'事件群組的描述', 
 st.type_desc N'物件類型', st.is_disabled N'是否已停用', st.create_date N'建立日期', st.modify_date N'修改日期'
FROM sys.triggers st INNER JOIN sys.trigger_events ste
 ON st.object_id=ste.object_id

--03 停用此資料庫層級的 DDL 觸發程序
DISABLE TRIGGER trDB01
ON DATABASE
GO

--04 啟用此資料庫層級的 DDL 觸發程序
ENABLE TRIGGER trDB01
ON DATABASE
GO

--05 刪除此資料庫層級的 DDL 觸發程序
IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'trDB01')DROP TRIGGER [trDB01] ON DATABASE
GO

---

以 DML 觸發程序為例：

-- EX3. DML 「觸發程序」
--01 建立 DML 「觸發程序」
USE Northwind
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trOrders01]'))
DROP TRIGGER [dbo].[trOrders01]
GO
CREATE TRIGGER trOrders01
 ON Orders
 AFTER INSERT
AS
  SELECT N' 執行DML 觸發程序'
GO

--02 查詢 DML 觸發程序的相關狀態
SELECT OBJECT_NAME(t.parent_id) N'資料表', t.name N'觸發程序名稱', parent_class_desc N'觸發程序父類別的描述', 
 t.type_desc N'物件類型的描述',  tEV.type_desc '引發觸發程序的每個事件', is_instead_of_trigger N'是否為 INSTEAD OF 觸發程序',
 t.is_disabled  N'是否已停用'
FROM sys.triggers t INNER JOIN sys.trigger_events tEV
ON t.object_id = tEV.object_id

--03 停用此DML 觸發程序
DISABLE TRIGGER trOrders01
 ON Orders
GO

--04 啟用此資料庫層級的 DDL 觸發程序
ENABLE TRIGGER trOrders01
 ON Orders
GO

--05 刪除此資料庫層級的 DDL 觸發程序
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trOrders01]'))
DROP TRIGGER [dbo].[trOrders01]
GO

---

參考資料：
ENABLE TRIGGER (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms182706.aspx

DISABLE TRIGGER (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms189748.aspx

CREATE TRIGGER (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms189799.aspx

查詢「作業(Job)」的執行記錄：使用「記錄檔檢視器」、預存程序 sp_help_jobhistory

適用版本：SQL Server 2005 與 2008

若要想知道「作業(Job)」執行完成後的紀錄，可以使用「記錄檔檢視器」，請參考下圖所示：

---

範例1：使用預存程序 sp_help_jobhistory 來查詢「作業(Job)」執行完成後的紀錄

--EX1. 以下是使用預存程序 sp_help_jobhistory 來查詢「作業(Job)」執行完成後的紀錄
-- 篩選一個月內的作業執行歷程紀錄

declare @tmp_sp_help_jobhistory table
(
 instance_id int null, job_id uniqueidentifier null, job_name sysname null, step_id int null, step_name sysname null, sql_message_id int null, 
 sql_severity int null, message nvarchar(4000) null, run_status int null, run_date int null, run_time int null, run_duration int null, 
 operator_emailed sysname null, operator_netsent sysname null, operator_paged sysname null, retries_attempted int null, server sysname null )

insert into @tmp_sp_help_jobhistory 
exec msdb.dbo.sp_help_jobhistory @mode='FULL';

WITH CTE_jobhistory
AS (
SELECT tshj.instance_id AS [InstanceID], tshj.sql_message_id AS [SqlMessageID], tshj.message AS [Message],tshj.step_id AS [StepID],
 tshj.step_name AS [StepName], tshj.sql_severity AS [SqlSeverity], tshj.job_id AS [JobID], tshj.job_name AS [JobName], tshj.run_status AS [RunStatus],
 CASE tshj.run_date WHEN 0 THEN NULL ELSE
  convert(datetime, stuff(stuff(cast(tshj.run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' + 
  stuff(stuff(substring(cast(1000000 + tshj.run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'), 120) 
 END AS [RunDate],
 tshj.run_duration AS [RunDuration], tshj.operator_emailed AS [OperatorEmailed], tshj.operator_netsent AS [OperatorNetsent],
 tshj.operator_paged AS [OperatorPaged], tshj.retries_attempted AS [RetriesAttempted], tshj.server AS [Server], getdate() as [CurrentDate]
 FROM @tmp_sp_help_jobhistory as tshj)
 
SELECT ROW_NUMBER()OVER (ORDER BY InstanceID) 'RowNum' , JobName N'作業名稱', 
 CASE RunStatus 
  WHEN 0 THEN N'失敗'
  WHEN 1 THEN N'成功'
  WHEN 3 THEN N'取消'
  WHEN 4 THEN N'進行中'
  WHEN 5 THEN N'未知'
 END N'執行結果', 
 Message N'記錄訊息', RunDate N'啟動日期與時間', RunDuration N'經歷時間(秒)', OperatorEmailed N'已傳送電子郵件通知',SqlSeverity N'SQL嚴重性層級'
FROM CTE_jobhistory
WHERE StepID =1 AND RunDate >= dateadd(MM,-1,getdate()) -- 篩選一個月內的作業執行歷程紀錄
ORDER BY RunDate DESC
GO

請參考下圖所示：

---

範例2：檢視全部「作業」內的各個「步驟」之詳細紀錄

--EX2. 檢視全部「作業」內的各個「步驟」之詳細紀錄
-- 依據記錄的日期作為排序
SELECT ROW_NUMBER()OVER (ORDER BY his.run_date, his.run_time) 'RowNum',
 jb.name N'作業', his.step_name N'步驟', 
 CASE his.run_status 
  WHEN 0 THEN N'失敗'
  WHEN 1 THEN N'成功'
  WHEN 3 THEN N'取消'
  WHEN 4 THEN N'進行中'
  WHEN 5 THEN N'未知'
 END N'執行結果', 
 his.message N'訊息',
 CASE his.run_date WHEN 0 THEN NULL ELSE
  convert(datetime, stuff(stuff(cast(his.run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' + 
  stuff(stuff(substring(cast(1000000 + his.run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'), 120) 
 END AS N'啟動日期',
 his.run_duration N'經歷時間(秒)',  his.operator_id_emailed N'已傳送電子郵件通知', his.sql_message_id N'錯誤訊息識別碼',
 his.sql_severity N'SQL嚴重性層級'
FROM msdb.dbo.sysjobs jb INNER JOIN msdb.dbo.sysjobhistory his
 ON jb.job_id = his.job_id
GO

請參考下圖所示：

---

這裡要提醒您的是：


依據預設值，「作業記錄大小上限」是：1,000 筆資料列。
每項作業的作業記錄最大資料列數是：100 筆資料列。


請參考下圖所示：

---

權限
依預設，只有系統管理員 (sysadmin) 固定伺服器角色的成員，才能夠執行這個預存程序。

其他使用者必須被授與 msdb 資料庫的下列其中一個 SQL Server Agent 固定資料庫角色。
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole

如需有關這些角色權限的詳細資料，請參閱＜SQL Server Agent 固定資料庫角色＞。

---

參考資料：
sp_help_jobhistory (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms188025.aspx

SQL Server Agent 屬性 (記錄頁面)
http://technet.microsoft.com/zh-tw/library/ms189683.aspx

SQL Server Agent 固定資料庫角色
http://msdn.microsoft.com/zh-tw/library/ms188283.aspx