SQL Replication Agent Profile 效能調教

高頻交易在使用交易式複寫時,如果使用的Agent Profile是Defaut值,會有效能問題。 這篇文章會介紹用UI以及用TSQL的作法調整Agent Profile,使得交易式複寫獲得效能改善。

每個Subscription都有自己的Distribution Agent,因此不同的Subscription都可以調整不同的Distribution Agent Profile。

但是Log Agent 則是一個DB只有一個。所以會有很多Publication共用同一個Log Agent。

繼續閱讀 “SQL Replication Agent Profile 效能調教"

[BUG] OBJECTSTORE_LBSS

這次遇到的問題,大概查了將近兩周才查出結果,OBJECTSTORE_LBSS這個Waittype在官網和網路上都找不到太多的資源。

遇到的問題:

突然發現有大量的Session 都在等Latch(ACCESS_METHODS_ACCESSOR_CACHE)的資源,並且造成效能低落。往Latch和 ACCESS_METHODS_ACCESSOR_CACHE查都沒有太多的方向。

檢查了一下Memory發現有異常:

嘗試Reboot SQL Server Service,會維持正常一段時間,不久後發現上圖中的Memory (Other) 會持續的上升且不會釋放。

用Script去找發現占用Memory的資源是 OBJECTSTORE_LBSS,並且會不斷成長。

找問題的途中有請教過一些高手,往Page fault 和cache hit也沒有找到異常,一般如果記憶體有物理上問題這幾兩個數值會有異常。

此外,有發現TempDB的IO似乎數值有異常,在做TempDB的調教後也沒有任何改善,因此又重新把目標定為 OBJECTSTORE_LBSS。

找到一篇文章 提到過去曾經有一個是因為 TVP 和SQL Profiler造成的BUG,如下圖,假設TVP使用到大量的Varchar(max)並同時開啟SQL Profiler,會造成這個問題。但是我目前環境的版本是SQL Server 2014 SP2,理論上應該不會遇到這個BUG。

由於,已經思無所路,所以我變嘗試將 SQL Profiler 關掉來驗證是否是一樣的問題。

-- Get the trace id
SELECT * FROM ::fn_trace_getinfo(default) WHERE property = 2;

--Stop AND Close trace
DECLARE @TraceID int
SET @TraceID = ?
EXEC sp_trace_setstatus @TraceID, 0
EXEC sp_trace_setstatus @TraceID, 2

關掉 SQL Profiler 後,問題就解了。因此應該真的是 SQL Profiler 和TVP造成的問題。於是我開始查詢微軟是否有相關的文章,後來查到這篇文章,裡面確實提到SQL Profiler和 TVP以及會出現OBJECTSTORE_LBSS的問題。可惜的是Hotfix並沒有上到SQL SERVER 2014。

Cross-Database Access Permission Control – Certificate Signing

Cross Database 牽涉到Ownership Chaining,理論上只要Cross Database就會需要給予額外的權限設定,否則SQL Server會Return權限不足的Error,大致上可以利用以下幾種方法來給與權限:

  1. 直接給予物件權限
  2. 憑證授權 Certificate Signing to Store Procedure
  3. 啟用 Trustworthy On

這篇主要會探討如何利用憑證授權 Certificate Signing來達到Cross DB交易的目標。若是對Trustworthy不清楚,建議可以從上述連結了解。使用 Certificate Signing 有幾項優缺點

繼續閱讀 “Cross-Database Access Permission Control – Certificate Signing"

升級SQL 2014,統計資訊(Statistics)的重要性?

統計資訊到底重不重要?

很多人都會說統計資訊的正確性很重要,因此要定時維護。事實上在SQL Server 2008的時候,我都仰賴自動更新而沒有設過任何排成維護。但是我在升級SQL Server 2014時,吃了一個大悶虧是關於統計資訊。原先CPU 好好的,升級後突然CPU高到接近100%。

自動更新的Option如下圖
「sql statistics auto update option」的圖片搜尋結果

先說說為什麼維護統計資訊很重要

  1. SQL Server產生執行計畫Execution Plan會依據統計資訊產生
  2. 所以不好的統計資訊會讓SQL Server誤用執行計畫
  3. 要特別注意統計資訊更新後會促發執行計畫重新編譯 (Recompile)

繼續閱讀 “升級SQL 2014,統計資訊(Statistics)的重要性?"

Connection String 中的 Failover Partner Parameter

相信大家都知道Connection String可以設定Failover Partner在有Mirroring的環境上。當主體伺服器發生問題時,能夠Failover連線到另外一台鏡像伺服器。

但是大家對於鏡像環境建立Connection String的Failover Partner Parameter通常都有不太清楚的理解。

理論上來說,當成功連接Principle SQL Server,data access provider 會向目前已連線的SQL Server下載Failover Partner在Client端並Cache,之後,.NET Framework Data Provider for SQL Server 就不會更新Failover Partner。 然而,假設Principle SQL Server突然掛了,Client連線不到Principle SQL Server,後續則會透過Connection String中的Failover Partner Parameter連接SQL Server,假如此時和Failover Partner Parameter中的SQL Server連線順利建立,SQL Server則會傳遞【新】的Failover Partner給Client端並Cache。

所以其實有兩種情況

  1. 當連線順利建立,data access provider 會向目前已連線的SQL Server下載Failover Partner
  2. 若第一次連線無法建立成功,則 data access provider tries透過Connection String中的Failover Partner Parameter試著連線,若是連線成功,則會向目前已連線的SQL Server下載Failover Partner

繼續閱讀 “Connection String 中的 Failover Partner Parameter"

Set up SQL Replication by Remote Distribution & Domain Account Step By Step

建立SQL Replication可以選擇 Local Distribution 或是 Remote Distribution,兩種架構各有他的優缺點,這篇會一步一步教大家如何設定Remote Distribution。

使用 Remote Distribution代表將Publisher和Distributor分開在不同的Instance上,同時代表工作量就會被拆分在Publisher 和 Distributor上。利用這個架構可以改善SQL Replication的效能。缺點是必須多維護一台Distribution Server,並且花比較多時間設定,優點是可以減少Publisher的負擔。如果有很多Publisher和Subscriber,其實利用Remote Distribution這個架構或許是一個不錯的考量。 如果使用此架構也需要同時考量到Network, IO的影響。

繼續閱讀 “Set up SQL Replication by Remote Distribution & Domain Account Step By Step"

SQL Server Agent Invokes Failed ‘sa’ Login

這篇分享是從SQL PASS的討論整理出來的,社團的前輩真的很厲害,一下就找到原因,並且發現這是SQL的BUG…

苦主環境為: SQL 2017 + CU6

狀況如下:
使用維護計畫(Maintenance plan )刪除過期被份檔+做交易記錄備份,會在Event Viewer不斷出現【使用者 ‘sa’ 的登入失敗。 原因: 密碼與提供的登入密碼不符。】。此狀況再從sa改為windows驗證就正常。

Login failed for user ‘sa’. Reason: Password did not match that for the login provided. [CLIENT: ]

繼續閱讀 “SQL Server Agent Invokes Failed ‘sa’ Login"

SQL 2014 Database Mail 導致 high CPU

某一次升版到SQL Server 2014後,CPU開始不尋常的升高,但是SQL Server不忙,看了一下Task Manger發現DatabaseMail占用很多資源。

2018-05-16_111943.png

於是上網一查,發現這個問題在SQL SERVER 2016也有,是個BUG…….
https://support.microsoft.com/en-nz/help/3197879/fix-sql-server-2016-database-mail-causes-high-cpu-usage-after-many-ema

2018-05-16_112456.png
繼續閱讀 “SQL 2014 Database Mail 導致 high CPU"