How TVP Affect SQL Server Memory

之前有寫過一篇文章,其中的Root Cause就是TVP+SQL Profiler所造成的記憶體內存問題。原本以為tvp中的欄位如果沒有varchar(max)理應不會對SQL Server造成問題。但在測試結果下,如果Application沒有對字串限制長度,還是會造成上述的記憶體內存問題。同時也感謝Rock撥空測試幫助我驗證以下結論。

大概有幾個手段可以解決問題

  1. 限制Application 字串長度
  2. 升級CU Hotfix
//Sample Code for limit string length
DataSet dataSet = new DataSet();

DataTable customTable = new DataTable();
DataColumn dcName = new DataColumn("Name", typeof(string));
dcName.MaxLength= 500;
customTable.Columns.Add(dcName);

dataSet.Tables.Add(customTable);

至於原理跟測試,有興趣的可以看這篇文章

DataTable objects are most commonly used as TVP values. DataTables are easy to use and can serve as containers for data beyond just TVP usage. But unlike DbDataReader and IEnumerable objects, a big gotcha with a DataTable is that the default data type String with maximum length of -1 (2GB LOB). This is the .NET equivalent of the SQL Server nvarchar(MAX) data type and has many insidious and negative implications with a TVP.

SQL Server misaligned log IOs which required falling back to synchronous IO

There have been 27888384 misaligned log IOs which required falling back to synchronous IO.
某一次在Production換新機後,突然Secondary狂噴如標題的Error。
Google後發現可能是Disk sector不Align的問題。

使用下列語法查詢Disk的狀態。

fsutil fsinfo ntfsinfo
繼續閱讀 “SQL Server misaligned log IOs which required falling back to synchronous IO"

[BUG] OBJECTSTORE_LBSS

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

遇到的問題:

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

繼續閱讀 “[BUG] OBJECTSTORE_LBSS"

升級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)的重要性?"

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"

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"

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"

Missing Indexes for All Databases

一直以來都知道Execution Plan可以看Missing Indexes,但是並不知道其實SQL Server一直以來都有將Missing Indexes記錄起來,利用以下的Script可以找出每個DB中所缺失的Indexes,作為建立新的index的依據和考量。

SELECT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage],

migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],

mid.equality_columns, mid.inequality_columns, mid.included_columns,

migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact

FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)

INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)

ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)

ON mig.index_handle = mid.index_handle

ORDER BY index_advantage DESC OPTION (RECOMPILE);

-- Getting missing index information for all of the databases on the instance is very useful

-- Look at last user seek time, number of user seeks to help determine source and importance

-- Also look at avg_user_impact and avg_total_user_cost to help determine importance

-- SQL Server is overly eager to add included columns, so beware

-- Do not just blindly add indexes that show up from this query!!!

-- Refer Glenn Berry on Pluralslight -- Glenn@SQLskills.com -- GlennAlanBerry

繼續閱讀 “Missing Indexes for All Databases"