CaryHsu - 學無止盡: 11月 2012

2012年11月26日星期一

歡迎參加 SQL PASS User Group Taiwan 聚會

以下內容感謝德瑞克同意分享：德瑞克：SQL Server 學習筆記

2012年11月成立 SQL PASS User Group Taiwan。<

第一屆會長由 SQL Server 宗師：胡百敬先生擔任。

歡迎各位夥伴前來參加 SQL PASS User Group Taiwan 聚會

認識 SQL PASS
Professional Association for SQL Server (PASS) 社群已經成立十幾年，應該是全球最大的 SQL Server 使用者社群。致力於 SQL Server 推廣與教育，以志工組成沒有營利，這也須貫徹到全球各個分部。

我們將在 12/1(六) 號 1:30 PM-4:30 PM 藉由台灣微軟總部 7F (台北市信義區松仁路七號七樓)舉辦第一次的聚會，讓好朋友們可以碰個面，彼此認識一下。

並針對以下議題稍做說明：

介紹 SQL PASS 社群
大家對 SQL User Group 的期待
SQL Server 的現今與未來

有興趣的朋友都可以自由參加。
報名網頁：http://sdrv.ms/Uitd57

我們希望在台灣能成立 SQL Server 使用者的社群，以互相幫忙與分享。
讓大家不管在工作技術、或職場生涯都有聯繫與依靠。

但由於沒有經營社團的經驗，除了跟 SQL PASS 學習與借助其平台外，還請各位朋友告知 SQL 使用者社群可做些什麼。

台灣 SQL PASS 社群
https://www.facebook.com/sqlpass.tw?fref=ts

參考資料 Professional Association for SQL Server (PASS) 社群
http://www.sqlpass.org/

台灣 SQL PASS 社群
https://www.facebook.com/sqlpass.tw?fref=ts

SQL PASS User Group Taiwan 成立 http://byronhu.wordpress.com/2012/11/21/sql-pass-user-group-taiwan-%E6%88%90%E7%AB%8B/

關鍵字：SQL Server、SQL PASS、Professional Association for SQL Server

2012年11月21日星期三

SQL Server - 如何透過DMV進行效能評估與監控

SQL Server有許多監控的方式，較常用的就是透過活動監視器(Activity Monitor)觀察目前使所有的Session，藉以找出那一個Session使用的CPU、IO或執行最久，但是從SQL Server 2005之後，就推出了DMV(Dynamic Management Views)可以找出從SQL Server開機以後到目前的資訊，所以只要有效的利用這個資訊，在系統發生問題時，就可以利用這項功能有效的找出。

底下我就透過一個例子讓你可以藉以找出目前有問題的語法，當然我也整理了一些常用的DMV用法，但是在管理與分析上，其實不只是透過DMV之外，最好也可以透過Performance Counter進行交叉比對，藉以找出語法執行過慢的問題並加以加析。

事件模擬：
下列我會開一個視窗啟動一個交易，並於交易中新增一筆記錄，但是並不下Commit或Rollback，藉以模擬交易鎖定的狀態。


begin tran

insert into [test_db2].[dbo].[Table_1]

([name] ,[clsid])

values('caryhsu', newid())

另外再開一個視窗，查詢同一個表格，由於此表格並未執行Commit或Rollback，所以會有鎖定的情況發生，請執行下列的語法。


select * from table_1

接下來只有查詢此表格的查詢都會有上述的情況發生，所以接下來我們就透過DMV來進行解析，藉以找出問題。

1、列出目前在執行的Process中有遇到Blocked的部份。


select * from sys.sysprocesses

where blocked <> 0

從上述的查詢中可以看出，目前Session ID 53，被Session ID 54給鎖定住，鎖定類型為LCK_M_S，當然另外我們也要注意waittime的部份，如果鎖定的越久，代表影響到其他的查詢相對也會越久。

PS：請特別注意，透過上述語法查出資料時，並不一定就代表有問題，因為鎖定本來就是合理與預期的動作，但需特別注意的事，但如果查詢出來的資料中waittime過大時，那就代表需特別注意。

2、另外我們可以透過下列的查詢，找出目前系統中等待的前10名類型為何，而查詢出的wait_type可以到下列的網址查詢類型的說明。


select top 10 * 

from sys.dm_os_wait_stats

order by wait_time_ms desc

Wait Type參考連結：
sys.dm_os_wait_stats (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms179984.aspx

3、由於我們在第一個查詢知道Session ID 53被54給鎖定住，所以我們可以透過下列的語法找出Session ID 54正在進行的語法與內容。


dbcc inputbuffer(54)

透過上述的方式可以快速且簡單的找出目前的問題，另外下方我也提供了三個語句給大家，希望大家藉以分析並找出目前系統的瓶頸所在。

1、找出最耗用IO的語法


SELECT TOP 10

total_logical_reads,

total_logical_writes,

execution_count,

total_logical_reads+total_logical_writes AS [IO_total],

st.text AS query_text,

db_name(st.dbid) AS database_name,

st.objectid AS object_id

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

WHERE total_logical_reads+total_logical_writes> 0

ORDER BY [IO_total] DESC

2、列出目前最耗用CPU的前50個查詢


SELECT  TOP 50 

qs.total_worker_time / qs.execution_count AS[Avg CPU Time],

SUBSTRING(qt.text, qs.statement_start_offset / 2, 

(CASE WHEN qs.statement_end_offset = -1 THEN len(CONVERT (NVARCHAR (MAX), qt.text)) * 2

ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) AS query_text,

qt.dbid,

qt.objectid

FROM     sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt

ORDER BY [Avg CPU Time] DESC;

3、列出目前最耗用Worker Time的前50個查詢


SELECT   TOP 50 sum(qs.total_worker_time) AS total_cpu_time,

                sum(qs.execution_count) AS total_execution_count,

                count(*) AS '#_statements',

                qt.dbid,

                qt.objectid,

                qs.sql_handle,

                qt.[text]

FROM     sys.dm_exec_query_stats AS qs 

CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt

GROUP BY qt.dbid, qt.objectid, qs.sql_handle, qt.[text]

ORDER BY sum(qs.total_worker_time) DESC, qs.sql_handle;

鎖定類型參考：

Lastwaittype	Waittype	描述
LCK_M_SCH_S	0x01	結構描述穩定性
LCK_M_SCH_M	0x02	結構描述修改
LCK_M_IS	0x03	轉譯分享
LCK_M_SIU	0x04	要更新的轉譯分享
LCK_M_IS_S	0x05	共用轉譯分享 (機碼範圍鎖定)
LCK_M_IX	0x06	轉譯排除
LCK_M_SIX	0x07	共用轉譯排除
LCK_M_S	0x08	共用
LCK_M_U	0x09	更新
LCK_M_II_NL	0x0A	轉譯插入 NULL (機碼範圍鎖定)
LCK_M_II_X	0x0B	轉譯插入排除 (機碼範圍鎖定)
LCK_M_IU	0x0C	轉譯更新鎖定
LCK_M_IS_U	0x0D	轉譯分享更新 (機碼範圍鎖定)
LCK_M_X	0x0E	排除
LCK_M_BU	0x0F	大量更新

參考連結：
sys.sysprocesses (Transact-SQL)
http://support.microsoft.com/kb/244455/zh-tw

關鍵字：SQL Server、DMV、Dynamic Management Views、Performance Monitoring、性能分析、性能優化

2012年11月15日星期四

SQL Server - 如何動態的變更資料庫

最近接到一個需求，需要透過T-SQL進行檢示表(View)的建立，但是在程式中需要在A資料庫上建立在B資料庫上，相信大家都知道如何透過GUI的畫面與T-SQL變更目前的資料庫的方式，在T-SQL中，我們可以使用 USE 的關鍵字，但是如果要透過動態的SQL語法時，就無法正常的使用這個關鍵字，一但執行時會發生下列的錯誤訊息。

1、嘗式透過動態SQL的方式進行執行，但會遇到下列的錯誤。

原本的語法：


DECLARE @sql nvarchar(4000);



set @sql = 'USE Northwind '

set @sql = @sql + ' go '

set @sql = @sql + ' create view dbo.test_view '

set @sql = @sql + ' as'

set @sql = @sql + ' select * from customers;'



EXEC (@sql)

錯誤訊息：
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'go'.
Msg 111, Level 15, State 1, Line 1
'CREATE VIEW' must be the first statement in a query batch.

2、透過上述的語法，想說可能不可以在動態SQL中進行，所以直接在View的前面嘗試加上資料庫名稱進行，但仍然有下列的錯誤。

調整後語法：


DECLARE @sql nvarchar(4000);



set @sql = ' create view Northwind.dbo.test_view '

set @sql = @sql + ' as'

set @sql = @sql + ' select * from customers;'



EXEC (@sql)

錯誤訊息：
Msg 166, Level 15, State 1, Line 1
'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.

3、最後終於找到一個方法，也就是透過在內層同時執行USE與建立View的語法，即可解決這個問題。

修改後的語法：


DECLARE @sql nvarchar(4000);

DECLARE @sql_package nvarchar(4000);

DECLARE @dbName varchar(100);



set @dbName = 'Northwind';

set @sql = 'create view dbo.test_view '

set @sql = @sql + ' as'

set @sql = @sql + ' select * from customers;'



SET @sql_package = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @sql + '''';

EXEC (@sql_package)

參考網址：
sp_executesql (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188001.aspx
USE (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms188366.aspx

關鍵字：SQL Server、USE、sp_executesql、Dynamic SQL Command

2012年11月10日星期六

SQL Server - 文字檔控制

最近接到一個新的需求，需要將表格中的資料輸出到文字檔案中，方法其實有很多種，如透過 CmdShell 來進行，但可能會有資訊安全的疑慮，所以建議可以透過連結伺服器 (Linked Server)，但問題來了，在SQL Server 2008R2上，由於是64位元的應用程式，所以當要連線到文字檔如(*.txt、*.csv)或Office的檔案，如Excel時，就會發生下列的錯誤，所以本篇就來介紹解決這個問題。

錯誤訊息：
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

連結伺服器 "(null)" 的 OLE DB 提供者 "MSDASQL" 傳回訊息 "[Microsoft][ODBC 驅動程式管理員] 找不到資料來源名稱且未指定預設的驅動程式"。
訊息 7303，層級 16，狀態 1，行 1
無法初始化連結伺服器 "(null)" 的 OLE DB 提供者 "MSDASQL" 的資料來源物件。

接下來我們就來介紹如何透過連結伺服器進行：

1、建立連結伺服器
1-1 開啟Management Studio，點選[伺服器物件] -> [連結的伺服器] -> [新增連結的伺服器]

1-2 輸入連結的資訊。下列資訊中資料來源主要是指定您的檔案存放位置，本範例我是放在C:\LogFolder下面。

1-3 建立完成後，您就可以在連結的伺服器下看到剛剛新增的連結伺服器

2、安裝 Microsoft.ACE.OLEDB.12.0 的驅動程式與設定。
2-1 由於在SQL Server 64位元中如果要去存取32位元的應用程式時，會發生問題，請先進行下列檔案的安裝。

2-2 下載安裝Microsoft.ACE.OLEDB.12.0
Microsoft Access Database Engine 2010 Redistributable
http://www.microsoft.com/en-us/download/details.aspx?id=13255

2-3 安裝的時候，如果你的機器上已有安裝Office 32位元的程式時，就會遇到下列的問題，由於無法同時安裝32與64位元的應用程式與同一台上，所以請先移除後再安裝即可。

錯誤訊息：
You cannot install the 64-bit version of Microsoft Access Database Engine 2010 because you currently have 32-bit office products installed. If you want to install 64-bit Microsoft Access Database Engine 2010, you will first need to remove the 32-bit installation of Office products. After uninstalling the following product(s), rerun setup in order to install 64-bit version of Microsoft Access Database Engine 2010:

2-4 安裝完成後，請重新啟動電腦。

2-5 安裝完成後，您如果直接使用，然會遇到下列的問題，必須啟用下列的設定後，您才可以讓此驅動程式運作。


EXEC

master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess', 1

GO

錯誤訊息：

訊息 7399，層級 16，狀態 1，行 1
連結伺服器 "LOGMANAGER" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 報告了錯誤。提供者並未給予任何關於錯誤的資訊。
訊息 7330，層級 16，狀態 2，行 1
無法從連結伺服器 "LOGMANAGER" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 提取資料列。

3、使用連結伺服器進行測試。

3-1 接下來請在 C:\LogFolder 目錄下新增一個檔案，本範例名稱為test_doc.txt。
3-2 透過下列的方式即可將進行文字檔的新增與查詢

3-2-1 查詢文字檔：
select * from LOGMANAGER...test_doc#txt

3-2-2 新增文字到文字檔中：
insert into LOGMANAGER...test_doc#txt values('12345')

透過上述介紹的方式，你就可以在SQL Server中進行文字檔的控制，即可整合應用。

參考連結：
How to connect to file-based data sources (Microsoft Access , Microsoft Excel and Text files ) from a 64 bit application
http://blogs.msdn.com/b/sqlblog/archive/2009/12/29/how-to-connect-to-file-based-data-sources-microsoft-access-microsoft-excel-and-text-files-from-a-64-bit-application.aspx

關鍵字：SQL Server、Linked Server、連結伺服器、Microsoft.ACE.OLEDB、Microsoft Access Database Engine、MSDASQL

2012年11月8日星期四

自動化管理 - 透過警示功能進行監控

前面的文面已有介紹過多種進行SQL Server資料庫監控的方式，也有介紹到如何透過效能監視器進行監控，但這些都是屬於被動的方式進行，如果希望透過主動的方式，如當CPU、Memory、Disk等物件超過門檻值時，可以主動的通知管理者，如此一來就不用由管理者持續的監控資料庫也可以確認正常的運作，所以本章，我就來介紹如何透過SQL Server的警示功能來達到上述的需求。

1、郵件主機設定

如果你已有內部的專用郵件主機(Mail Server)的話，可以直接跳過本步驟。

1-1 選取功能 [SMTP 伺服器]

1-2 安裝完成後，請開啟 [Internet Information Services 6.0 Manager]，然後點選 [SMTP Virtual Server #1] -> 內容。

1-3 選擇 [存取] -> [轉送]

1-4 選擇 [僅限下列清單]，此時請將目前此台主機的IP加入後，即可完成設定。

2、設定Database Mail

2-1 點選 [Database Mail] -> [設定Database Mail]

2-2 選擇 [執行下列工作以設定Database Mail]

2-3 設定檔名稱輸入後，請輸入 [加入]。

2-4 在下列位置上輸入你的Mail與郵件主機的資訊。

2-5設定完成後，請點選 [傳送測試電子郵件]，藉以確認Database Mail設定正確。

3、設定警示功能

3-1 請確認SQL Server Agent為啟動中。
3-2 點選 [SQL Server Agent] -> [屬性]

3-3 點選 [警示系統] ，勾選 [啟用郵件設定檔] 後，在郵件系統選擇[Database Mail]，而在郵件設定檔，請選擇在第二個步驟中新增的設定檔 [profile_cary]。

3-4 點選 [警示] -> [新增警示]。

3-5 在下列選擇類型中主要有分成三類，分別為[SQL Server 事件警示]、[SQL Server 效能條件警示] 與 [WMI 事件警示]，此處我們以 [SQL Server 效能條件警示] 為主，選擇後即可將在 [效能監視器] 中以SQLServer開頭的物件加入，此處我們以 [Data Files(s) Size (KB)]為例，當資料庫的大小超過1024KB時，就寄出Mail進行通知。