2017-07-29

[SQL Server] Buffer cache hit ratio over 100% ?


觀察 Buffer cache hit ratio 超過 100 ?

解決方案是: Buffer cache hit ratio 與 Buffer cache hit ratio base 一併列入計算。

對於Buffer cache hit ratio 的建議值,多半是 90%、95% 以上。
因為這表示不需讀取磁碟即可在緩衝區快取中找到之頁面的百分比。從快取讀取遠比從磁碟讀取節省成本,因此此比率越高越好。
但實際去觀察此比率值時,卻是超過 100% ?

示範版本
SQL Server 2014、SQL Server 2016

-- 001_Buffer cache hit ratio over 100_SQL2016



-- 002_Buffer cache hit ratio over 100_SQL2014



查詢 效能計數器 Buffer cache hit ratio

-- Query: Buffer Cache Hit Ratio
USE  master
GO
SELECT object_name, counter_name, cntr_value, @@VERSION
FROM sys.dm_os_performance_counters 
WHERE [counter_name] = 'Buffer cache hit ratio';
GO




為什麼 Buffer cache hit ratio 值會破百?

應使用  Buffer cache hit ratio 搭配 Buffer cache hit ratio base 一併列入計算

計算 Buffer cache hit ratio(%)

-- Calculate: Buffer cache hit ratio(%)
/*
Calculate: Buffer cache hit ratio(%) = 100.0 * Buffer cache hit ratio / Buffer cache hit ratio base
SQL Server 2012 edition
*/
USE  master
GO
DECLARE @bchr INT, @bchrb INT
SET @bchr = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='Buffer cache hit ratio')
SET @bchrb =(SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='Buffer cache hit ratio base')

SELECT 100.0 * @bchr/@bchrb 'Calculate: Buffer cache hit ratio(%)', 
 @bchr 'Counter: Buffer cache hit ratio', 
 @bchrb 'Counter: Buffer cache hit ratio base'
GO

-- 003_計算_Buffer cache hit ratio(%)



觀察 兩個計數器 Buffer Cache Hit Ratio, Buffer cache hit ratio base


-- Counter: Buffer Cache Hit Ratio, Buffer cache hit ratio base                                                                                                     
USE  master
GO
SELECT object_name, counter_name, cntr_value, @@VERSION
FROM sys.dm_os_performance_counters 
WHERE [counter_name] LIKE 'Buffer cache hit ratio%';
GO


-- 004_觀察這兩個計數器


-- 009_效能監視器的Buffer cache hit ratio是正確的





SQL Server 2012 Memory Manager 的改變

這是因自 SQL Server 2012 版本開始,在記憶體的管理應用以及DMV上有許多改變。

-- 005_SQL Server 2012 Memory_Manager



在 SQL Server 的 Buffer Manager 物件裡的 Buffer cache hit ratio,需要搭配 Buffer cache hit ratio base 一併列入運算。

Calculate: Buffer cache hit ratio(%) = 100.0 * Buffer cache hit ratio / Buffer cache hit ratio base

Memory Manager surface area changes in SQL Server 2012

Interpreting the counter values from sys.dm_os_performance_counters



SQL Server 線上說明

尚未找到與此相關的說明

Buffer cache hit ratio
表示不需讀取磁碟即可在緩衝區快取中找到之頁面的百分比。
此比率是過去數千個分頁存取中,快取叫用總數除以快取查閱所得的結果。
時間一久,比率的變動會越來越小。 從快取讀取遠比從磁碟讀取節省成本,因此您會希望此比率越高越好。
通常,您可以藉由增加 SQL Server 可用的記憶體數量或是使用緩衝集區擴充功能,來提高緩衝區快取叫用比率。

-- 010_線上說明_僅Buffer cache hit ratio



-- 011_增加_Buffer cache hit ratio base




參考資料

Memory Manager surface area changes in SQL Server 2012
https://blogs.msdn.microsoft.com/sqlosteam/2012/07/11/memory-manager-surface-area-changes-in-sql-server-2012/

Interpreting the counter values from sys.dm_os_performance_counters
https://blogs.msdn.microsoft.com/psssql/2013/09/23/interpreting-the-counter-values-from-sys-dm_os_performance_counters/

SQL Server, Buffer Manager Object
https://technet.microsoft.com/en-us/library/ms189628(v=sql.120).aspx

SQL Server, Buffer Manager Object
https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-buffer-manager-object

SQL Server 的 Buffer Manager 物件
https://docs.microsoft.com/zh-tw/sql/relational-databases/performance-monitor/sql-server-buffer-manager-object

SQL Server 的 Memory Manager 物件
https://docs.microsoft.com/zh-tw/sql/relational-databases/performance-monitor/sql-server-memory-manager-object

2017-07-27

[SQL Server] sp_server_info: Maximum length of table name or column name - 資料表名稱、資料行名稱的最大長度


sp_server_info 可以取得 SQL Server 伺服器的相關屬性。例如:
  • 資料表名稱 最大長度
    • 例如:128
  • 資料行名稱 最大長度
    • 例如:128
  • 伺服器的字元集排序
    • 例如:charset=cp950 collation=Chinese_Taiwan_Stroke_BIN

sp_server_info
傳回 SQL Server、資料庫閘道或基礎資料來源的屬性名稱和相符值的清單。

示範版本
SQL Server 2016

範例01:sp_server_info 列出全部的屬性

-- 01-sp_server_info-Lists all the attribute values
USE master
GO
EXEC sp_server_info
GO

-- 01_sp_server_info-Lists all the attribute values




範例02:sp_server_info 列出指定的屬性


-- 02-sp_server_info-Lists the specified attribute values
--  sp_server_info - attribute_id is int data type
USE master
GO
EXEC sp_server_info 2
GO

-- 02_sp_server_info 列出指定的屬性





參考資料

sp_server_info (Transact-SQL)
https://technet.microsoft.com/en-us/library/ms176094(v=sql.110).aspx

sp_server_info (Transact-SQL)
https://technet.microsoft.com/zh-tw/library/ms176094(v=sql.110).aspx

[SQL Server] xp_msver: version, server information - 版本資訊、伺服器環境等資訊


xp_msver 可用於查詢 SQL Server 伺服器上的 CPU 數量、記憶體  等。

xp_msver
  • 傳回有關 Microsoft SQL Server 的版本資訊。
  • xp_msver 也會傳回有關伺服器之實際組建編號的資訊,以及有關伺服器環境的資訊。 
  • xp_msver 傳回的資訊可用於 Transact-SQL 陳述式、批次、預存程序等等,以加強平台獨立程式碼的邏輯。

範例01:xp_msver 列出全部的選項值

-- 01_xp_msver: Lists all the option values
USE master
GO
EXEC xp_msver
GO

-- 01_xp_msver_Lists all the option values





範例02:xp_msver 列出指定的選項值


-- 02_xp_msver: Lists the specified option values
USE master
GO
EXEC xp_msver 'ProductName', 'ProductVersion', 'ProcessorCount', 'PhysicalMemory'
GO


-- 02_xp_msver_Lists the specified option values





xp_msver 支援的選項







參考資料

xp_msver (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-msver-transact-sql

2017-07-26

[SQL Server] Performance Dashboard Reports - Reports Demo


SQL Server 2012 Performance Dashboard Reports 可用於協助 DBA 快速識別出目前系統的效能瓶頸。

安裝與建置 ,請參考:
[SQL Server] Performance Dashboard Reports - CPU bottlenecks, IO bottlenecks, missing Index ,Blocking, Latch
http://sharedderrick.blogspot.tw/2017/07/sql-server-performance-dashboard.html

-- 010_Overall_Performance



-- 041_檢視報表_勾選_請不要再顯示這個警告



-- 043_檢視_效能瓶頸



-- 100_系統負載_Performance_Dashboard_Reports




更多的 Reports Demo,請參考
https://photos.app.goo.gl/BrwZtgBfgH9O7u5b2





參考資訊

[SQL Server] Performance Dashboard Reports - CPU bottlenecks, IO bottlenecks, missing Index ,Blocking, Latch
http://sharedderrick.blogspot.tw/2017/07/sql-server-performance-dashboard.html

[SQL Server] Performance Dashboard Reports - CPU bottlenecks, IO bottlenecks, missing Index ,Blocking, Latch

SQL Server 2012 Performance Dashboard Reports 可用於協助 DBA 快速識別出目前系統的效能瓶頸。

Performance Dashboard Reports 是 Microsoft 所提供,採用 SQL Server Management Studio(SSMS) 的自訂報表(Custom Reports)方式來呈現。

可用於解決以下性能上的問題:
  1. CPU  瓶頸(bottlenecks )
  2. DISK IO  瓶頸
  3. 遺漏索引(missing index)
  4. Blocking(封鎖)
  5. Latch contention(閂鎖爭用)
Performance Dashboard Reports 使用 DMV(dynamic management views) 方式來取得系統相關資訊,以自訂報表(*.rdl)方式來呈現,無須安裝 Reporting Services。

適用環境:

  • SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014
  • 經過測試,SQL Server 2014、SQL Server 2016 可以使用。




下載與安裝 Performance Dashboard Reports 

示範版本
SQL Server 2016 + SSMS 17.1

工作:
00. 安裝 Performance Dashboard Reports
01. 執行 setup.sql
02. 使用 SSMS  開啟 performance_dashboard_main.rdl

預設路徑

  • "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\setup.sql"
  • "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\performance_dashboard_main.rdl"


工作00: 安裝 Performance Dashboard Reports

-- 011_安裝 Performance Dashboard Reports


預設安裝路徑:
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard

-- 012_點選_I_Accept

 

-- 013_Registration_Information



-- 014_Feature_Selection



-- 015_Change_Current_Destination_Folder



-- 016_Ready_to_Install



-- 017_Complete



-- 018_Performance Dashboard_安裝路徑與檔案



-- 019_CHM說明_Performance Dashboard




工作01: 執行 setup.sql

01. 使用 SSMS,執行 setup.sql。
setup.sql 預設是位於:
"C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\setup.sql"

02. setup.sql 將在 msdb 系統資料庫,建立新的結構描述、資料表、預存程序等資料庫物件。
例如:
MS_PerfDashboard、MS_PerfDashboard.tblConfigValues、MS_PerfDashboard.usp_CheckDependencies等

預存程序,約有 37 支。

-- 020_在msdb建立新的物件


-- 021_完成建置



-- 022_檢視產生的預存程序



-- 023_檢視產生的物件




工作02: 使用 SSMS  開啟 performance_dashboard_main.rdl

01. 使用 SSMS,在 物件總管,在執行個體節點上,滑鼠右鍵,選擇 自訂報表。

-- 031_SSMS_執行個體_自訂報表


02. 開啟 performance_dashboard_main.rdl
performance_dashboard_main.rdl 預設路徑:
"C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\performance_dashboard_main.rdl"

-- 032_performance_dashboard_main_路徑


03. 在 執行自訂報表,勾選 請不要再顯示這個警告

-- 033_執行自訂報表


完成安裝。

之後要使用 Performance Dashboard Reports,可直接開啟 SSMS ,就可以看到 Performance Dashboard Reports。

-- 034_完成安裝



-- 035_檢視可用報表類型


-- 36_SSMS_點選已存在的報表_performance_dashboard_main




下載 SQL Server 2012 Performance Dashboard Reports

Microsoft® SQL Server® 2012 Performance Dashboard Reports

-- 101_下載_Performance_Dashboard_Reports


-- 102_功能說明


-- 103_下載的檔案_SQLServer2012_PerformanceDashboard



-- 104_檔案_詳細資訊




參考資料

SQL Server® 2012 Performance Dashboard Reports

SQL Server Performance Dashboard Reports unleashed for Enterprise Monitoring !!!

2017-07-20

[SQL Server] Log Path of Failover Cluster Instance Diagnostics, sys.dm_os_server_diagnostics_log_configurations 容錯移轉叢集診斷記錄檔的存放路徑


使用 sys.dm_os_server_diagnostics_log_configurations,可取得 SQL Server 容錯移轉叢集診斷記錄檔的存放路徑等相關資訊。

以 SQL Server 2016 為例,容錯移轉叢集診斷記錄檔的存放路徑,預設是:
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log

-- 使用 sys.dm_os_server_diagnostics_log_configurations,查詢 SQL Server 容錯移轉叢集診斷記錄檔的存放路徑
USE master
GO
SELECT is_enabled, path, max_size, max_files
FROM sys.dm_os_server_diagnostics_log_configurations
GO

-- 01_使用 sys.dm_os_server_diagnostics_log_configurations,查詢 SQL Server 容錯移轉叢集診斷記錄檔的存放路徑




-- 02_檔案總管_ 查詢 SQL Server 容錯移轉叢集診斷記錄檔的存放路徑













參考資料

[SQL Server] ServerProperty('ErrorLogFileName') , Error log file location 錯誤紀錄檔: 目前最新的完整檔案路徑與檔案名稱
http://sharedderrick.blogspot.tw/2017/07/sql-server-serverpropertyerrorlogfilena.html

sys.dm_os_server_diagnostics_log_configurations
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-server-diagnostics-log-configurations

sys.dm_os_server_diagnostics_log_configurations
https://technet.microsoft.com/zh-tw/library/gg471697(v=sql.110).aspx

View and Read Failover Cluster Instance Diagnostics Log
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/view-and-read-failover-cluster-instance-diagnostics-log

[SQL Server] ServerProperty('ErrorLogFileName') , Error log file location 錯誤紀錄檔: 目前最新的完整檔案路徑與檔案名稱


以 SQL Server 2016 為例,錯誤記錄檔,目前最新的完整檔案路徑與檔案名稱,預設:
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG

以 SQL Server 2016 為例,錯誤記錄檔的檔案路徑,預設是:
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log


使用 ServerProperty('ErrorLogFileName'),可以取得 SQL Server 錯誤記錄檔,目前最新的完整檔案路徑與檔案名稱


-- 查詢 SQL Server 錯誤記錄檔,目前最新的完整檔案路徑與檔案名稱
USE master
GO
SELECT ServerProperty('ErrorLogFileName') 'Error log file location'
GO


-- 01_ ServerProperty_ErrorLogFileName_SQL Server 錯誤記錄檔,目前最新的完整檔案路徑與檔案名稱




-- 02_檔案總管_ 查詢 SQL Server 錯誤記錄檔,目前最新的完整檔案路徑與檔案名稱





使用 xp_readerrorlog,查詢 SQL Server 錯誤記錄檔,目前最新的完整檔案路徑與檔案名稱


-- 使用 xp_readerrorlog,查詢 SQL Server 錯誤記錄檔,目前最新的完整檔案路徑與檔案名稱
USE master
GO
EXEC xp_readerrorlog 0, 1,N'Logging SQL Server messages in file'
GO

-- 03_使用 xp_readerrorlog,查詢 SQL Server 錯誤記錄檔,目前最新的完整檔案路徑與檔案名稱





參考資料

設定SQL Server「錯誤記錄檔(Error log file)」的保存份數

檢視 SQL Server 錯誤記錄檔 (SQL Server Management Studio)

使用 xp_readerrorlog 或 sp_readerrorlog 來讀取 SQL Server Agent「錯誤記錄檔」

使用 xp_readerrorlog 或 sp_readerrorlog 來讀取 SQL Server 「錯誤記錄檔」

Identify location of the SQL Server Error Log file

SERVERPROPERTY (Transact-SQL)

2017-07-16

[PowerShell] Get-ClusterLog: Generate Windows cluster log for troubleshooting (產生 Cluster.log 進行錯誤排除)


對 容錯移轉叢集 執行錯誤排除時,將需要分析 Windows cluster log (Cluster.log)。

可以使用 PowerShell 的 Get-ClusterLog 來產生 全部 或是 指定 節點伺服器的系統記錄檔。

本文以 [SQL Server] AlwaysOn, Error 41131, failover failed,容錯移轉 失敗 為例

逐步帶領各位使用 PowerShell 的 Get-ClusterLog 來產生 Cluster.log,執行 錯誤排除 [NT AUTHORITY\SYSTEM] 帳戶權限不足 的問題。



目的:產生 Failover Cluster log

作業系統:Windows 2008 R2/2012/2012 R2/2016 Cluster

請使用 「系統管理員身分」來啟動 「Windows PowerShell ISE」

提醒:請到各個 Node Server 去檢視  log。


-- 為每個 Node Server 產生 Failover Cluster log,
-- 並存放於預設的本機路徑:C:\Windows\Cluster\Reports
-- -UseLocalTime:採用 LocalTime,若未指定此參數,將使用 Greenwich Mean Time (GMT)。

Get-ClusterLog -Destination . -UseLocalTime

-- 010_執行Get-ClusterLog



-- 011_檢視產生的 Failover_Cluster_log





[PowerShell] Get-ClusterLog: Generate Windows cluster log for troubleshooting


工作01. 使用 [PowerShell] Get-ClusterLog: 產生 Windows cluster log


-- 語法
Get-ClusterLog –Node [SQL Server node name] –TimeSpan 15



-- 範例
Get-ClusterLog –Node AGN02 –TimeSpan 15


-TimeSpan
指定要產生 Cluster.log 的時間間距,以分鐘為單位。
例如:–TimeSpan 15,是指產生最近 15 分鐘內的 Cluster.log。
- Node  指定需要產生 Cluster.log 節點伺服器。
-UseLocalTime採用 LocalTime,若未指定此參數,將使用 Greenwich Mean Time (GMT)

Cluster.log
系統紀錄 Cluster.log 預設是存放在 %WINDIR%\cluster\reports。
例如:C:\Windows\Cluster\Reports。


-- 01_01_PowerShell 產生 Windows cluster log



工作02. 分析 Windows cluster log(Cluster.log)

分析 Cluster.log

搜尋以下文字:
Failed to run diagnostics command.
The user does not have permission to perform this action.

-- 02_檢視Cluster_Log



-- 03_查詢到相關聯的錯誤_The user does not have permission to perform this action.



分析
在 Cluster.log 報告中,可以看到系統紀錄了 權限不足 的問題。
這是因為  [NT AUTHORITY\SYSTEM] 帳戶的權限不足,無法執行 「健全狀況」的偵測,也無法執行 「容錯移轉(Failover) 」。

解決方案

賦予 [NT AUTHORITY\SYSTEM] 帳戶以下權限來解決問題:
  1. Alter Any Availability Group
  2. Connect SQL
  3. View server state
參考



 補充資訊

-- 10_Error_41131



-- 30_SQL_Log



-- 31_發出Failover



-- 32_Error_Reported_02_TO_01




-- 33_RESOLVING_PENDING_FAILOVER



-- 34_state_change



-- 35_Rollback_Failover



-- 36_state_change__SECONDARY_NORMAL



-- 50_SQL_Log



-- 51_發出 Failover





參考文件

[SQL Server] AlwaysOn, Error 41131, failover failed,容錯移轉 失敗
http://sharedderrick.blogspot.tw/2017/07/sql-server-alwayson-error-41131.html

Cannot create a high-availability group in Microsoft SQL Server 2012
https://support.microsoft.com/en-us/help/2847723/cannot-create-a-high-availability-group-in-microsoft-sql-server-2012

Windows Server 2008 R2: Troubleshooting Failover Clusters
https://technet.microsoft.com/en-us/library/hh272674.aspx

Microsoft.FailoverClusters.PowerShell
https://technet.microsoft.com/en-us/library/ee461009.aspx

Get-ClusterLog
https://technet.microsoft.com/itpro/powershell/windows/failoverclusters/get-clusterlog