SQL Server - 初使連線過慢問題處理

        最近接到一個Case，客戶表示他有多台電腦，但是有特定的電腦連線到SQL Server時都會特別的久，但是其他的電腦都不會，而且透過電腦名稱(Host Name)或IP結果都相同，而且當下觀察Server與Client端的資源與效能都很正常，所以當下猜想，或許與網路有相關，這個是問題最後是透過網路封包分析後才找出問題並解決的，所以在此將這個案例分享出來。

        由於網路的部份可能牽涉會有很多的層面，所以進一步與客戶確認，如是否為同一網址，防火牆的設定等，最後決定透過網路封包分析的方式進行確認，所以我就請客戶透過Netmon的程式進行兩邊封包的收集，封包分析如下：

PS：netmon是Microsoft所推出的一套免費的封包捉取與分析的工具，大家可以透過下列的網址進行下載，也請大家多多利用。

<br>

下載連結：
Microsoft Network Monitor 3.4
http://www.microsoft.com/en-us/download/details.aspx?id=4865


問題分析：
1、在有問題的電腦封包上發生在一開始的初使連線都需要等待5秒的時間，才可以與Server建立連線。

2、在正常電腦上的封包，發現很快的就可以建立連線。

3、在Server上的封包看到也是相同需要5秒的時間才可以正常的連線。

4、確認主機上的效能資訊，看起來也是正常。

解決方式 :
根據上述的分析，與網路Team的同事討論後，懷疑可能是系統網路中的一個New Feature所造成，後來嘗試下列的步驟將此功能關閉後，問題就解決了。

Set the EnableTCPA registry entry to "0" on the SQL Server computer.
To do this, follow these steps:
1.      Click Start, click Run, type regedit, and then click OK.
2.      Locate the EnableTCPA registry entry under the following registry subkey:
         HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
3.      Right-click EnableTCPA, and then click Modify.
4.      In the Value data box, type 0, and then click OK.
5.      Exit Registry Editor.
6.      Restart Computer.


參考知識文件：
A Windows Server 2003-based computer responds slowly to RDP connections or to SMB connections that are made from a Windows Vista-based computer
http://support.microsoft.com/kb/947773


關鍵字：SQL Server、Network Monitor、Network Packet、SQL Server Connectity

歡迎參加 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 Server、SQL PASS、Professional Association for SQL Server

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、性能分析、性能優化

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

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