繼上一篇[系列文章] 淺談如何標準化作業規範 - Setting cost threshold for parallelism in SQL Server

例行公事：enable the lock pages in memory

這篇 LPIM 其實有蠻多 DBA 也是會忽略掉這點

因為這屬於 Windows 的設定，所以會被歸類到 SP 人員的管理項目裡

然而有些 SP 並不會了解 DBA 的環境需求，因此 LPIM 就變成了邊緣人...

有時候邊緣人觀察到 SQL Server 有點不舒服時就會在 event log 裡面刷一下存在感...

A significant part of sql server process memory has been paged out.

How to reduce paging of buffer pool memory in the 64-bit version of SQL Server

小弟我比較雞婆點，所以會一併關心一下邊緣人

這裡稍稍的與先前的作法不太一樣

我先透過 SECEDIT /EXPORT /cfg 把 GPO 匯出

然後再去檢查 SeLockMemoryPrivilege 是否有 SQL Server 的帳號

並匯出 report 請 SP 同事幫忙設定一下帳號

function CheckLPIM()
{
	SECEDIT /EXPORT /cfg $gpoFile
	$result = Select-String -Path $gpoFile -Pattern SeLockMemoryPrivilege | Select-Object -Property  @{N=$queryname;E={$_.Line}}
	$result.$queryname

	$message = "Please check account group in LPIM."
	$message | Out-File -Append $reportPath"\"$reportFile
	$result | Out-File -Append $reportPath"\"$reportFile
	$endmark | Out-File -Append $reportPath"\"$reportFile
	Remove-Item $reportPath"\"$gpoFile
}



$queryname = "AccountGroup"
CheckLPIM

最後分享完整的檢查語法供大家參考與指教

如有設想不周的地方也歡迎大家提醒我記得檢查噢～
(檢查完實作自動設定，這樣不僅有泡咖啡的時間，還更多時間可以泡妞唷XD)

have fun ʕ•͡ᴥ•ʔ

[string] $datasource = "."
[string] $user = ""
[string] $pwd = ""
[string] $database = "master"
[string] $conn = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=$database;Data Source=$datasource;"
[string] $connStr = "Integrated Security=False;Server=$datasource;uid=$user;pwd=$pwd;Database=$database;"
[int] $value = 0
[int] $width = 30
[string] $query = ""
[string] $queryname = ""
[string] $message = ""
[string] $endmark = "**********************************************************************"
[string] $reportPath = Split-Path -Parent $MyInvocation.MyCommand.Definition
[string] $reportFile = "CheckReport.log"
[string] $gpoFile = "GPO.txt"

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $conn
$command = $connection.CreateCommand()
$connection.Open()

"" | Out-File -Append $reportPath"\"$reportFile
"" | Out-File -Append $reportPath"\"$reportFile
"" | Out-File -Append $reportPath"\"$reportFile
$dtnow = Get-Date -UFormat "%Y/%m/%d %A %H:%M:%S %Z"
"Check Date Time : " + $dtnow | Out-File -Append $reportPath"\"$reportFile
$endmark | Out-File -Append $reportPath"\"$reportFile



function CheckDrive()
{
	$BytesPerCluster = Get-WmiObject -Query $query -ComputerName '.' | Select-Object Name, BlockSize
	If ($BytesPerCluster.BlockSize -eq $value)
		{
			$message = "OK."
			$message | Out-File -Append $reportPath"\"$reportFile
			$BytesPerCluster | Out-File -Append $reportPath"\"$reportFile
			$endmark | Out-File -Append $reportPath"\"$reportFile
		}
	Else
		{
			$message = "Warning! Please check " + $queryname + "."
			$message | Out-File -Append $reportPath"\"$reportFile
			$BytesPerCluster | Out-File -Append $reportPath"\"$reportFile
			$endmark | Out-File -Append $reportPath"\"$reportFile
		}
}



function CheckConfigurations()
{
	$command.CommandText = $query
	$result = $command.ExecuteReader()
	$table = new-object "System.Data.DataTable"
	$table.Load($result)

	If ($table[0].value -eq $value)
		{
			$message = "OK."
			$message | Out-File -Append $reportPath"\"$reportFile

			$format = @{Expression={$_.value};Label=$queryname;width=$width}
			$table | format-table $format | Out-File -Append $reportPath"\"$reportFile
			$endmark | Out-File -Append $reportPath"\"$reportFile
		}
	Else
		{
			$message = "Warning! Please check " + $queryname + "."
			$message | Out-File -Append $reportPath"\"$reportFile

			$format = @{Expression={$_.value};Label=$queryname;width=$width}
			$table | format-table $format | Out-File -Append $reportPath"\"$reportFile
			$endmark | Out-File -Append $reportPath"\"$reportFile
		}
}



function CheckLPIM()
{
	SECEDIT /EXPORT /cfg $gpoFile
	$result = Select-String -Path $gpoFile -Pattern SeLockMemoryPrivilege | Select-Object -Property  @{N=$queryname;E={$_.Line}}
	$result.$queryname

	$message = "Please check account group in LPIM."
	$message | Out-File -Append $reportPath"\"$reportFile
	$result | Out-File -Append $reportPath"\"$reportFile
	$endmark | Out-File -Append $reportPath"\"$reportFile
	Remove-Item $reportPath"\"$gpoFile
}



$query = "SELECT Name, BlockSize FROM Win32_Volume WHERE FileSystem='NTFS' and DriveLetter = 'D:'"
$value = 65536
$queryname = "disk block size"
CheckDrive



$query = "SELECT value FROM sys.configurations WHERE name = 'max server memory (MB)'"
$value = 32768
$queryname = "max server memory (MB)"
CheckConfigurations



$query = "SELECT value FROM sys.configurations WHERE name = 'max degree of parallelism'"
$value = 2
$queryname = "max degree of parallelism"
CheckConfigurations



$query = "SELECT value FROM sys.configurations WHERE name = 'cost threshold for parallelism'"
$value = 50
$queryname = "cost threshold for parallelism"
CheckConfigurations



$queryname = "AccountGroup"
CheckLPIM



$connection.Close()