2011年9月26日 星期一

SQL Server 記憶體管理 - DBCC FREEPROCCACHE 說明與應用

底下的這篇是由國外的 SQL Server Central 的網站中載址而來,主要介紹 DBCC FREEPROCCCACHE的使用與介紹,再請大家參閱:

原文主旨:Fun With DBCC FREEPROCCACHE

最近任何時候你可以看到關於 DBCC FREEPROCCACHE 指令的訊息公告在部落格、雜誌文章或書本上,你會得到一些嚇人的警告建議您不要使用他在正式產品的系統上。除非您真的有心理準備此系統會有很大的影響。在範例上,官方線上說明也有講到使用 DBCC FREEPROCCACHE 去清除快取計畫要非常的小心。通常釋放快取計畫的原因,在範列上,一個預儲程序去重新編譯取代快取的重覆使用,SQL Server 錯誤記錄將包含下列的資訊: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations." 這個訊息是記錄當快取刷新在區間每五分鐘內。

我將討論關於執行 DBCC FREEPROCCACHE 並不會造成更多的窘境,當在一個非常忙錄的OLTP系統上。大部份的系統在計畫重新編譯的過程中將造成大量的CPU使用率變高。實際上,他主要是用的在於重設 cached_time 時間對於 sys.dm_exec_procedure_stats, 如同大部份的預儲程序在你的一般工作負載上。這可以透過簡單的方式去取得查詢成本較重的查詢或預儲程序在一個累計的基礎上,你可以看到在總計的工作時間或總計的邏輯讀取的增進。

說了這麼多,我將要顯示一個簡單的方法去清除全部或部份的快取,雖然可能會稍微影響到系統。執行 DBCC FREEPROCCACHE 是一種暴力方法。所以如果你是考量到這部份,你可以執行下列所示的變化之一。

Example 1 (Sledgehammer)

-- Remove all elements from the plan cache for the entire instance
DBCC FREEPROCCACHE;

-- Flush the cache and suppress the regular completion message
-- "DBCC execution completed. If DBCC printed error messages, contact your system administrator."
DBCC FREEPROCCACHE WITH NO_INFOMSGS;

-- Example 2 (Ballpeen hammer)
-- Remove all elements from the plan cache for one database
-- Get DBID from one database name first
DECLARE @intDBID INT;
SET @intDBID = (SELECT [dbid]
FROM master.dbo.sysdatabases
WHERE name = 'AdventureWorks');

-- Flush the procedure cache for one database only
DBCC FLUSHPROCINDB (@intDBID);

-- Example 3 (Scalpel)
-- Remove one plan from the cache
-- Get the plan handle for a cached plan
SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%/* GetOnlineSearchResultsMonday %';

-- Remove the specific plan from the cache using the plan handle
DBCC FREEPROCCACHE (0x05000800F7BA926C40C15055070000000000000000000000);


個人說明備註:
其實寫到這我個人有一個疑問,也就是在什麼時候才會去執行這個指令,後來經過我的查詢後找到使用的時機,假設當你透過 sp_configure 來改變設定值,如 max degree of parallelism ,由於改變這個值之後會影響到計畫的執行,但是由於查詢已經被快取了,所以造成計畫不會重新編譯,這時如果您不能重新啟動服務時,你就可以透過這個指令來清除快取。

參考資訊:
  1. Fun With DBCC FREEPROCCACHE:http://www.sqlservercentral.com/blogs/glennberry/archive/2009/12/28/fun-with-dbcc-freeproccache.aspx
  2. DBCC FREESYSTEMCACHE (Transact-SQL):http://msdn.microsoft.com/zh-tw/library/ms178529.aspx
  3. DBCC FREEPROCCACHE (Transact-SQL):http://msdn.microsoft.com/zh-tw/library/ms174283.aspx

關鍵字:SQL ServerMemoryDBCC FREEPROCCACHE

2011年9月25日 星期日

SQL Server 2012 - AlwaysOn 安裝與設定教學

由於此版是在CTP3的環境下建立的,而正式版的環境建立,再請參考我的另一篇文章,謝謝。

SQL Server 2012 新功能 - AlwaysOn安裝與設定
http://caryhsu.blogspot.com/2012/04/sql-server-2012-alwayson.html


今天要來介紹的是SQL Server 2012 Denali的新功能 AlwaysOn的安裝與設定,相信有在注意我的部落格的人,應該都有耳聞這個功能,或是大家可以參考下列的連結再重新認識一下AlwaysOn,並實際學習如何將這個功能進行設定。

1、功能說明

1-1 AlwaysOn的新特點:

1-2 AlwaysOn的架構:


SQL Server 2012 (Code Name Denali) - HA 新功能 - AlwaysOn

資料庫版本:SQL Server 2012 Denali (CTP3)

2、安裝與設定 AlwaysOn

2-1 設定Cluster
而安裝的方式上 AlwaysOn 的節點必須架構在 Cluster之上,所以必須先設定好Cluster,設定方式請大家參考我的下列兩篇文章進行設定,雖然 AlwaysOn 是架構在 Cluster,但他跟以往的安裝略有不同,所以在安裝時,請注意下列幾點事項。
注意事項:
  • AlwaysOn 的2個節點上資料庫不需安裝在叢集磁碟上。
  • AlwaysOn 不需依靠在 MSDTC 的服務上。
  • 安裝節點上的資料庫時,需選擇 [新增SQL Server 獨立安裝或將功能加入至現有安裝]
  • 請先安裝在第一台之後,然後再安裝在第二台。
  • 關於SQL Server 2012的安裝方式,請參考我的另一篇作品。


2-2 安裝完成後,分別到兩台電腦上啟用 AlwaysOn 的功能,路徑為 [SQL Server 組態管理員] -> [SQL Server] -> [內容] -> [AlwayOn High Avaliability] ,你會看到 [啟用 AlwaysOn 可用性群組] 勾選之後,點選確定即可。


2-3 啟動 SQL Server Management Studio ,連線到資料庫後,點選 [管理] -> [可用性群組] -> [新增可用性群組精靈]。

2-4 當你啟用 [新增可用性群組精靈] 如果得到下列的錯誤訊息時,代表你在第2-2的步驟沒有啟用成功,請再次確認即可。

PS:The HADR service must be enabled for server instance 'Server-Name' before you can create an availability group on this instance. To enable the HADR service, open the SQL Server Configuration Manager, select SQL Server Services, right-click on the SQL Server instance name, select Properties, and use the SQL HADR tab of the Server Properties dialog. Enabling the HADR service requires that the SQL (Microsoft.SqlServer.Managmement.HadrTasks)


2-5 輸入 Availability Group Name

2-6 選擇要加入 HADR 的架構中的資料庫,請注意在節點啟用HADR後,不像Cluster是以節點為主,而是以單一的資料庫為主,所以啟用後,必須手動的將你想要的資料庫逐一加入,而加入的資料庫必須符合下列的需求。

  • 資料庫的復原模式必須選擇 [Full Backup]。
  • 在將資料庫加入Availability Group之前必須先進行全備份。
參考說明:



2-7 點選 Add Replicas,將第二台也加入。

2-8 設定 Availability Group 的連接埠,使用預設值即可。

2-9 設定主機上的分享目錄,請確認此目錄第二台也可以連線的到。

2-10 驗證兩台之間設定值,這個部份請注意系統會檢查第一台資料庫的安裝路徑必須在第二台電腦上也有相同的目錄,否則會驗證失敗。

2-11 最後的設定總結,再點選完成即可。

2-12 設定完成後,到 [容錯移轉叢集管理員] -> [服務與應用程式]你就可以看到剛剛新增的Availability Group已上線成功。

3、測試 AlwaysOn 的功能

3-1 連線到第一台之後,試著新增一個資料表與資料。


3-2 連線到第二台之後,點選 [管理] -> [可用性群組] -> [剛剛自訂的 Availability Group Name] -> [可用性複本] -> [第二台名稱] -> [容錯轉移]。


3-3 轉換成功後,你就可以看到兩台電腦的 [主要] 與 [次要] 的身候就會互換。

3-4 連線到第二台,你就可以看到剛剛在第一台新增的資料表與資料也成功的轉入到第二台上。

其他相關網址:
  1. SQL Server 2012 Code Name(Denali) 新功能介紹與預覽
  2. SQL Server 2012(Code Name Denali) - 新T-SQL語法介紹 – 分頁功能
  3. SQL Server 2012(Code Name Denali) - 新T-SQL語法介紹 – Sequence
  4. SQL Server 2012(Code Name Denali) - 新T-SQL語法介紹 – Code Snippet Manager
  5. SQL Server 2012(Code Name Denali) - 以列為主的新儲存方式(雲端儲存架構)
  6. SQL Server 2012(Code Name Denali) - FileTable介紹
  7. 微軟介紹雲端平台就緒的資訊 - TechEd 2011
  8. SQL Server 2012 (Code Name Denali) - HA 新功能 - AlwaysOn
  9. SQL Server 2012 (Code Name Denali) - AlwaysOn 安裝與設定教學
關鍵字:SQL ServerDenaliAlwaysOnHA

2011年9月20日 星期二

MSDN - 網站無法連線的處理方式

今天遇到一個問題,就是當連線到 MSDN的網站時,竟然無法連線,網頁只會秀出下列的錯誤訊息,經過確認後,我整合成下列的兩個方法,再請大家試看看。

錯誤訊息:
The specified CGI application encountered an error and the server terminated the process.

解決方法:
方法-1:
1. 關閉 IE 瀏覽器
2. 點選 [開始] -> 執行
3. 輸入 inetcpl.cpl 之後會開啟網際網路的選項
4. 選擇 [瀏覽歷程記錄] -> [刪除] -> 勾選 Cookies 之後再選取 [刪除]。

方法-2:
1. 輸入 inetcpl.cpl 之後會開啟網際網路的選項
2. 進階 -> 重設 Internet Option 設定 -> 重設
3. 勾選 [刪除個人設定] 然設點選 [重設]

2011年9月18日 星期日

SQL Server Integration Service - 最小權限設定

SQL Server 權限的設定,一直以來是一個大學問,我看過許多人在設定新權限,由於不清楚設定的細節,所以就只有直接設定 sysadmin 的角色,但是這樣一來非常的危險,容易遭到外部的攻擊,所以在新使用者上就必須只開放需要的權限即可,而本篇主要介紹 SSIS 如何設定,以下我們就以設定一個新的使用者可以登入到 SSIS,並且只有最小的權限為主。

1、 新增一個使用者,並指定名稱與密碼。


2、在 [伺服器角色] 的部份不需特別指定權限,只需指定預設的 [public] 即可。



3、開啟該使用者的安全性屬性,[使用者對應] -> [msdb] -> [db_ssisltduser] 的權限勾選即可。



4、使用者如果需要針對特定的資料庫進行讀寫的話,可以只勾選 [db_datareader] 或 [db_datawriter]的權限即可。


關鍵字:SSIS最小權限設定Minimum Authority、Integration Service

2011年9月17日 星期六

Reporting Service 2008 與 2008 R2 - 自訂錯誤畫面

在以往 Reporting Service 2005 是依附在IIS之下,而在2008之後,Reporting Service 就是自行透過 HTTP.SYS進行控制,近來收到許多人詢問關於 Reporting Service 2008 的錯誤網頁要如何自訂,如 HTTP 404、500等錯誤等,後來終於找到了設定方法,設定方法如下:

ReportManager 的目錄位址:
C:\Program Files\Microsoft SQL Server\MSRS10.SSRS\Reporting Services\ReportManager

設定檔的位址:
C:\Program Files\Microsoft SQL Server\MSRS10.SSRS\Reporting Services\ReportManager\web.config

設定檔位置如上所述,開啟之後,將原本在 [customerrors] 的區段中的值,改為下列區段2中介紹的值即可,關於值的定義,說明如下:

1、原本預設的區段:
<customerrors mode="RemoteOnly" />

2、修改後的區段:
<customerrors mode="On" defaultRedirect="http://yourserver/Reports/default_custom_error.htm">   <error statusCode="404" redirect="http://yourserver/Reports/custom_error_404.html" />
</customerrors>

  1. yourserver : Reporting Service 主機的網域名稱。
  2. custom_error_404.html : 特別指定的網頁名稱,而網頁必須放在ReportManage的目錄下。
  3. default_custom_error.htm:除了您特別指定的代碼之外,所有網頁錯誤代碼的預設頁面。
  4. 404:網頁的錯誤代碼。

New Report Server Architecture 



參考連結:

  1. What's New in Report Server Architecture and Tools:http://msdn.microsoft.com/en-us/library/bb630410.aspx
  2. 在 IIS 7.0 中的 HTTP 狀態碼:http://support.microsoft.com/kb/943891


關鍵字:Reporting ServiceCustom Error Page

2011年9月16日 星期五

SQL Server - 鎖定類型介紹

本篇文章是從 (http://www.sqlteam.com) 轉載而來,主要介紹SQL Server 中鎖定的類型,再請大家參考。

原文主旨:Introduction to Locking in SQL Server

鎖定是很多RDBMS很重要的一部份,而且也是每個DBA必須去了解的部份。他是一個資料庫中的功能,在多使用者的環境下沒有他就不能正常的運作。主要的問題為鎖定在本質上是一種邏輯而非實體的問題。這代表著硬體數量的多寡也將無法幫助你去解決他。或許硬體的增加可能可以增加效能,但這只是一個假像的修正。在一個沉重的多使用者環境問題遲早會再出現。

鎖定模式:
全部的範例皆執行在預設的 READ COMMITED  的隔離層級下。透過鎖定在不同的隔離層級下,下列範例是透過語法秀出鎖定的類型。在此先行說明在sys.dm_tran_locks中的三個欄位的意義。
  1. resource_type:這將告訴我們目前在資料庫中鎖定的項目為何。數位可能為:DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, ALLOCATION_UNIT.
  2. request_mode:這將告訴我們鎖定的模式為何。
  3. resource_description:這將顯示一個資料的描述與簡介。通常保持著一個Page的ID,物件檔案,資料列,列,等....,這不是一個填補在每一個鎖定的樣目。
範列中過濾條件為 resource_type <> 'DATABASE' 意思上是代表將不要去看到一般分享的資料庫鎖定。這是固定的行為。全部的輸出皆由 sys.dm_tran_locks 的動態管理檢示表。在下列的範例中,將只秀出鎖定的部份。在執行上你也可以決定自行輸出的欄位。

共享鎖定 Shared Locks (s)
共享鎖定是保持在資料讀取悲觀 (Pessimistic) 並行的模組下。一個共享鎖定運作時是保持其他交易能取讀取但是不能夠修改鎖定資料的情況下。鎖定的資料將在共享鎖定讀取完成之後釋放,除非該交易是透過提示(READCOMMITTED、READCOMMITTEDLOCK)的方式或是隔離等級等於或大於 REPEATABLE READ。在一般的情況下,你將看不到共享鎖定,因為在 選取sys.dm_tran_locks 的命令過程中 SELECT 已經釋放鎖定 。這也就是為什麼需要一個額外的 (HOLDLOCK) 提示來看到鎖定的類型。

BEGIN TRAN
USE AdventureWorks
SELECT * FROM Person.Address WITH (HOLDLOCK)
WHERE AddressId = 2
SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK


更新鎖定 Update Locks (U)
更新鎖定是一種分享和獨占的混合鎖定。在DML執行之前修改之前會使用共用鎖定,其他交易可以讀取被鎖定的資料,但不可以修改。一旦開始修改時,就變成了獨占鎖定,其他交易無法讀取和更新被鎖定的資料,直到交易結束。所以更新鎖是可以避免造成死結的發生。在相同的時間只有一個更新鎖能夠鎖定資料,類似獨占鎖。但是不同的地方,更新鎖只能鎖住自已本身不能夠修改在基礎的資料上。他可以在修改資料之前轉換成獨占鎖,你可以在更新鎖的部份透過提示 UPDLOCK來完成。

BEGIN TRAN

USE AdventureWorks

SELECT * FROM Person.Address WITH (UPDLOCK)
WHERE AddressId < 2 SELECT resource_type, request_mode, resource_description FROM sys.dm_tran_locks WHERE resource_type <> 'DATABASE'

ROLLBACK


獨占鎖定 Exclusive Locks (X)
獨占鎖定是使用在一個修改資料的交易中並防止其他交易進行修改。你只可以透過 NOLOCK的提示讀取到已鎖定的資料或讀取未確認的隔離等級資料。當修改資料時,SQL Server會使用獨佔鎖定。在交易結束之前,其他交易的鎖定請求都會被拒絕。一個資源只能有一個獨佔鎖定,當一個交易對某個資源進行獨占鎖定時,其他交易無法讀取該資源,由此可知,這種鎖定會限制了同時線上數量。

BEGIN TRAN

USE AdventureWorks

UPDATE Person.Address
SET AddressLine2 = 'Test Address 2'
WHERE AddressId = 5

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK


意圖鎖定 Intent Locks (I)
意圖鎖定的意思是當一個交易通知其他的交易,他有 "意圖" 去鎖定資料,所以如同名稱一般。他是確保交易得到正確的資料進行修改,防止其他交易去取得一個更高層級的鎖定。這個意義上是表示,你在 Page 得到一個鎖定之前或在 Table 上的一個 Row 的意圖鎖定。這是防止其他的交易將獨占鎖定放置在表格上,並嘗試去取消 Row 或 Page 的鎖定。在範列上可以到看意圖獨占鎖定放置在 Page 和 Table 上,是為了要防止其他交易去鎖定資料。

BEGIN TRAN

USE AdventureWorks

UPDATE TOP(5) Person.Address
SET AddressLine2 = 'Test Address 2'
WHERE PostalCode = '98011'

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> DATABASE

ROLLBACK


綱要鎖定 Schema Locks (Sch)
綱要鎖定分成兩種類型:
  • 綱要平穩鎖定 Schema Stability Locks (Sch-S):使用在當產生執行計畫時,這個鎖定並不會封鎖去存取其他的資料。
  • 綱要修改鎖定 Schema Modification Locks (Sch-S):使用在當執行一個 DDL 的語句。當結構正在改變時,將封鎖存取物件資料。

在下列的範例上將可以看到 Sch-S 和 Sch-M 的鎖定目前的系統表格和 TestTable再加上一些其他的鎖定在系統表格上。
BEGIN TRAN

USE AdventureWorks

CREATE TABLE TestTable (TestColumn INT)

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK


大量更新鎖定 Bulk Update Locks (BU)
大量更新鎖定是使用大家操作當 TABLOCK 提示是使用在匯入的時候。這是允許多個快速同時的新增經由不允許的資料讀取到其他的交易上。

交談鎖定 Conversion Locks
交談鎖定是鎖定結果從一個類型的鎖定轉換到另一種。下列是三種交談鎖定的類型:
  • 共享與意圖獨佔 Shared with Intent Exclusive (SIX):一個交易將包含一個共享鎖定與一些Page 或 Row 的獨占鎖定。
  • 共享與意圖更新 Shared with Intent Update (SIU):一個交易將包含一個共享鎖定與一些Page 或 Row 的更新鎖定。
  • 更新與意圖獨占鎖定 Update with Intent Exclusive (UIX):一個交易將包含一個更新鎖定與一些Page 或 Row 的獨占鎖定。

在下列的範列中,你可以看到 UIX 的交談鎖定在頁面上:
BEGIN TRAN

USE AdventureWorks

UPDATE TOP(5) Person.Address
SET AddressLine2 = 'Test Address 2'
WHERE PostalCode = '98011'

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK


鍵值範圍鎖定 Key-Range Locks
鍵值範圍鎖定保護一個 Row 的範圍隱含在一個記錄的讀取經由一個 Transact-SQL 語句當使用序列化交易隔離層級。鍵值範圍鎖定可以防止 幽靈讀取 (Phantom Reads) 的情況發生。經由鍵值範圍保護的的資料列,他可以防止 幽靈新增或刪除到一個交易記錄。在下列的範列將秀出兩種鍵值範圍鎖定:

譯註:
Phantom Reads:主要用在高層級的交易隔離,當交易A同時查詢一個資料表二次,假設第一次得到五筆,而第二次查詢前,剛好交易B新增或刪除了一筆時,造成交易A的第二次查詢與第一次查詢筆數不相同。
  • RangeX-X - 獨占鎖定在鍵值和獨占鎖定之間最後的鍵值範圍。
  • RangeS-U - 共享鎖定在鍵值和更新鎖定間最後的鍵值範圍。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN

USE AdventureWorks

UPDATE Person.Address
SET AddressLine2 = 'Test Address 2'
WHERE AddressLine1 LIKE '987 %'

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK


鎖定粒度 Lock Granularity
鎖定的粒度考量到 Table、Page 和 Row 的鎖定。如果你有一個叢集索引在表格上將會由鍵值鎖定取代列的鎖定。鎖定在較低的層級會同時的增加,但是如果使用了過多的鎖定是將造成消耗更多的記憶體,反之亦然。所以粒度簡單的意思是指SQL Server鎖定資料的層級。同樣地我們需要注意更多的限制隔離層級,較高的鎖定層級會去保持資料在正確的狀態。你可以覆寫鎖定層級經由使用 ROWLOCK、PAGLOCK、TABLOCK提示,但是使用這些提示是不鼓勵的,因為SQL Server明白什麼時候使用合適的鎖定。如果你必須使用提示你將必須注意資料一致性的問題與可能發生的情況。

譯註:關於提示的使用,可以參考我的另二此文章
  1. 你是否需要 SQL Server Query Hint ?
  2. SQL Server 效能調整 - Optimizer Hint 的使用

自旋鎖 Spinlocks
自旋鎖是一種輕量級的鎖定機制,他將不鎖定資料,但是他將透過交易鎖定一個區塊的資料一段時間後,再進行釋放,然後再轉由其他交易鎖定。這是一種互斥機制藉以減少 SQL Server中的 Threads 進行 本文交換(Context Switching) 次數。

鎖定相容性矩陣 Lock Compatibility Matrix
下列的圖表是微軟的網站捉取來的,同樣的一個好的資源進行鎖定時,可以透過 鎖定相容性矩陣來與其他的鎖定模組進行比較,或許在現在你沒有使用的急迫性,但是以後你應該是會用到的。


總結
希望這個主題可以讓大家對SQL Server的運作與鎖定有些微的了解,也可以知道鎖定的重要性
在應用程式、資料庫設計和操作上。請記得鎖定的問題是一種邏輯性並非實際自然可見的,所以必須深思熟慮後才可以了解。鎖定與交易的隔離等級有非常大的相關聯,作者將會在下一個主題介紹一些方法去解決鎖定上的問題。

參考網址:
  1. Introduction to Locking in SQL Server:http://www.sqlteam.com/article/introduction-to-locking-in-sql-server
  2. Lock Compatibility:http://msdn.microsoft.com/En-US/library/ms186396.aspx
  3. Lock Granularity and Hierarchies:http://msdn.microsoft.com/en-us/library/ms189849.aspx
  4. SET TRANSACTION ISOLATION LEVEL:http://msdn.microsoft.com/en-us/library/ms173763.aspx

關鍵字:SQL ServerLockingLocksLocks Hints