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"

Begin Transaction 會不會影響 SQL Performance?

今天同事剛好問我這個問題,這個問題很有趣,也被很多人討論過。 首先,我們需要了解到 MS SQL的Default是Auto Commit,意思就是說縱使你沒有Commit Transaction,你的交易還是會被自動Commit。這點和Oracle不太一樣,算是MS SQL的特色之一(?)

我們先講結論,如果再大量Insert的時候加入Begin Transaction,效能會遠比沒有加好。

Why?–原理很簡單,假如沒有加入Begin Tran,因為MS SQL Default 是 Auto Commit,因此每Insert一筆資料Log都會有Begin Tran, Commit的紀錄,如果有十萬筆就會有十萬筆的Begin Tran, Commit。 如此一來效能就會很差….

所以如果在一開始加入Begin Tran,在Insert 十萬筆資料後再Commit,就會節省十萬筆的Begin Tran, Commit。所以效能就會好很多。

繼續閱讀 “Begin Transaction 會不會影響 SQL Performance?"

設定Instant Initialization– What, Why and How?

為什麼要設定Instant Initialization?

我們先由以下的例子來說明為什麼在一開始Config Server的時候需要設定Instant Initialization。網路上已經有很多文章實際模擬了,所以我這邊直接引用文章

Performance Test with Zero Initialization
Hardware: Dell Precision 670 Dual Proc (x64) with Dual Core, 4 GB Memory, RAID 1+0 array w/4-142 GB, 15000rpm disks
CREATE DATABASE with 20 GB Data file = 14:02 minutes
ALTER DATABASE BY 10 GB = 7:01 minutes
RESTORE 30 GB DATABASE (EMPTY Backup) = 21:07 minutes
RESTORE 30 GB DATABASE (11GB Backup) = 38:28 minutes

Performance Test with Instant Initialization
Hardware: Dell Precision 670 Dual Proc (x64) with Dual Core, 4 GB Memory, RAID 1+0 array w/4-142 GB, 15000rpm disks
CREATE DATABASE with 20 GB Data file = 1.3 seconds
ALTER DATABASE BY 10 GB = 0.4 seconds
RESTORE 30 GB DATABASE (EMPTY Backup) = 5 seconds
RESTORE 30 GB DATABASE (11GB Backup) = 19:42 minutes

繼續閱讀 “設定Instant Initialization– What, Why and How?"

Lock Resource by METADATA DATABASE_PRINCIPAL

最近在Production Server上遇到了一個Case,Change dbowner的Action做過很多次,第一次遇到Block的問題,最後理解到是Resouce:METADATA.DATABASE_PRINCIPAL被Lock,但是卻找不到太多相關的資訊,最後要特別感謝Phil Liu的幫忙與指導,才能了解到底層如何去運作。

首先在問題發生後,我利用Whoisactive查看我當時蒐集的資訊:
2017-12-01_174735.png

繼續閱讀 “Lock Resource by METADATA DATABASE_PRINCIPAL"

Replication Recovery Mode 一定要用Full Mode ?

有很多人對於SQL Replication有錯誤的觀念,認為只有Full Mode支援,事實上無論是Full, Bulk-Copy, Simple 都不會影響到SQL Replication。我們可以參考以下微軟官方的說明:

What recovery model is required on a replicated database?

Replication functions properly using any of the recovery models: simple, bulk-logged, or full. Merge replication tracks change by storing information in metadata tables. Transactional replication tracks changes by marking the transaction log, but this marking process is not affected by the recovery model.

繼續閱讀 “Replication Recovery Mode 一定要用Full Mode ?"

Who is Active 介紹

Whoisactive 由MVP Adam Machanic提出,從15個DMV收集數據。每個DMV可以顯示的大量有價值的資訊。sp_who和sp_who2亦可以顯示大量的資訊,但是必須熟悉和有經驗才能妥善的運用,whoisactive減低了這個門檻。

Whoisactive抓到的資訊是RealTime的,邏輯可以參考下圖,例如每一分鐘抓一次資訊,因此T1-T0=1min, T2-T0= 2min。因此可以從此去了解每個SPID的Wait Type轉變過程,也可以從中找出在等待什麼資源,進而了解過去DB遇到什麼瓶頸。

 

繼續閱讀 “Who is Active 介紹"

What Are Threads(執行儲)

為什麼需要了解Threads?

Threads為SQL底層運作的基礎,SQL的工作執行是由SQLOS layer of the Storage Engine來主導,其工作運作的最小單位為Threads,在執行的過程中,SQL會不斷的調度資源,可能是從IO,可能是從CPU,當資源有限時會造成等待資源的狀況出現。因此了解目前DB在等待什麼資源(Waittype)是一種對資料庫調校的科學方法,可以藉此知道資源瓶頸發生在哪裡。而了解各種Waittype之前的基本功,就是先了解Thread和其運作方式。

  • Thread為Process執行的單位
  • 一個Process裡面可以存在多個Thread
  • 每個Thread會有一個極小的等待時間,而其等待執行的動作為Scheduling
  • SQL Server系統會分配CPU資源給Thread使其執行命令,為Worker Thread
  • 有一些Threads是專用的,例如執行 Checkpoints, deadlock monitoring。
    一搬來說,大部分的Threads在Pool裡面等待,直到有新的Requests
  • SQL Server 不仰賴Windows scheduling,他有自己獨立的一套工作模式,叫做non-preemptive scheduling,其效率遠比仰賴Windows scheduling高。
  • 底層由SQLOS layer of the Storage Engine來主導
  • 每一個Core(無論是實體還是邏輯)都擁有一個Scheduler (有8個Core就有8個Scheduler)
  • 每個Scheduler負責管理每一個Threads的Execution
  • sys.dm_os_schedulers DMV可以用來查詢schedulers

繼續閱讀 “What Are Threads(執行儲)"

Trouble Shooting by Wait type

前陣子有幸參加SQL PASS的課程,由顏瑞宏老師所安排如何利用Wait type去進行Monitor和Trouble Shooting,收穫良多,以下的筆記來自於顏老師的上課講義。

什麼是Waitype?

  1. SQL Server的開發人員自行定義並用於診斷效能瓶頸
  2. 早期的Wait Type總類較少,隨著版本替換,總類上升
  3. Wait Type的執行狀態分為執行中(Running)、等候中(Waiting)、即將啟動(Runnable)
  4. 主要的Wait Resource總類可以分為下列幾種
    a. Resource e.g. I/O, Network, Thread, Memory..
    b.Synchronization e.g. Lock, Latches..
    c. Forced e.g. Yield, Sleep
    d. External e.g. Preemption
    e. Queue e.g. Background tasks
  5. 而在OS的上層SQLOS有權衡量TASK的作業優先權, 對於Wait Type的運作底層知識則可以參考Phil Liu寫的文章:
    http://sqlworker.blogspot.tw/2017/04/sql-server-waits.html
    http://sqlworker.blogspot.tw/2017/05/sqlserver-wait-typesosscheduleryield.html
  6. 不是所有Wait Type都代表系統瓶頸,有很多是背景工作的Waits
    e.g. LAZYWRITER_SLEEP, SQLTRACE_BUFFER_FLUSH, LOGMGR_QUEUE, CHECKPPOINT

繼續閱讀 “Trouble Shooting by Wait type"