德瑞克：SQL Server 學習筆記: 2010/02

2010-02-22

淺談遺漏索引(missing index)

在SQL Server 2005 與 2008 版本上，提供了「遺漏索引(missing index)」。

遺漏索引功能是一種可隨時在資料庫資料表和索引檢視上識別遺漏索引的輕量型方法

查詢最佳化工具產生查詢計畫時，它會分析特定篩選條件的最佳索引。
如果最佳索引不存在，則查詢最佳化工具會產生次佳的查詢計畫，但仍會儲存這些索引的相關資訊。

遺漏索引功能可讓您存取這些索引的資訊，讓您決定是否應該實作它們。

預設會開啟遺漏索引功能。未提供任何控制項來開啟或關閉這個功能，或重設在查詢動態管理物件時所傳回的任何資料表。
重新啟動 SQL Server 時，會卸除所有遺漏的索引資訊。

只有在搭配使用 -x 引數與 sqlservr 命令提示字元公用程式以啟動 SQL Server 執行個體時，才可停用這個功能。

遺漏索引功能的元件會在其輸出中列出相等、不相等和內含資料行。

例如，XML 執行程序表 MissingIndexes 元素指出是否要在 Transact-SQL 陳述式述詞中使用索引鍵資料行代表相等 (=) 或不相等 (<、> 等等)，或是只包含該索引鍵資料行來涵蓋查詢。
它會將這項資訊顯示成 ColumnGroup 子元素之 Usage 屬性的下列其中一個值：
1. usage="EQUALITY"
2. usage="INEQUALITY"
3. usage="INCLUDE"

請遵照下列方針，在利用遺漏索引功能元件輸出所撰寫的 CREATE INDEX 陳述式中排序資料行：
1. 將相等資料行列在最前面 (資料行清單的最左邊)。
2. 將不相等資料行列在相等資料行後面 (列出的相等資料行右邊)。
3. 將內含資料行列在 CREATE INDEX 陳述式的 INCLUDE 子句中。
4. 若要決定相等資料行的有效順序，請依據其選擇性排列這些資料行；也就是將選擇性最高的資料行列在最前面。

遺漏索引功能具有下列限制：
1. 不能用來微調索引組態。
2. 不能收集超過 500 個遺漏索引群組的統計資料。
3. 不能指定索引中使用的資料行順序。
4. 對於只涉及不相等述詞的查詢，傳回的成本資訊比較不精確。
5. 僅報告某些查詢的內含資料行，因此必須手動選取索引鍵資料行。
6. 只會傳回有關可能遺漏索引之資料行的原始資訊。
7. 它不建議已篩選的索引。
8. XML 執行程序表中重複出現的同一個遺漏索引群組，可能會傳回不同的成本。
9. 它不會考慮一般查詢計畫。

遺漏索引功能是一種輕量型工具，用來尋找可大幅提升查詢效能的遺漏索引。
它並未提供足夠的資訊讓您微調索引組態。請使用 Database Engine Tuning Advisor 來達成此目的。

Database Engine Tuning Advisor 是獨立的工具，用以分析整個 SQL Server 工作負載，以及產生實體設計結構組態的建議 (例如索引、索引檢視或資料分割)。
以下提供 Database Engine Tuning Advisor 與遺漏索引功能的詳細比較。

遺漏索引是輕量型伺服器端一律啟動功能，用於捕捉和更正錯誤。
相反地，Database Engine Tuning Advisor 是完整工具，用以存取實體資料庫設計，以及建議新的實體設計結構以改進效能。
Database Engine Tuning Advisor 不僅會考慮索引，也會考慮是否應該使用索引檢視或資料分割來改進查詢效能。

下表詳細比較遺漏索引功能和 Database Engine Tuning Advisor：

如果實作識別的遺漏索引，可能會改進查詢效能。
您可使用 Database Engine Tuning Advisor 使用者指定的組態功能和評估模式，來決定實作遺漏索引對整個工作負載的影響。

即使對於單一查詢工作負載，Database Engine Tuning Advisor 和遺漏索引功能都可傳回不同的建議。
因為遺漏索引功能建議之索引的索引鍵資料行不區分順序，所以會發生這種情況。

另一方面，Database Engine Tuning Advisor 建議包含索引之索引鍵資料行的順序，以最佳化查詢效能。

摘要
遺漏索引功能可用來捕捉和更正索引錯誤，而 Database Engine Tuning Advisor 可用來更正索引錯誤以及完整微調伺服器上的工作負載。
您可使用遺漏索引功能來產生候選索引，但應使用 Database Engine Tuning Advisor 來驗證它們。

sys.dm_db_missing_index_columns 動態管理函數
傳回有關遺漏索引之資料庫表格資料行的資訊。
sys.dm_db_missing_index_columns 是動態管理函數。

sys.dm_db_missing_index_columns 傳回的資訊會在查詢最佳化工具最佳化查詢時更新，而不會一直保存。
遺漏索引資訊只會保留到 SQL Server 重新啟動為止。
如果資料庫管理員想要在伺服器回收之後保留遺漏索引資訊，應該定期製作該項資訊的備份副本。

關於資料行：column_usage 之值的說明
EQUALITY 作為表示相等之述詞的資料行，格式如下：
table.column = constant_value

INEQUALITY 作為表示不相等之述詞的資料行，例如，格式如下的述詞：
table.column > constant_value
"=" 以外的其他任何比較運算子都可表示不相等。

INCLUDE
資料行不是用來評估述詞，而用於其他原因，例如用來涵蓋查詢。

sys.dm_db_missing_index_columns 傳回的資訊會在查詢最佳化工具最佳化查詢時更新，而不會一直保存。
遺漏索引資訊只會保留到 SQL Server 重新啟動為止。

如果資料庫管理員想要在伺服器回收之後保留遺漏索引資訊，應該定期製作該項資訊的備份副本。

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

-- 建立資料表：tindx1
USE Northwind
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tindx1]') AND type in (N'U'))
DROP TABLE [dbo].[tindx1]
GO
CREATE TABLE tindx1
(sid INT identity , cDateTime datetime,cData char(8000))
GO
-- 新增資料列：5 萬筆  
SET NOCOUNT ON 
GO  
DECLARE @cnt INT=1  
WHILE @cnt<=50000 
BEGIN  
INSERT tindx1 VALUES( GETDATE() ,GETDATE())  
SET @cnt+=1 
END
GO
/*=========================================================*/
--01 檢視資料表：tindx1 的索引類型  
SELECT t.name N'資料表', i.name N'索引名稱', i.type_desc N'索引類型的描述'  
FROM sys.tables t INNER JOIN sys.indexes i  
ON t.object_id = i.object_id  
WHERE t.name = 'tindx1'
GO
EXEC sp_helpindex 'tindx1'
GO
--02 執行一般查詢時，沒有最佳可用的索引
SELECT cDateTime,cData
FROM tindx1
WHERE sid=1

/*=========================================================*/
--03 使用動態管理函數來分析：「遺漏索引(missing index)」
SELECT *
FROM sys.dm_db_missing_index_details

--
SELECT mig.*, statement N'資料表',
column_id, column_name N'資料行', column_usage N'查詢使用資料行的方式',included_columns N'內含資料行(Inclueded Columns)'
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id;

顯示，建議：在資料行：sid 上建立索引。

--04 建立索引，例如：
CREATE CLUSTERED INDEX [_dta_index_tindx1_c_8_2098106515__K1] ON [dbo].[tindx1]
(
[sid] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO
--04 再度執行一般查詢時，已經有最佳可用的索引
SELECT cDateTime,cData
FROM tindx1
WHERE sid=1
--05 再度使用動態管理函數來分析：「遺漏索引(missing index)」，已經沒有「遺漏索引(missing index)」。
SELECT *
FROM sys.dm_db_missing_index_details
--
SELECT mig.*, statement N'資料表',
column_id, column_name N'資料行', column_usage N'查詢使用資料行的方式',included_columns N'內含資料行(Inclueded Columns)'
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id;

若要覺得寫出建立索引的語法覺得麻煩的話，可以使用 Database Engine Tuning Advisor 或是利用 SSMS 來建立索引。

參考資料：

使用 SSMS 管理工具的「圖形執行計畫」來檢視「遺漏索引(missing index)」，以及產生建立合適的索引之範例程式碼
http://sharedderrick.blogspot.com/2011/06/ssms-missing-index.html

尋找遺漏索引
http://technet.microsoft.com/zh-tw/library/ms345417.aspx

使用遺漏索引功能的限制
http://technet.microsoft.com/zh-tw/library/ms345485.aspx

相關的查詢微調功能
http://technet.microsoft.com/zh-tw/library/ms345577.aspx

sys.dm_db_missing_index_columns (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms345364.aspx

sys.dm_db_missing_index_groups (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms345407.aspx

2010-02-19

認識 sys.dm_db_index_usage_stats 目錄檢視，查詢與索引作業相關的計數器資料

sys.dm_db_index_usage_stats 目錄檢視
傳回不同類型索引作業的計數，以及每種類型作業上次執行的時間。

在指定索引上由一項查詢執行所進行的每個個別搜尋、掃描、查閱或更新，在這份檢視都被當作使用一次該索引，並且累加對應的計數器。

它會針對由使用者提交之查詢所導致的作業，以及由內部產生之查詢所導致的作業 (例如，掃描以收集統計資料)，來報告資訊。

user_updates 計數器會指出索引上由基礎資料表或檢視的插入、更新或刪除作業所導致的維護層級。
您可以利用這份檢視，來判斷應用程式根本很少用到的索引。

您也可以利用這份檢視，來判斷哪些索引會產生維護負擔。您可能會考慮卸除產生維護負擔，但不用於查詢，或者不常用於查詢的索引。

只要一啟動 SQL Server (MSSQLSERVER) 服務，計數器就會初始化為空值。
此外，每當卸離或關閉資料庫時 (例如，因為 AUTO_CLOSE 設為 ON)，就會移除所有與資料庫關聯的資料列。

使用索引時，如果索引的資料列不存在，則會將資料列加入 sys.dm_db_index_usage_stats 中。
加入資料列後，其計數器的初始設定為零。

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

USE Northwind
GO

--01 檢視資料表 Orders 的索引類型
SELECT t.name N'資料表', i.name N'索引名稱', i.type_desc N'索引類型的描述'
FROM sys.tables t INNER JOIN sys.indexes i
ON t.object_id = i.object_id
WHERE t.name = 'Orders'

--02 叢集索引掃描(Clustered Index Scan)
SELECT * FROM Northwind.dbo.Orders

-- 觀察：last_user_seek、last_user_scan、last_user_lookup 等資料行
SELECT db_name(database_id) N'資料庫名稱',object_name(dm.object_id) N'資料表或檢視', inx.name N'索引',inx.type_desc N'索引類型的描述', dm.*
FROM sys.dm_db_index_usage_stats dm INNER JOIN sys.indexes inx
ON dm.object_id = inx.object_id AND dm.index_id = inx.index_id
WHERE database_id=db_id('Northwind') and object_name(dm.object_id)='Orders'

--03 叢集索引搜尋(Clustered Index Seek)
SELECT * FROM Northwind.dbo.Orders
WHERE OrderID =10248

-- 觀察：last_user_seek、last_user_scan、last_user_lookup 等資料行
SELECT db_name(database_id) N'資料庫名稱',object_name(dm.object_id) N'資料表或檢視', inx.name N'索引',inx.type_desc N'索引類型的描述', dm.*
FROM sys.dm_db_index_usage_stats dm INNER JOIN sys.indexes inx
ON dm.object_id = inx.object_id AND dm.index_id = inx.index_id
WHERE database_id=db_id('Northwind') and object_name(dm.object_id)='Orders'

--04 索引搜尋(NonClustered Index Seek) + 索引鍵查詢(Clustered Index Lookup)
SELECT * FROM Northwind.dbo.Orders
WHERE CustomerID='VINET'

-- 觀察：last_user_seek、last_user_scan、last_user_lookup 等資料行
SELECT db_name(database_id) N'資料庫名稱',object_name(dm.object_id) N'資料表或檢視', inx.name N'索引',inx.type_desc N'索引類型的描述', dm.*
FROM sys.dm_db_index_usage_stats dm INNER JOIN sys.indexes inx
ON dm.object_id = inx.object_id AND dm.index_id = inx.index_id
WHERE database_id=db_id('Northwind') and object_name(dm.object_id)='Orders'

--05 重建資料表 Orders 上的索引 PK_Orders
ALTER INDEX [PK_Orders] ON [dbo].[Orders]
REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO

-- 觀察：last_system_scan 等資料行
SELECT db_name(database_id) N'資料庫名稱',object_name(dm.object_id) N'資料表或檢視', inx.name N'索引',inx.type_desc N'索引類型的描述', dm.*
FROM sys.dm_db_index_usage_stats dm INNER JOIN sys.indexes inx
ON dm.object_id = inx.object_id AND dm.index_id = inx.index_id
WHERE database_id=db_id('Northwind') and object_name(dm.object_id)='Orders'

/*=========================================================*
-- 初始化計數器 sys.dm_db_index_usage_stats 為空值
/*
只要一啟動 SQL Server (MSSQLSERVER) 服務，計數器就會初始化為空值。
此外，每當卸離或關閉資料庫時 (例如，因為 AUTO_CLOSE 設為 ON)，就會移除所有與資料庫關聯的資料列。
*/
USE [master]
GO
ALTER DATABASE [Northwind]
SET AUTO_CLOSE ON WITH NO_WAIT
GO
WAITFOR DELAY '00:00:10';
GO
ALTER DATABASE [Northwind]
SET AUTO_CLOSE OFF WITH NO_WAIT
GO
USE [master]
GO

/*=========================================================*/
-- 查詢每一個資料庫與索引作業相關的計數器資料
SELECT db_name(database_id) N'資料庫', *
FROM sys.dm_db_index_usage_stats
ORDER BY 1

參考資料：
sys.dm_db_index_usage_stats (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms188755.aspx

2010-02-15

交易記錄檔(transaction log)意外損毀，僅剩下資料檔案可用為例。錯誤訊息：訊息 1813，層級 16，狀態 2 ... 檔案啟動錯誤。實體檔案名稱 "*.ldf" 可能不正確。

這裡所指的意外中斷服務情境，例如：
1.系統意外、不正常中斷服務。
2.磁碟損毀。
3.交易記錄檔案(*.ldf)損毀。
等...

發生上述的情境時，若仍是採取一般方式來附加資料庫時，但因為資料庫未正常關閉，導致交易記錄檔案毀損時，將遇到以下的錯誤訊息：

檔案啟動錯誤。實體檔案名稱 "C:\myAdmin\DB\DBLog_log.ldf" 可能不正確。
無法重建記錄，因為關閉資料庫時仍有開啟的交易/使用者、資料庫未發生檢查點，或資料庫是唯讀的。如果手動刪除交易記錄檔，或因硬體或環境失敗而遺失交易記錄檔，就可能發生這種錯誤。
訊息 1813，層級 16，狀態 2，行 1
無法開啟新資料庫 'DBLog'。CREATE DATABASE 已經中止。

情境：
模擬系統損毀，僅剩下資料檔案，沒有交易記錄檔案(*.ldf)。

可能的搶救方式：

適用版本：
SQL Server 2005 與 2008

--步驟01. 請先依據此資料庫的檔案結構，重新建立相同結構、名稱的資料庫。
--步驟02. 正常離線此資料庫，例如執行：
USE master
GO
ALTER DATABASE [資料庫名稱]
SET OFFLINE;
GO

/*=========================================================*/
--步驟03. 將先前的資料檔案覆蓋掉此新建立的資料庫檔案。
--步驟04. 若嘗試執行重新上線此資料庫，例如：
USE master
GO
ALTER DATABASE [資料庫名稱]
SET ONLINE;
GO

/* 遇到的錯誤訊息：
訊息 5173，層級 16，狀態 1，行 1
一或多個檔案與資料庫的主要檔案不符。如果您嘗試附加資料庫，請以正確檔案重試作業。如果這是現有的資料庫，則檔案可能已損毀，應該從備份還原。
記錄檔 'C:\myAdmin\DB\DBLog_log.ldf' 與主要檔案不符。可能是因為來自於不同的資料庫，或是記錄檔先前已經重建過。

訊息 945，層級 14，狀態 2，行 1
檔案無法存取、記憶體或磁碟空間不足，因此無法開啟資料庫 'DBLog'。詳細資訊請參閱 SQL Server 錯誤記錄檔。

訊息 5069，層級 16，狀態 1，行 1
ALTER DATABASE 陳述式失敗。
*/

--步驟05. 查詢資料庫的狀態為：RECOVERY_PENDING 與 MULTI_USER
/*
RECOVERY PENDING 資料庫狀態
SQL Server 在復原期間發生資源相關的錯誤。
資料庫並未損毀，但是檔案有可能遺失或系統資源限制有可能造成它無法啟動。
資料庫是無法使用的。需要使用者執行其他動作以解決錯誤並讓復原處理得以完成。

MULTI_USER
當指定 MULTI_USER 時，可讓所有具有適當權限的使用者都連接到允許的資料庫。
*/
SELECT name N'資料庫', state_desc N'資料庫狀態', user_access_desc N'使用者存取設定'
FROM sys.databases
GO

/*=========================================================*/
--步驟06. 先設定此資料庫的狀態為：SINGLE_USER
/*
SINGLE_USER 資料庫狀態
指定每次只能有一位使用者存取資料庫。
當指定 SINGLE_USER 時，每次只有一個使用者可以連接到資料庫。所有其他的使用者連接都會中斷。

如果指定了 SINGLE_USER，且沒有其他使用者連接到資料庫，就會封鎖 ALTER DATABASE 陳述式，直到所有使用者都中斷連接指定的資料庫為止。
若要覆寫這個行為，請參閱 WITH  子句。

資料庫會保留在 SINGLE_USER 模式中，即使是將選項記錄設為關閉的使用者也是如此。此時其他使用者可以連接到這個資料庫，但只能有一位。

將資料庫設為 SINGLE_USER 之前，請先確定 AUTO_UPDATE_STATISTICS_ASYNC 選項是否設為 OFF。
當設為 ON 時，更新統計資料的背景執行緒會取得資料庫連接，而您就無法以單一使用者模式存取資料庫。
若要檢視這個選項的狀態，請查詢 sys.databases 目錄檢視中的 is_auto_update_stats_async_on 資料行。

使用終止選項 WITH ROLLBACK IMMEDIATE。
所有未完成的交易都會回復，而且資料庫的任何其他連接都會立即中斷。
*/
ALTER DATABASE [資料庫名稱]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

/* 遇到的錯誤訊息：
訊息 5173，層級 16，狀態 1，行 1
一或多個檔案與資料庫的主要檔案不符。如果您嘗試附加資料庫，請以正確檔案重試作業。如果這是現有的資料庫，則檔案可能已損毀，應該從備份還原。
記錄檔 'C:\myAdmin\DB\DBLog_log.ldf' 與主要檔案不符。可能是因為來自於不同的資料庫，或是記錄檔先前已經重建過。
訊息 945，層級 14，狀態 2，行 1
檔案無法存取、記憶體或磁碟空間不足，因此無法開啟資料庫 'DBLog'。詳細資訊請參閱 SQL Server 錯誤記錄檔。
訊息 5069，層級 16，狀態 1，行 1
ALTER DATABASE 陳述式失敗。
*/
-- 查詢資料庫屬性已經變更為：SINGLE_USER
SELECT name N'資料庫', state_desc N'資料庫狀態', user_access_desc N'使用者存取設定'
FROM sys.databases
GO

/*=========================================================*/
--步驟07. 設定資料庫狀態為：EMERGENCY
/*
EMERGENCY  資料庫狀態
使用者已變更資料庫並將狀態設為 EMERGENCY。
資料庫是在單一使用者模式下，而且可以進行修復或還原。

資料庫是標示為 READ_ONLY、記錄已停用並限定只有系統管理員 (sysadmin) 固定伺服器角色的成員才可存取。
EMERGENCY 主要是做為疑難排解的用途。例如，標示為有疑問的資料庫可以設為 EMERGENCY 狀態。

在這個情況下，系統管理員可以進行資料庫的唯讀存取。
只有系統管理員 (sysadmin) 固定伺服器角色的成員，可以將 資料庫設定為 EMERGENCY 狀態。
*/
ALTER DATABASE  [資料庫名稱]
SET EMERGENCY;
GO

-- 查詢資料庫狀態已經變更為：EMERGENCY 與 SINGLE_USER
SELECT name N'資料庫', state_desc N'資料庫狀態', user_access_desc N'使用者存取設定'
FROM sys.databases
GO

/*=========================================================*/
--步驟08. 執行 DBCC CHECKDB 搭配使用 REPAIR_ALLOW_DATA_LOSS 參數。
/*
REPAIR_ALLOW_DATA_LOSS 參數
指定的資料庫必須為單一使用者模式，才能使用。
嘗試修復所有報告的錯誤。這些修復可能會造成某些資料的遺失。

以資料庫緊急模式解決錯誤
如果已經利用 ALTER DATABASE 陳述式將資料庫設為緊急模式，則 DBCC CHECKDB 可以在資料庫上執行某些特殊的修復作業 (如果已指定 REPAIR_ALLOW_DATA_LOSS 選項)。

這些修復可讓一般無法修復的資料庫以實體一致的狀態重新上線。
只有在資料庫無法從備份還原時，才應該使用上述修復來當做最後手段。
*/
DBCC CHECKDB ( [資料庫名稱] , REPAIR_ALLOW_DATA_LOSS) ;
GO

/* 顯示的訊息：
訊息 5173，層級 16，狀態 1，行 1
一或多個檔案與資料庫的主要檔案不符。如果您嘗試附加資料庫，請以正確檔案重試作業。如果這是現有的資料庫，則檔案可能已損毀，應該從備份還原。
記錄檔 'C:\myAdmin\DB\DBLog_log.ldf' 與主要檔案不符。可能是因為來自於不同的資料庫，或是記錄檔先前已經重建過。

警告: 資料庫 'DBLog' 的記錄已經重建。已遺失交易一致性。RESTORE 鏈已中斷，伺服器已經沒有先前記錄檔的內容，因此您必須知道這些內容。
您應該執行 DBCC CHECKDB 來驗證實體一致性。資料庫已進入僅限 DBO 模式。您準備好讓資料庫能供使用後，必須重設資料庫選項，並刪除任何額外的記錄檔。

DBCC 對 'DBLog' 的結果。
...
*/

--步驟09. 正確依序完成步驟08 後，交易記錄檔案已經重新建立完成。

/*=========================================================*/
--步驟10. 查詢資料庫狀態已經變更為：ONLINE 與 SINGLE_USER。
SELECT name N'資料庫', state_desc N'資料庫狀態', user_access_desc N'使用者存取設定'
FROM sys.databases
GO

--步驟11. 再度執行 DBCC CHECKDB 檢查指定資料庫中所有物件的邏輯完整性和實體完整性
use master
GO
DBCC CHECKDB( [資料庫名稱])
GO

/*=========================================================*/
--步驟12. 設定此資料庫的狀態為：MULTI_USER，回復正常上線
/*
當指定 MULTI_USER 時，可讓所有具有適當權限的使用者都連接到允許的資料庫。
*/
ALTER DATABASE [資料庫名稱]
SET MULTI_USER;
GO

--步驟13. 查詢資料庫狀態已經變更為：ONLINE 與 MULTI_USER。
SELECT name N'資料庫', state_desc N'資料庫狀態', user_access_desc N'使用者存取設定'
FROM sys.databases
where name='DBLog'
GO

參考資料：
自動重建交易記錄檔案(*.ldf)，以正常卸離情況為例
http://sharedderrick.blogspot.com/2010/02/ldf.html

資料庫狀態
http://msdn.microsoft.com/zh-tw/library/ms190442.aspx

DBCC CHECKDB (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms176064.aspx

INF：有修復選項的 DBCC CHECKDB/CHECKTABLE 要求資料庫必須為「單一使用者模式」
http://support.microsoft.com/kb/264154/zh-tw

In Recovery... CHECKDB From Every Angle: EMERGENCY mode repair - the very, very last resort
http://www.sqlskills.com/blogs/paul/post/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort.aspx

When should you rebuild the transaction log?
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/15/632398.aspx

Which part of REPAIR_ALLOW_DATA_LOSS isnt clear?
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/16/633645.aspx

Ta da! Emergency mode repair
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx

attaching DB without .ldf file ??
http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/189be01f-23de-48b0-96cc-8f1292c13c54

系統重建交易記錄檔案(*.ldf)，以正常卸離情況為例。錯誤訊息：訊息 5120，層級 16，狀態 101 ... 無法開啟實體檔案

這邊所指的正常卸離狀況，例如：
1. 卸離此資料庫。
2. 離線此資料庫。
3. 關閉 SQL Server 服務後。
等。

但是附加的方式，則需要改變，若仍是採取一般方式來附加資料庫時，但因為沒有交易記錄檔，將遇到以下的錯誤訊息：

訊息 5120，層級 16，狀態 101，行 1
無法開啟實體檔案 "C:\myAdmin\DB\DBLog_log.ldf"。作業系統錯誤 2: "2(系統找不到指定的檔案。)"。

若僅複製其資料檔案，但不包含交易記錄檔案(*.ldf)。
符合上述的情境，可以在沒有包含交易記錄檔案(*.ldf)情況下，僅是附加資料檔案，系統可以自動重新建立交易記錄檔案(*.ldf)。

重建交易記錄檔案(*.ldf)，以正常卸離情況為例：

適用版本：
SQL Server 2005 與 2008

方法1：使用 Transact-SQL 語法：

--01 建立範例資料庫：DBLog
USE [master]
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DBLog')
DROP DATABASE [DBLog]
GO
CREATE DATABASE [DBLog] ON  PRIMARY
( NAME = N'DBLog', FILENAME = N'C:\myAdmin\DB\DBLog.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [G1]
( NAME = N'DBLogG1F1', FILENAME = N'C:\myAdmin\DB\DBLogG1F1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'DBLog_log', FILENAME = N'C:\myAdmin\DB\DBLog_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

--
USE DBLog
GO
-- 查詢資料庫檔案的磁碟使用空間與資料實際使用空間
SELECT name N'邏輯名稱' , size/128.0 N'使用的磁碟空間(MB)' ,  CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 N'資料實際上使用的空間(MB)'
,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 N'剩餘的可用空間(MB)'
FROM sys.database_files;

/*=========================================================*/
--02 卸離此資料庫，請手動修改交易記錄檔案(*.ldf)的檔名。
USE [master]
GO
ALTER DATABASE [DBLog] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'DBLog'
GO

/*=========================================================*/
--03 重新附加此資料庫：僅是附加資料檔案，系統可以自動重新建立交易記錄檔案(*.ldf)。
USE [master]
GO
CREATE DATABASE [DBLog] ON
( FILENAME = N'C:\myAdmin\DB\DBLog.mdf' ),
( FILENAME = N'C:\myAdmin\DB\DBLogG1F1.ndf' )
FOR ATTACH
GO

/* 顯示的訊息：
檔案啟動錯誤。實體檔案名稱 "C:\myAdmin\DB\DBLog_log.ldf" 可能不正確。
已建立新記錄檔 'C:\myAdmin\DB\DBLog_log.LDF'。
*/

方法2：使用 SSMS 管理工具，重建交易記錄檔案(*.ldf)的方式：

步驟01. 使用 SSMS 管理工具，「附加」此資料庫。
步驟02. 在「附加資料庫」視窗，在右下方的窗格內，選取指定的「記錄檔」之資料列，在「訊息」區域，也會被標示為「找不到」。
步驟03. 點選下方的「移除」，刪除此「記錄檔」。
步驟04. 再點選「確定」，進行附加資料庫作業。

原本建立資料庫時的交易記錄檔，改名為：DBLog_log.ldf_qq，預設初始化的大小約：3,072(KB)。

僅是附加資料檔案，系統自動重新建立交易記錄檔案(*.ldf)：DBLog_log.LDF，大小約：504(KB) 。

參考資料：
卸離和附加資料庫
http://msdn.microsoft.com/zh-tw/library/ms190794.aspx
保護資料和記錄檔
http://msdn.microsoft.com/zh-tw/library/ms189128.aspx

2010-02-13

檢視每一個索引的片段(Index fragmentation)資訊

請參考以下的範例碼：

-- 檢視指定資料庫內的每一個索引之片段(fragmentation)資訊，如下：
SELECT sch.name N'結構描述', obj.name N'資料表',
inx.name AS N'索引名稱',
index_type_desc N'索引類型',
avg_fragmentation_in_percent '片段(%)',
avg_page_space_used_in_percent N'頁面飽和度(%)',
fragment_count,
avg_fragment_size_in_pages,
page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'SAMPLED') AS phy
INNER JOIN sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id
INNER JOIN sys.objects AS obj ON phy.object_id = obj.object_id
INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
WHERE index_type_desc <> 'HEAP'  AND fragment_count IS NOT NULL AND avg_fragment_size_in_pages IS NOT NULL
ORDER BY 2

-- Get fragmentation for each index  (SAMPLED mode)
SELECT sch.name [Schema], obj.name [Table], inx.name AS [Index], index_type_desc [Index_Type],
 avg_fragmentation_in_percent [Fragmentation(%)], avg_page_space_used_in_percent [Page fullness(%)],
 fragment_count [Fragment_count], avg_fragment_size_in_pages [Avg_Fragment_Size_in_Pages],
 record_count [Record_Count], max_record_size_in_bytes, min_record_size_in_bytes, avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'SAMPLED') AS phy
 INNER JOIN sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id
 INNER JOIN sys.objects AS obj ON phy.object_id = obj.object_id
 INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
WHERE index_type_desc <> 'HEAP'  AND fragment_count IS NOT NULL AND avg_fragment_size_in_pages IS NOT NULL
ORDER BY 2
GO

由上，可以觀察到索引 CL_SID 在關於片段(fragmentation)資訊：
avg_fragmentation_in_percent '片段(%)' -- 51.0280
avg_page_space_used_in_percent N'頁面飽和度(%)' -- 50.9545
fragment_count -- 274
page_count -- 535

一般而言：
avg_fragmentation_in_percent '片段(%)' -- 理想值為 0
avg_page_space_used_in_percent N'頁面飽和度(%)' -- 理想值為 100

雖然 avg_fragmentation_in_percent 的值愈接近零，其效能愈好。
不過，百分之 0 到 10 之間的值都在接受範圍內。
所有縮減片段的方法 (例如，重建、重新組織或重新建立)，都可以用來縮減這些值。

在了解片段的程度後，請使用以下方式來決定修正片段的最好方法：
(1) avg_fragmentation_in_percent 值 > 5% 和 < = 30%，採取重新組織索引。
語法：ALTER INDEX REORGANIZE ......

(2) avg_fragmentation_in_percent 值 > 30%，採取重建索引。
語法：ALTER INDEX REBUILD WITH (ONLINE = ON)

基本上，不建議使用上述任何命令來處理片段層級過低 (低於 5%) 的情況，這是因為重組或重建索引的成本遠遠超過移除這麼少量的片段所獲得的好處。

重建索引可於線上或離線執行。但重新組織索引則一律在線上執行。
若要達到與重新組織選項相似的可用性，您應該在線上重建索引(使用 Enterprise 版本)。

在執行重建索引作業後：
索引 CL_SID 在關於片段(fragmentation)資訊，如下：
avg_fragmentation_in_percent '片段(%)' -- 0
avg_page_space_used_in_percent N'頁面飽和度(%)' -- 98.7938
fragment_count -- 2
page_count -- 276

參考文件：
重新組織和重建索引
http://technet.microsoft.com/zh-tw/library/ms189858.aspx

2010-02-10

Hyper-V 無法啟動虛擬機器，錯誤訊息：無法建立磁碟分割：系統資源不足，無法完成要求的服務。(0x800705AA)。Failed to create partition: Insufficient system resources exist to complete the requested service. (0x800705AA)

在建置 Hyper-V 環境時，遇到以下的錯誤訊息：

無法建立磁碟分割：系統資源不足，無法完成要求的服務。(0x800705AA)
Failed to create partition: Insufficient system resources exist to complete the requested service. (0x800705AA)

在檢查可用的磁碟空間後，還有數百GB可以使用。
但怎麼會說是磁碟空間不足呢？

在參考相關資料後，發覺可能的原因是虛擬機器將實體機器的記憶體耗用殆盡了。

以下是 Hyper-V Server 2008 R2 的部份系統需求：
RAM: Minimum:
1 GB RAM; Recommended: 2 GB RAM or greater (additional RAM is required for each running guest operating system); Maximum 1 TB.

實驗的環境：
實體機器有：4 GB。
同時啟動 4 個虛擬機器，其記憶體的配置如下：
VM1 = 1 GB
VM2 = 1 GB
VM3 = 1 GB
VM4 = 512 MB

在安裝虛擬機器的系統時，都沒有什麼問題。
但等到虛擬機器上面裝滿了相關軟體，每個虛擬機器的大小都超過 20 GB的磁碟空間。
就發生上述的錯誤訊息。

目前的解決方式：

在本環境中，減少各個虛擬機器所使用的記憶體，整體虛擬機器的記憶體使用量是小於 3GB。

參考資料：

Microsoft Hyper-V Server 2008 R2 System Requirements
http://www.microsoft.com/hyper-v-server/en/us/system-requirements.aspx

I cannot start any VMs - "could not initialize" "failed to create partition" (error 0x800705AA)
http://social.technet.microsoft.com/Forums/en/winserverhyperv/thread/58d2517a-04b8-4189-a50c-e56864f686d0

2010-02-09

忘記 Administrator 密碼，使用開機修復光碟：ERD Commander 的 LockSmith 來重設密碼；以 Windows Server 2008 R2 為例。

若因故忘記 Administrator 帳戶的密碼，或是密碼被惡意侵入修改等，導致無法登入作業系統時。

可以利用 Microsoft Diagnostics and Recovery Toolset 6.5 (Microsoft DaRT) 來建立可開機修復光碟：ERD Commander。

利用在 ERD Commander 光碟所提供的工具：LockSmith 來重設 Administrator 帳戶的密碼。

工作1：下載與安裝軟體：

Microsoft Desktop Optimization Pack 2009 R2 (x86 and x64) - DVD (English)

桌面最佳化工具組（Microsoft Desktop Optimization Pack，簡稱MDOP）

適合的作業系統：

Windows® 7

Windows Server® 2008 R2

安裝時，請選擇「Microsoft Diagnostics and Recovery Toolset」。

工作2：建立可開機修復光碟：ERD Commander

接下來，就是要建立可開機修復光碟：ERD Commander。

請先準備好 Windows Server 2008 R2 光碟片。

步驟如下：

1. 執行「開始」\「所有程式」\「Microsoft Diagnostics and Recovery Toolset」。

2. 點選「ERD Commander Boot Media Wizard」。

3. 在「ERD Commander Boot Media Wizard」視窗，點選「Next」。

4. 在「Select Boot Image」頁面，點選「Browse」，選擇 Windows Server 2008 R2 光碟片的路徑後，點選「Next」。

5. 在「Preparing Files」頁面，點選「Next」。

6. 等候片刻後，在「Tools Selection」頁面，接受預設值，選取全部的工具，點選「Next」。

7. 在「Crash Analyzer Wizard」頁面，點選「Locate the Debugging Tools for Windows installation located on the target computer 」，點選「Next」。

8. 在「Standalone System Sweeper Definition Download」頁面，點選「No, manually download definitions later」，點選「Next」。

9. 在「Additional Derivers」頁面，點選「Next」。

10. 在「Additional Files」頁面，點選「Next」。

11. 在「Create Startup Image」頁面，手動輸入Boot Image檔案的路徑與檔名，點選「Next」。

12. 在「Burn to a recordable CD」頁面，若要立刻執行燒錄開機修復光碟作業時，請按下「Next」。若僅是想先產生 *.iso檔案，之後再依據需求進行燒錄，請取消勾選「Burn the image to the following recordable CD drive」後，再點選「Next」。