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 (台北市信義區松仁路七號七樓)舉辦第一次的聚會,讓好朋友們可以碰個面,彼此認識一下。

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

  1. 介紹 SQL PASS 社群
  2. 大家對 SQL User Group 的期待
  3. 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 ServerSQL PASSProfessional 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;


鎖定類型參考:
LastwaittypeWaittype描述
LCK_M_SCH_S0x01結構描述穩定性
LCK_M_SCH_M0x02結構描述修改
LCK_M_IS0x03轉譯分享
LCK_M_SIU0x04要更新的轉譯分享
LCK_M_IS_S0x05共用轉譯分享 (機碼範圍鎖定)
LCK_M_IX0x06轉譯排除
LCK_M_SIX0x07共用轉譯排除
LCK_M_S0x08共用
LCK_M_U0x09更新
LCK_M_II_NL0x0A轉譯插入 NULL (機碼範圍鎖定)
LCK_M_II_X0x0B轉譯插入排除 (機碼範圍鎖定)
LCK_M_IU0x0C轉譯更新鎖定
LCK_M_IS_U0x0D轉譯分享更新 (機碼範圍鎖定)
LCK_M_X0x0E排除
LCK_M_BU0x0F大量更新


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


關鍵字:SQL ServerDMVDynamic Management ViewsPerformance 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 ServerUSEsp_executesqlDynamic 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 ServerLinked Server連結伺服器Microsoft.ACE.OLEDBMicrosoft Access Database EngineMSDASQL

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進行通知。

3-6 點選到 [回應],然後勾選 [通知操作員],選擇通知那些操作員,並勾選 [電子郵件]。

3-7 點選到 [選項],然後請勾選 [電子郵件]

4、測試與確認收件格式

4-1 如果你安裝 的版本為SQL Server 2012 RTM (11.0.2100.60)時,你在收到郵件時會遇到下列的問題,這個已確認為產品問題,再請安裝下列的Fix,或是安裝SQL Server CU3進行即可。


個別修正:
FIX: Incomplete Performance Condition alert in SQL Server 2012

累計更新:
Cumulative update package 3 for SQL Server 2012


4-2 正確的郵件格式

本章的功能可以大大的減輕DBA管理員的負擔,可以在同時管理多台主機的情況下,又可以兼固第一時間知道系統的異常,所以大家一定要多多的利用。

關鍵字:SQL ServerSQL Server Alert警示SQL Server Agent資料庫警示自我警示