SQL Server performance baseline and monitoring

Let's talk about the monitoring values. The baseline captures those values. How to Make an alarm

Environment: Windows 2008r2, SQL Server 2008r2 SP1

Performance baseline:

CPU:

\ Processor (_ total) \ % processor time
\ Processor (_ total) \ % privileged time

\ Sqlserver: SQL statistics \ batch requests/sec
\ Sqlserver: SQL statistics \ SQL compilations/sec
\ Sqlserver: SQL statistics \ SQL re-compilations/sec
\ System \ processor Queue Length
\ System \ context switches/sec

Memory:

\ Memory \ available bytes
\ Memory \ pages/sec
\ Memory \ page faults/sec
\ Memory \ pages input/sec
\ Memory \ pages output/sec
\ Process (sqlservr) \ private bytes
\ Sqlserver: Buffer Manager \ buffer cache hit ratio
\ Sqlserver: Buffer Manager \ page life expectancy
\ Sqlserver: Buffer Manager \ lazy writes/sec
\ Sqlserver: Memory Manager \ Memory Grants pending
\ Sqlserver: Memory Manager \ target server memory (KB)
\ Sqlserver: Memory Manager \ Total server memory (KB)

Disk:

\ Physicaldisk (_ total) \ % disk Time
\ Physicaldisk (_ total) \ current disk Queue Length
\ Physicaldisk (_ total) \ avg. Disk Queue Length
\ Physicaldisk (_ total) \ disk transfers/sec
\ Physicaldisk (_ total) \ disk Bytes/sec
\ Physicaldisk (_ total) \ avg. Disk SEC/read
\ Physicaldisk (_ total) \ avg. Disk SEC/Write

SQL Server:

\ Sqlserver: Access Methods \ freespace scans/sec
\ Sqlserver: Access Methods \ full scans/sec
\ Sqlserver: Access Methods \ table lock escalations/sec
\ Sqlserver: Access Methods \ worktables created/sec
\ Sqlserver: General Statistics \ processes blocked
\ Sqlserver: General Statistics \ User connections
\ Sqlserver: latches \ Total latch wait time (MS)
\ Sqlserver: locks (_ total) \ lock timeouts (timeout & gt; 0)/sec
\ Sqlserver: locks (_ total) \ lock wait time (MS)
\ Sqlserver: locks (_ total) \ Number of deadlocks/sec
\ Sqlserver: SQL statistics \ batch requests/sec
\ Sqlserver: SQL statistics \ SQL re-compilations/sec

The above information is the performance baseline monitoring information, of course, the performance warning is also the monitoring information, the threshold value is reflected after the baseline is captured.

Regarding the performance warning, I used powershell to write a script and run it in the SQL Agent. If a warning occurs, send an email through dbmail.

The powershell script and some configuration information are as follows:

$ Server = "(local) "$ uid =" sa "$ db =" master "$ Pwd =" PWD "$ mailprfname =" Sina "$ recipients =" xxxxx@qq.com "$ subject =" proformance alter "$ computernamexml = "F: \ computername. XML "$ alter_cpuxml =" F: \ alter_cpu.xml "function getservername ($ xmlpath) {$ xml = [XML] (get-content $ xmlpath) $ return = new-object collections. generic. list [String] For ($ I = 0; $ I-lt $ XML. computernames. childnodes. count; $ I ++) {if ($ XML. computernames. childnodes. count-EQ 1) {$ CP = [String] $ XML. computernames. computername} else {$ CP = [String] $ XML. computernames. computername [$ I]} $ return. add ($ CP. trim ()} $ return} function getaltercounter ($ xmlpath) {$ xml = [XML] (get-content $ xmlpath) $ return = new-object collections. generic. list [String] $ list = $ XML. counters. counter $ list} function createalter ($ message) {$ sqlconnection = new-Object System. data. sqlclient. sqlconnection $ cnnstring = "Server = $ server; database = $ db; user id = $ uid; Password = $ PWD" $ sqlconnection. connectionstring = $ cnnstring $ cc = $ sqlconnection. createcommand (); If (-not ($ sqlconnection. state-like "open") {$ sqlconnection. open ()} $ CC. commandtext = "Exec MSDB .. sp_send_dbmail @ profile_name = '$ mailprfname', @ recipients =' $ recipients ', @ body =' $ message', @ subject = '$ subobject' "$ CC. executenonquery () | out-null $ sqlconnection. close () ;}$ names = getservername ($ computernamexml) $ pfcounters = getaltercounter ($ alter_cpuxml) foreach ($ CP in $ names) {$ P = new-object collections. generic. list [String] $ report = "" foreach ($ PFC in $ pfcounters) {$ B = "" $ counter = "\" + $ CP + $ PFC. get_innertext (). trim () $ p. add ($ counter)} $ COUNT = Get-counter $ P for ($ I = 0; $ I-lt $ count. countersamples. count; $ I ++) {$ v = $ count. countersamples. get ($ I ). cookedvalue $ PFC = $ pfcounters [$ I] # $ PFC. get_innertext () $ B = "" $ lg = "" if ($ PFC. operator-EQ "LT") {if ($ V-ge [double] $ PFC. alter) {$ B = "alter" $ lg = "greater than"} elseif ($ PFC. operator-EQ "GT") {if ($ V-le [double] $ PFC. alter) {$ B = "alter" $ lg = "less than" }}if ($ B-EQ "alter ") {$ Path = "\" + $ CP + $ PFC. get_innertext () $ item = "{0 }:{ 1 };{ 2} threshold: {3}"-F $ path, $ v. tostring (), $ LG, $ PFC. alter. trim () $ report + = $ item + "'n'" }}if ($ Report-ne ") {# production warning parameter counter, threshold value, current Value: createalter $ report }}

Two configuration files are involved: computernamexml and alter_cpuxml:

<computernames>        <computername>                fanr-pc        </computername></computernames>

<Counters>      <Counter alter = "10" operator = "gt" >\Processor(_Total)\% Processor Time</Counter>        <Counter alter = "10" operator = "gt" >\Processor(_Total)\% Privileged Time</Counter>        <Counter alter = "10" operator = "gt" >\SQLServer:SQL Statistics\Batch Requests/sec</Counter>        <Counter alter = "10" operator = "gt" >\SQLServer:SQL Statistics\SQL Compilations/sec</Counter>        <Counter alter = "10" operator = "gt" >\SQLServer:SQL Statistics\SQL Re-Compilations/sec</Counter>        <Counter alter = "10" operator=  "lt" >\System\Processor Queue Length</Counter>        <Counter alter = "10" operator=  "lt" >\System\Context Switches/sec</Counter></Counters>

Here, alter is the threshold value. For example, if the threshold value is greater than the performance counter value, a warning is issued.