CaryHsu - 學無止盡

2011年7月1日星期五

效能監視器的進階應用 - Logman

當問題發生時，最需要的就是有資料進行確認與還原問題，尤其是協助他人處理問題時，沒有相關的資料進行參考時，就有可能花費更多的時間進行處理，如最常遇到的效能調整就是一個典型的問題，而在Windows的平台中有一個很好用的資料收集器，就是效能監視器，但是由於此軟體可以收集的資料種類非常的多，如果收集的過多，會造成系統效能的負擔，而如果收集的太少，也會無法進行有的分析，而且如果要監控的電腦很多台的時候，就需要逐一的設定，所以在此提供一個內建的軟體(Logman)，透過此軟體，可以事先將需要的物件種類範本準備好後，再產生一個效能監能器進行監控。

監控資料庫上常用到的物件種類：

System

Memory
Paging File
Process
Processor
System
Physical Disk
Network interface

SQL Server

SQL Server:Access Methods
SQL Server:Buffer Manager
SQL Server:Databases
SQL Server:General Statistics
SQL Server:Locks
SQL Server:Memory Manager
SQL Server:SQL Statistics
SQL Server:Transactions
SQL Server:Wait Statistics

Logman是一個可以透過指令的方式來進行效能監視器的設定與管理的軟體，方便進行監視器的設定步署。

操作方式：

CREATE COUNTER <CounterLogName>：建立[效能計數器]收集項目
Update <CounterLogName>：更新[效能計數器]收集項目

參數設定：

-f ：紀錄檔格式(bin|bincirc|csv|tsv|sql)
-max ：紀錄檔案最大大小(MB)
-si ：每次收集間隔
-o ：輸出路徑或是SQL Server DSN
-cf ：指定[效能計數器]設定檔
-c ：收集的計數器名稱
-s ：收集的遠端電腦名稱
-b ：收集的起始時間
-e ：收集的結束時間
-v ：指定輸出檔名編號方式，格式為 nnnnnn|mmddhhmm
-r ：依據設定的起迄時間重複每天收集

使用範例：

假設我想要設定一個監控檔在客戶端，名稱為 [pm_new]，輸出格式為[csv]，每個檔案的最大容量為 [100] MB，每隔 [30] 秒收集一次，輸出的檔案放置在 [c:\pm_tmp\]，每個檔案的前置名稱為 [log]，檔案格式為 [月日時分]，而為了大量步署，所以將範本檔案透過網路分享的方式放置在 [\\CARYHSU-COMPUTER\share\pm_template.xml]的電腦中，需要控監的電腦名稱為 [CARYHSU-COMPUTER]，最後此監控在每天早上的 [11:00] 開始，[22:00] 結束。

指令：
1、建立效能監控

logman create counter pm_new -f csv -max 100 -si 30 -o c:\pm_tmp\log -v MMddHHmm -cf \\CARYHSU-COMPUTER\share\pm_template.xml -s CARYHSU-COMPUTER -b 11:00 -e 22:00 -r

2、更新效能監控
logman update pm_new -f csv -max 100 -si 30 -o c:\pm_tmp\log -v MMddHHmm -cf \\CARYHSU-COMPUTER\share\pm_template.xml -s CARYHSU-COMPUTER -b 11:00 -e 22:00 -r

範本說明：在範本的制作上可以先透過效能監器先將需要的物件種類選擇好了，再將其他的文字刪除，只剩下物件名稱的部份如下方的範列即可。

範本文件內容：

\Memory\Available MBytes
\Memory\Page Faults/sec
\MSSQL$SQL2008R2:Buffer Manager\Buffer cache hit ratio
\PhysicalDisk(_Total)\Avg. Disk Bytes/Read
\PhysicalDisk(_Total)\Avg. Disk Bytes/Write
\PhysicalDisk(_Total)\Avg. Disk Queue Length
\Processor(_Total)\% Processor Time

參考網址：
http://technet.microsoft.com/zh-tw/library/cc755366(WS.10).aspx

關鍵字：Performance Monitor、Logman、效能監視器、資料收集

2011年6月30日星期四

SQL Server 效能調整 - Optimizer Hint 的使用

下列的文章是從SQL Server Performance的網站轉載而來，剛好與我的另外一篇文章 [你是否需要 SQL Server Query Hint ?] 有相關，所以特別整理在此，藉以提供參考。

在很多的案例上，SQL Server 的查詢優化器將正確的評估一個查詢和執行最佳化的可能，但是在某些情況下可能查詢優化器會產生一些不好的執行計畫，造成查詢效能的不佳。當你分別的去檢查每一個查詢時，你可以透過優化提示 (Optimizer Hint) 改寫查詢計畫，而Hint的種類上分成下列五種：

Table Hints: 在查詢時指定特定的索引。
Join Hints: 指定兩個表格結合時的策略。
Query Hints: 提示的使用將會影響GROUP BY 和 UNION 的運作。
Lock Hints : 使用上去避免不好的鎖定。
View Hints: 指定 Views 的 Indexs 。

在本質上，hints可以覆寫查詢優化推薦器。如果查詢優化器有錯誤時，使用hints是非常有幫助的。但如果你撰寫的 hints 有錯誤時，他將持續錯誤到你發現到後改變他為止，否則他會同樣的持續影響效能。

一般的來說，hints 應該是可以避免少用。因為查詢優化器是非常聰明(大部份的時間)，而且產生的執行計畫是最好的。所以我們必須確認所認知的部份，不要在不明確的情況下進行使用。當查詢優化器錯誤而且是我們確認的情況下，就可以透過 hints 來改善，進行最好的測試去並找到更好的方法。

如果你想像一個 hints 可能可以優化你的查詢，但是在使用前最好確認下列的步驟，，因為下列的其中一個將可能是實際問題：

更新相關表格的statistics。

如果有問題的查詢是透過 stored procedure，請重新編譯，並且重新執行確認執行的狀況是否有變好。
重新檢示查詢參數是否合適，並且嘗試重新改寫藉以改善。
重新檢示目前的索引，如果需要時可以嘗試進行更動。
如果你已有進行以上的動作，查詢仍然無法如同你的預期，那你可以考慮去使用 hints。

另外一個問題，使用 hints 在一個確信的地方是可以理解的，但如果情境改變時，可能 hints 會變得失效。所以如果你決定去使用一個 hints，你需要去建立一些處理記錄提醒自已定期去重新檢查 hints 的效能。如同資料或程式碼變更，都有可能造成 hints 的效能影響。如果沒有注意反而最終會讓hints降低效能而非提升。

*****
Hints 在大部份的時間上造成效能的降低影響比實質幫助上還多，舉例來說，你接管一個database，其中使用到許多的 hints，此時你需要先行了解到 hints 對效能的影響是好還是壞。方法上可以測試各個查詢在有 hints 與沒有的情況下，執行時間的表現。如果只有少數幾個 hints 時，在測試上可能不是個問題。但是如果數量非常多的時候，比如說幾千個時，有沒有什麼好的方法來重新確認全部的 hints 呢？

依據你的情境，有一個粗陋的方式可以關閉所有的 hints 針對單一使用者連線，或是全部SQL Server的連線。透過這個方法，你可以不需修改任何的程式即可。

DBCC TRACEON(8602)
上述的命令是關閉目前的連接中所有的index hints。所以執行此命令之後，任何的Transact-SQL將會忽略任何的index hints.

DBCC TRACEON(8755)
上述的命令是關閉目前連接中所有的locking hints。所以執行此命令之後，任何的Transact-SQL將會忽略任何的locking hints.

DBCC TRACEON(8722)
上述的命令是關閉目前連接中所有的other hints。所以執行此命令之後，任何的Transact-SQL將會忽略任何的other hints.

上述的 hints 都可以進行合併，如同下列的範例：

DBCC TRACEON(8602, 8755, 8722)
這個命令是關閉目前連接中所有的 hints。

現在，我們來關閉一個或多個 hints 在整個Server上，不只是目前的連接。

DBCC TRACEON(8602, 8755, 8722, -1)
經由增加 -1，即可達成針對整個服務進行。

一但你執行了上述的指令，你需要再關閉停用的部份，如果你只是關閉目前的連線，只要取消連接即可，但是如果你是針對整個Server時，就需要再執行一次下列的指令進行啟用。

DBCC TRACEOFF(8602, 8755, 8722, -1)
使用上述的命令，整個Server任何的關閉停用的部份，將會恢復正常。

上述的指令，將如何的運用在真實的世界中呢？方法上可以先開啟一個查詢視窗，執行程式碼，然後再關閉所有的 hints，然後再執行一次後，並觀察執行計畫的行為與執行時間，但在此時，暫不需要更改任何的程式碼。

另外一個方法是透過Profiler追踨工作負載，分類並排序彼此執行時間，然後關閉整個伺服器中的hints，再啟用另一個Profiler追踨工作負載，同樣的也進行分類與排序。透過這個方法，你可以比較出兩個查詢之間的差異。這個方法雖然也是很複雜，但是總比單獨的進行各自的測試上，還要來的簡單。

如果你加入一個 hints 但是卻未使用？可能是因為你加入的 hints 被SQL Server忽略了。舉例來說，如果你指定一個 index hint 到查詢中，但是查詢優化器不需去存取表格，因為他有找到一個相關的Indexed View去取代，此時hint就會被忽略。

在另外的案例上，locking hints也是有可能被忽略的，當 hints 的欄位中包含計算欄位時，這些欄位是透過 function 進行計算在其他的表格中。

在更多的案例上，如果你注意到，hints 在使用上常常會有指定，但是也是有可能被忽視的，因為可能你的誤用或錯誤所造成。如果一個 hints 沒有使用到，檢查你的 hints 去看看是有仍有需要，藉以更正確的管理 hints 的使用。

相關文章：

你是否需要 SQL Server Query Hint ?

參考網址：http://www.sql-server-performance.com/2006/hints-general/
關鍵字：SQL Server、Hints、Performance Turning、效能調整

2011年6月21日星期二

SQL Server - 單獨設定 Linked Server 權限控制說明

最近接到需要進行SQL Server與Oracle的連接需求，想當然可以透過 Linked Server來進行解決，但是需要管控那些人可以存取，所以在權限上的設定就需要進行一些調整，而目前上可以設定那些使用者可以執行 Linked Server，無法單一設定是否可執行，所以Oracle上的帳號密碼就需要特別小心管控，以免利用此為跳板造成資料查詢上的漏洞，下列就介紹一下如何進行權限上的設定。

Linked Server 架構：

1、新增使用者，並設定最小權限：

2、因為此帳號不需太大的權限，所以請記得下列的 [sysadmin] 不要勾選，要不然也會直接給予Linked Server執行的權限，也等同DBA的權限。

3、由於只有簡單的查詢資料而已，所以建議可以只要再勾選設定db_datareader即可。

4、因為在開啟Linked Server的單獨權限時，需要可以進行Master的權限，所以Master的部份只需給予預設的 [public] 的權限，其他的權限都不用給。

5、新增完成使用者後，由於只有最低權限，雖然可以看到Linked Server，但是當要展開細部的資訊時，由於沒有執行的權限，但是執行後會看到下列的警告訊息：

6、最後執行下列的指令後，就可以讓使用者 [caryhsu] 執行Linked Server了。

Use master
GRANT EXECUTE ON sys.xp_prop_oledb_provider TO [caryhsu]
GO

關鍵字：SQL Server、Linked Server、Linking Server、連結伺服器、最小權限設定

2011年6月15日星期三

資料庫管理員必學技巧 - 磁碟機寫入快取對資料庫系統的影響

在許多人不斷的追求資料庫效能的提升，而對資料庫影響最大的莫過於磁碟I/O的效能，磁碟上的快取可以有效的提高I/O的效能，降少I/O之間的運作，但是這多人卻乎略了許多小地方，在資料庫中最重要的應該是四個基本原則 ACID (不可部分完成性、一致性、隔離性以及持久性)，而目前大部份的資料庫系統都是使用「預寫式記錄」(Write-Ahead Logging，WAL) 通訊協定進行，而磁碟機快取通常要依賴電容器，而不是電池供電方案，所以當跳電造成系統關機時，可能造成資料的不完整或毀損，而在Windows上磁碟寫入快取預設是啟用的，所以在此特別介紹磁碟寫入快取的影響與如何進行磁碟快取的設定。

預寫式記錄 - Write-Ahead Logging 說明：
http://twpug.net/docs/postgresql-doc-8.0-zh_TW/wal.html

在關閉之前先讓我們了解一下磁碟快取對於I/O的差異有多少，我使用二種方式進行測試，第一種是利用 SQL Server 的 Store Procedure 透過迴圈寫入表格並執行100,000次，每次約寫入30~50 byte，然後得到執行時間，而第二種的話，我就透過硬碟測試軟體 - HD_SPEED測試，在切換磁碟寫入快取的兩種情況各執行3分鐘，有磁碟寫入快取的情況下，效能約皆在100~120MB左右，而沒有磁碟快取的測試下，在經過一分鐘以後，就會馬上從原本的每秒100MB左右，掉至每秒只剩下10MB左右，而且就一持維持爬不上來了。

第一種 (測試實測對資料庫的Insert影響效能)：
a. 啟用磁碟寫入快取(預設)
32分38秒
b.停用磁碟寫入快取
33分43秒

第二種 (用磁碟效能測式軟體進行測試)：
a.啟用磁碟寫入快取(預設)時的測試結果：

b.停用磁碟寫入快取時的測試結果：

基於上述理由為了提高資料的完整性最好是關閉磁碟寫入快取，但是關閉寫入快取會讓磁碟的I/O效能降低，所以在下列三種情況下是可以維持寫入快取的啟用。

1、磁碟本身有獨立穩定完整的電源供應設備，如 Network Attached Storage 等。

2、資料本身對於完整性較不重視，如單獨放置 tempdb 的磁碟等。

3、在電源設備上提供更完整的備援，如不斷電系統(UPS)等。

以下說明如何進行磁碟寫入快取的設定：
PS:在Windows 7的測試下可以不用重開機即可啟用。

總結：針對以上的測試，其實磁碟寫入快取在使用上，關閉後對效能還是有一定的影響，但為了資料的完整性，中間的取捨，只好靠各位DBA的智慧與努力了。

參考網址：http://support.microsoft.com/kb/234656/zh-tw

關鍵字：Disk Cache、SQL Server、Write-Ahead Logging、磁碟寫入快取

2011年6月14日星期二

不同資料庫的比較 - SQL Server vs Oracle and MySQL

市面上有許多資料庫，較常聽過如Microsoft SQL Server、Oracle、MySQL等，當然還是有其他許多的資料庫，但是我就不一一列入說明，就以我常使用的類型進行比較，當然我是以我使用的情況進行簡單的比較，謝謝大家了。

就比較的方面我分成幾個不同的面向進行討論，如效能、整合度、全面性、高可用性等進行比較，但是在比較之前還是說明在各家產品的使用上不一定如我所說，就我也有看過將MySQL調到速度絕佳的狀況，連其他人建立的高價資料庫也比不上，也有人將Oracle使用到跟Access差不多，所以沒有一定的情況，主要還是看資料庫管理師的功力(雖然程式設計師也需要負上責任)，在此我就先以不同的層面來加以說明。

1、價格：
在價格上就以Oracle的成本最高，而計費方式是以CPU計價，1顆CPU如果為8核心的話，會以單價*8的方式進行，而SQL Server的話也是以CPU計價，但如果1顆CPU有8核心的話，會以8 * 0.5 * 單價的方式進行，而MySQL大家一直以為是免費的，但是當你應用在商業行為時，還是需要收取一定的費用，最高版本的 [MySQL Cluster Carrier Grade Edition Subscription (5+ socket server)] 也要美金20000元，其實在商品導入的初期在Oracle與SQL Server都有免費版的可以使用，而且也可以用在商業上，雖然功能上會有其一定的限制，但是未來升級會非常的簡單容易，很可惜知道的人很少，關於費用上大家可以參考下列的網址：

2、效能：
就三種資料庫在比較上，依據 TPC 的分析結果，其實就以Oracle處理效能最快，而就以每秒交易量與價格比較的話，就以SQL Server最有效益，而MySQL也有不錯的表現。

PS: TPC(Transaction Processing Performance Council)是一家位於美國加州聖荷西市的非營利組織，專門執行商用負載標竿測試(benchmarks)，並將性能數據公布給產業。

參考網址：http://www.tpc.org/

3、平台整合度：
SQL Server只能運用在Windows的平台上，而且在此平台上就屬SQL Server運作的最好，而且速度最快，而其他的二種資料庫皆可以達到跨平台的運用，而在Unix 與 Linux都有非常高的穩定度與效能，所以平台的選擇對於後續資料庫的影響也有非常大的關聯。

4、程式語言整合度：
三種資料庫其實對於任何一種程式語言都可以進行存取與使用，但是如同上一個段落的描述，SQL Server與.Net配合的最好，而Java大部份會選擇Oracle、PHP也大部份選擇MySQL，各有各的選擇，但再次提醒資料庫連線時有連線等級的區分，如你使用ODBC的驅動程式來連線，雖然可以連線，但是會無法發揮資料庫的完整特性與效能，所以建議安裝原廠的驅動程式，藉以得到較佳的效能，如ASP.NET要連線到Oracle時，就需要額外安裝 ODP 的驅動，而Java也有分成Thin、OCI與KPRB等，所以在連線上還是需要注意，藉以發揮資料庫的完整效能。

5、工具整合性：
Microsoft SQL Server在安裝後其實有包含非常多的工具可以應用，我想這也是非常多人喜歡SQL Server的其中一個原因，因為不管報表、資料轉換(ETL)、商業智慧(BI)等，一應具全，都是可以免費使用，而他所包含的工具其完整性與功能也與許多商業軟體不相上下，如報表中的Reporting Service就是一大特色，一般大家最常聽到或使用到的就是Crystal Reports，但是此軟體的價格非常的貴，所以常常會造成專案在軟體授權上的成本，進而影響客戶購買的意願，而兩者在功能上與圖表上的差異，可以參考我的另一篇著作(Crystal Report與Reporting Service圖表種類比較)，相信可以得到進一步的說明。

另外在ETL主要是透過的部份是透過SSIS來進行，而這套軟體在功能上、穩定度與轉換的速度上更是備受肯定，當然尤其在其他資料庫轉入到SQL Server時，效果更是明顯，目前的記錄是1.18TB的資料量只需要30分鐘以內即可完成，甚至可以媲美許多ETL的大型軟體。

PS：關於SSIS資料轉換的速度比較，可以參考此網址 - http://blogs.msdn.com/b/sqlperf/archive/2008/02/27/etl-world-record.aspx

而在BI的區塊中，其實在SQL Server 2005以後就有非常大的進展，特別是Data Mining演算法的區塊上，更是增加了非常多的種類，讓你在進行資料分析上更可得心應手。

其實就管理層面上來看，其實我個人管理了這麼久，其實坦白說並沒有使用到其他第3方的工具，因為SQL Server本身提供的工具與資訊就可以讓我很容易的進行資料庫的管理，所以認人有值回票價的感覺。

在其他二種的資料庫在安裝後的初期其實就只有簡單的介面，而有使用Oracle的大部份會再另外購買TOAD來管理，當然費用上也是不便宜，但是Oracle在第三方軟體的支援度與選擇性上也是最高的，所以這也是大部份廠商建議選擇的原因之一。而MySQL本身的初期工具應該算是最少的，而在第三方軟體的支援度上也有限，所以真的是大大的考驗了DBA的能力。

6、高可用度：
在三種資料庫上其實對於高可用度的支援都非常完整，所以個人認為不相上下，但是Oracle在負載平衡(Load Balance)上有支援RAC的架構(如下圖)，所以在負載平衡上看起來算是略勝一籌(但是仍需要確認你是否真的需要此功能，詳細介紹也可以參考我的另一篇文章 - SQL Server 負載平衡架構介紹 - Load Balancing)，而其他兩種的資料庫在負載平衡的部份，都是透過複寫的機制完成，關於SQL Server的說明，同樣的請參考 (SQL Server 負載平衡架構介紹 - Load Balancing)，而MySQL的架構如下圖所示，但是另外提醒一點，目前MySQL Proxy仍在測試階段，所以如果要使用在正式環境的話，還請多多考量。

MySQL 負載平衡架構

7、介面操作性：
三種資料庫軟體上，就以Microsoft SQL Server 的介面操作最容易使用，而且裝好後不需要太多的設定就可以進行，不管是SQL的設計，或是管理的功能一應俱全，而且許多進階的功能如HA的設定等，也是透過工具的方式設定的即可，非常的容易上手。

但就我認為也造成了另一個不好的使用習慣，因為太容易使用了，所以在資料庫建置時的事前規劃通常不太完整，就直接的進行，我也曾經看過某系統整合大廠在協助建置時，也是頂多規劃Cluster而已，其他內部的設定值也是照預設進行，到後來效能不足時，才怪說SQL Server的效能差、不實用等，但其實個人認為裡面的學問非常的多，可以調整的項目也非常的多，如同我前面所提，不管是什麼樣的資料庫都需要DBA的細心照顧與調整，並非一簇可成，而詳細的設定方法，可以參考我另外一篇翻譯的文章進行了解 (SQL Server 儲存設備 (Storage) 最佳調整作業)，或是留言與我討論都可以。

8、安全性：
在這個部份的比較，我覺得三者都差不多，不管是在帳號管理、資料傳輸、備份加密等，都有非常好的表現，但對於微軟官方針對安全性的部份有說到，他們每年推出的更新次數相對少非常的多，不像其他的資料庫，但坦白說這個可能關係到，更新的大小，錯誤修正的速度等問題，我也沒有詳細的針對三者進行細部的比較，所以不敢妄下定論，但是在SQL Server 除了在SQL Server 2000的蠕蟲事件後，就非常少的傳出重大漏洞的消息，而就個人使用的情況也無重大的問題發生。

總結：
針對三個資料庫，我只是針對我的了解與在網路資訊進行分析，沒有絕對的勝負之分，個人認為還是取決於你使用的技術、平台架構與DBA的功力，以上的資訊也是提供給大家參考，如果需補充的部份，還請不吝指點，謝謝。

關鍵字：SQL Server、Oracle、MySQL、Database、比較資料庫、SQL Server vs Oracle、SQL Server vs MySQL

訂閱：文章 (Atom)

2011年7月1日 星期五