德瑞克：SQL Server 學習筆記: 2017/07

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)方式來呈現。

可用於解決以下性能上的問題：

CPU 瓶頸(bottlenecks )
DISK IO 瓶頸
遺漏索引(missing index)
Blocking(封鎖)
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

https://www.microsoft.com/en-us/download/details.aspx?id=29063

-- 101_下載_Performance_Dashboard_Reports

-- 102_功能說明

-- 103_下載的檔案_SQLServer2012_PerformanceDashboard

-- 104_檔案_詳細資訊

參考資料

SQL Server® 2012 Performance Dashboard Reports

https://www.microsoft.com/en-us/download/details.aspx?id=29063

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

https://blogs.msdn.microsoft.com/sql_server_team/sql-server-performance-dashboard-reports-unleashed-for-enterprise-monitoring/

SQL Server Performance Dashboards - Home
https://sqldashboards.codeplex.com/

SQL Server Performance Dashboard Reports
http://www.sqlservercentral.com/articles/SQL+Server/127992/

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)」的保存份數

http://sharedderrick.blogspot.tw/2010/04/sql-servererror-log-file.html

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

https://docs.microsoft.com/zh-tw/sql/relational-databases/performance/view-the-sql-server-error-log-sql-server-management-studio

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

http://sharedderrick.blogspot.tw/2011/01/xpreaderrorlog-spreaderrorlog-sql_07.html

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

http://sharedderrick.blogspot.tw/2011/01/xpreaderrorlog-spreaderrorlog-sql.html

Identify location of the SQL Server Error Log file

https://www.mssqltips.com/sqlservertip/2506/identify-location-of-the-sql-server-error-log-file/

SERVERPROPERTY (Transact-SQL)

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

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] 帳戶以下權限來解決問題：