Browse by Tags

SQL 最大伺服器記憶體設定值研究

SQL Server 預設會用光主機所有記憶體(預設上限為 2PB = 2048TB)，除非整個資料庫容量小於總記憶體，否則把記憶體當成 Cache 能提升效能、減少磁碟 I/O 耗損，絕對利多於弊。如果同主機有多個資料庫執行個體，或是要與其他應用程式、服務分享記憶體，就必須透過 Max_Server_Memory 設定伺服器可用記憶體上限，保留一部分供其他程式使用。 最近被問到一個問題，針對專屬資料庫用途的 SQL Server 主機，剩餘記憶體的監控警戒值該怎麼抓？若依通用慣例抓 90% ，主機應該...

Posted 12 March 2018 10:18 PM 由 Jeffrey | 1 comment(s) 5,174

Filed under: SQL

筆記－T-SQL 分頁查詢並傳回總筆數

資料庫查詢結果要做分頁，我較常用的做法是拉到 C# 端將物件陣列或 List<T> 存入 MemoryCache，用 .Length 可以取總筆數，用 Skip(pageSize  * (pageNo - 1)).Take(pageSize) 取回指定頁數資料，換頁或排序時從 MemoryCache 讀取以求迅速並減輕資料庫伺服器負擔，遇到變更查詢條件或按查詢鈕時再重新查詢資料庫。 最近遇到的案例，因使用者較多、單筆資料量也大，擔心 Cache 消耗過多記憶體，決定改用 T...

Posted 23 August 2017 09:33 PM 由 Jeffrey | 3 comment(s) 6,582

Filed under: CODE, SQL

TIPS－調整 SQL Agent 作業記錄筆數限制

查詢 SQL Agent 排程執行問題時，發現一個狀況：使用 Log File Viewer 查詢作業記錄（Job History Log），大部分排程的執行歷程都是空的（如下圖），只有少數幾個排程有內容： 研究後學到一件事－SQL Agent Job History 有預設筆數限制，預設值為所有排程作業總共 1000 筆，每項排程作業最多 100 筆。在我們的排程作業中，有幾個排程每兩分鐘或五分鐘就執行一次，很快把 1000 筆配額用光光，因而產生作業記錄只剩下高頻率排程的記錄，超過一天的記錄全部被擠掉消失無蹤...

Posted 21 August 2017 11:14 PM 由 Jeffrey | with no comments 2,956

Filed under: SQL

SQL 資料轉 INSERT 語法－使用 Visual Studio

將資料表內容轉成一連串 INSERT 語法，是蠻好用的跨伺服器搬資料表招式，之前我最愛用的工具是 SQL Dumper ，但昨天聽到不幸消息：官方網站人去樓空，連註冊的 DNS 網域都已棄守。 重新尋找替代方案，找到三種做法： SSMS 內建 Generate Script 功能 接著透過 Wizard 介面指定資料表並設定只輸出資料（Types of data to script: Data only） 由於當初設計可搬移整個 DB，故介面跟選項有點繁瑣，另外無法自訂查詢條件是一大缺點。 SSMS...

Posted 15 August 2017 08:43 PM 由 Jeffrey | 2 comment(s) 4,804

Filed under: SQL

KB－當 WHERE AND/OR 條件遇上 NULL

在 SQL 世界裡 NULL 性質特殊，行為獨特，過去就曾討論過： KB-詭異的NOT IN查詢，原來是NULL搞鬼 ORACLE, 你把空字串怎麼了 自以為至此對 NULL 認識已足，不料前兩天在 WHERE LIKE AND/OR 情境中遇上 NULL，一時意志動搖陷入迷惘，想必是認知還不夠深刻，再補篇 KB 吧！ 先別急著看答案，大家猜猜以下 SQL 指令會得到什麼結果？ select 'T1' , 'MATCH' from dual where null like...

Posted 19 July 2017 11:04 PM 由 Jeffrey | with no comments 3,953

Filed under: ORACLE, KB, SQL

【茶包射手筆記】SQL 錯誤－Server 'XXX' is not configured for RPC

在測試台運作正常，程式部署到正式環境後出現 SQL 錯誤： System.Data.SqlClient.SqlException (0x80131904): Server 'XXX' is not configured for RPC. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)...

Posted 12 July 2017 11:38 PM 由 Jeffrey | with no comments 3,485

Filed under: Trouble-Shooting, SQL

Visual Studio 開啟專案出現 SQL Server Express 未安裝警告

在公司開啟某些工作專案時，我的 Visaul Studio 常會彈出像這樣的警告訊息： The Web project 'MyWeb' requires SQL Server Express LocalDB, whick is not installed on this computer. To upgrade the project database to use latest SQL Server Express LocalDB, double-click the database...

Posted 03 July 2017 07:47 AM 由 Jeffrey | with no comments 2,842

Filed under: ASP.NET, Visual Studio, SQL

查詢 SQL Server 詳細版本資訊

資安更新作業需確認 SQL Server 詳細版本資訊，需細到 Service Pack 及累積安全更新，爬文查到超好用的 SQL Server 版本資訊偵測腳本 。這組自動偵測腳本由微軟 OneScript Team 提供，可顯示版號、產品名稱、產品層級（Service Pack 版本）、版本別（Express、Enterprise、Developer…）、32 / 64 位元、Service Pack 版本、累積更新（Cumulative Update）等資訊。 更厲害的來了，偵測結果還包含後續若要更新...

Posted 08 March 2017 10:23 PM 由 Jeffrey | with no comments 6,444

Filed under: SQL

【茶包射手筆記】在 View 使用 SELECT * 的風險

分享同事踩到的 SELECT ＊ 地雷一枚。 大家應該在程式設計準則都看過這條－「避免使用 SELECT * FROM Table，應以 SELECT Col1, Col2… 明確列舉欄位…」。 如此建議必有其考量：第一個理由顯而易見，正向表列必要欄位，可避免在網路傳送用不到的資料浪費頻寬，並能減少客戶端、伺服器端處理多餘資料的資源損耗。再者，查詢欄位多寡也可能影響效能，SELECT * 時為牽就非必要欄位，資料庫可能改用較無效率的索引，不利效能最佳化。還有一種極端情境，若所需欄位都存在於 Non...

Posted 15 February 2017 10:26 PM 由 Jeffrey | 1 comment(s) 9,419

Filed under: SQL

再談T-SQL複合字串鍵值比對－借用PARSENAME()

以下是我實際遇到的情境，複合鍵在某些資料表拆成兩個或三個欄位，但在某些資料表則用"."或"-"串接存成單一欄位。（不要問我為什麼搞出這種不一致的設計，誰沒有過去？） 如以下的例子，在JStock資料表的Market及Symbol欄位，在JReport則使用一個FullSymbol欄位，存成"Symbol.Market"。 問題來了，如果這兩個Table要JOIN怎麼辦？過去用過一種鳥方法： JReport R JOIN JStock S...

Posted 07 September 2016 10:19 PM 由 Jeffrey | 2 comment(s) 5,697

Filed under: SQL

SQL效能調校經驗一則

使用者報案，某網頁效能變得奇慢無比，簡單的上線公告查詢耗時超過兩分鐘，追查後抓出問題查詢如下例： select case when convert ( varchar ,u.StartDate,108)= '00:00:00' and convert ( varchar ,u.EndDate,108)= '00:00:00' then convert (datetime, convert ( varchar ,u.EndDate,111))+1 else EndDate...

Posted 30 May 2016 10:23 PM 由 Jeffrey | with no comments 14,407

Filed under: Performance, SQL

對付SQL Injection，換掉單引號到底夠不夠？

雖然現在遇到使用者輸入條件查詢DB，我一律都用參數化查詢（順推超好用的 Dapper ）不再偷懶組裝SQL指令，但關於SQL Injection，我心中始終藏著一個疑問：流傳千古的… WHERE Col = '" + input.Replace("'", "''") + "'"換單引號大法，人人都知它不夠安全，但網路流傳一種說法，指稱換單引號只是自欺欺人根本無效，奇怪的是卻很少看到它被打穿的實例...

Posted 20 March 2016 11:22 PM 由 Jeffrey | 6 comment(s) 12,385

Filed under: Security, SQL

無法使用Windows帳號登入防火牆內的SQL Server

要穿過防火牆連上一台SQL（1433 Port有開，網路芳鄰NETBIOS封閉），發現用SQL帳號登入（SQL Authentication)可成功登入，若用AD帳號（Windows Authentication)則會出錯。 錯誤訊息為： 已超過連接逾時的設定。在嘗試使用登入前的信號交換確認時超過逾時等待的時間。這可能是登入前的信號交換發生失敗，或伺服器無法及時回應所造成。 Connection Timeout Expired. The timeout period elapsed while attempting...

Posted 17 March 2016 09:46 PM 由 Jeffrey | 2 comment(s) 5,751

Filed under: SQL

T-SQL使用逗號分隔字串當作WHERE IN條件

寫Stored Procedure時有一個麻煩情境是由外界傳入參數當作WHERE IN條件，由於參數數量不定，難以事先寫成WHERE … IN (@val1, @val2, @val3)，開發者往往會走上用傳入參數組裝SQL指令的險路，稍有不慎就搞出SQL Injection，導致難以想像的災難。（是的，SQL Injection不只會出現在ASP/ASP.NET/PHP/Java/C++，也可能藏在Stored Procedure裡) 最常見的例子是開放使用者勾選一個到多個類別作為篩選條件，例如使用者選取了...

Posted 13 March 2016 10:16 PM 由 Jeffrey | 4 comment(s) 12,217

Filed under: SQL

將複合字串拆成多欄位－以ORACLE及SQL為例

先說說我的需求。某資料表使用複合欄位當Primary Key，例如：由OrgId、DeptId、UserId三欄組成唯一鍵值。當要查詢特定資料，理論上應寫成WHERE OrgId='…' AND DeptId = '…' AND UserId = '…'。為求簡便，在.NET程式端以及某些資料表我發明了一種複合代碼字串"OrgId-DeptId-UserId"，只用一個參數或一個欄位就搞定關聯，省時又省力。不過，每個做法總有黑暗面...

Posted 09 March 2016 06:32 AM 由 Jeffrey | 5 comment(s) 20,166

Filed under: ORACLE, SQL

搜尋