澳门金沙vip 3

SQL Server 如何确定内存瓶颈

sql server 统计出来的内存,不管是这个,还是dbcc
memorystatus,和进程管理器中内存差距很大,差不多有70G的差异。

就讲讲监控那些值,基线抓的是那些值。如何做告警

Detect Memory Bottleneck in SQL Server

具体原因不止,可能是内存泄漏,目前只能通过重启服务解决

环境:windows 2008r2,sql server 2008r2 sp1

By : Kasim Wirama, MCDBA

 

This article, I would
like to give information about memory bottleneck at SQL Server. The
easiest way to know whether your SQL Server undergoes memory bottleneck
is by looking at Task Manager, tab Performance, Physical Memory section.
Compare the Total and Available value, as long as Available is over 100
MB, it is still okay. If Available value is under 10 MB, your database
server box is definitely under physical memory pressure. Besides
physical memory pressure, there is virtual memory pressure. Let’s take a
look for these kinds of pressure and see what solutions addressed to the
corresponding problems.

 

You can get information
about physical memory pressure by looking at these performance monitor
counters: Memory:Available bytes, SQL Server:Buffer Manager: Buffer
Cache Hit Ratio, Page Life Expectancy, Checkpoint Pages/sec, and
Lazywrites/sec. You have your system performing well if value of first
two counters are high and last two counters are low.

 

You can check how many
memories allocated for buffer pool by issuing DBCC MEMORYSTATUS query in
SSMS. Compare value between Committed and Target, if Target value is
low, it indicates external physical memory pressure. You need to find
out whether other application that causes external memory pressure,
besides SQL Server, by looking at maximum memory consumed in Process tab
of Task Manager window. If other application causes external memory
pressure, it is advisable that the application is separated from
database server box, or you add more memory. You need to check internal
memory pressure by looking at percentage of stolen page to total
committed page from DBCC MEMORYSTATUS. Percentage value is greater than
75% or 80% indicating internal physical memory pressure.  Interesting
thing about internal memory pressure is that you need to find out what
components causing so many stolen memory page by querying this DMV below
:

 

SELECT type,
SUM(single_pages_kb) FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC

 

From query above, single
page allocation takes memory from SQL Server buffer pool, whereas
multipage allocation allocates memory outside buffer pool. Multipage
allocator possibly causes internal memory pressure. You can get
information about memory allocated to multipage page allocation by
querying DMV below :

 

SELECT type,
SUM(multi_pages_kb) FROM sys.dm_os_memory_clerks
WHERE multi_pages_kb != 0
GROUP BY type
ORDER BY SUM(multi_pages_kb) DESC

 

To detect virtual memory
pressure, you can look at performance counter name here : Paging File:
%Usage and Memory:Commit Limit.

Some solutions addressed
to virtual memory pressure. They are:

  1. 1.      
    Increase size of
    page file
  2. 2.      
    Use /3GB
    options
  3. 3.      
    Switch to 64 bit
    machine that have 8 TB memory address space.

澳门金沙vip 1

性能基线:

 澳门金沙vip 2

  cpu:

澳门金沙vip 3

    \Processor(_Total)\% Processor Time
    \Processor(_Total)\% Privileged Time

澳门金沙vip 4

    \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

Process/System Counts                    Value

  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)

Available Physical Memory                5337628672

  Disk:

Available Virtual Memory                 8723028054016

    \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

Available Paging File                    9299374080

  SQL Server:

Working Set                              26316115968

    \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 > 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

Percent of Committed Memory in WS        100

以上是性能基线监控的信息,当然性能警告也是监控这些信息,其中的阀值是根据基线抓取后体现。

Page Faults                              12218866

关于性能警告我是使用powershell 写了一个脚本,运行在SQL Agent
中。如果出现警告,就通过dbmail 发送邮件

System physical memory high              1

关于powershell 脚本和一些配置信息看如下:

System physical memory low               0

$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 = '$subject'
" 
    $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 "")
    {
        #生产警告 参数 计数器,阀值,当前值
        CreateAlter $report
    }
}

Process physical memory low              0

其中涉及到2个配置文件:computernamexml,alter_cpuxml分别如下:

Process virtual memory low               0

<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>

 

其中 alter 就是阀值,如第一条,如果 阀值 >
性能计数器值,就会发出警告。

(10 rows affected)

 

 

    

Memory Manager                           KB


VM Reserved                              70668820

VM Committed                             25599992

Locked Pages Allocated                   0

Large Pages Allocated                    0

Emergency Memory                         1024

Emergency Memory In Use                  16

Target Committed                         25600000

Current Committed                        25599992

Pages Allocated                          24728712

Pages Reserved                           0

Pages Free                               35552

Pages In Use                             4883440

Page Alloc Potential                     19436560

NUMA Growth Phase                        2

Last OOM Factor                          0

Last OS Error                            0

 

(16 rows affected)

 

Memory node Id = 0                       KB


VM Reserved                              70668756

VM Committed                             25599972

Locked Pages Allocated                   0

Pages Allocated                          24728712

Pages Free                               35552

Target Committed                         25599976

Current Committed                        25599976

Foreign Committed                        0

Away Committed                           0

Taken Away Committed                     0

 

(10 rows affected)

 

Memory node Id = 64                      KB


VM Reserved                              0

VM Committed                             20

Locked Pages Allocated                   0

 

(3 rows affected)

 

MEMORYCLERK_SQLGENERAL (node 0)          KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          27480

 

(6 rows affected)

 

MEMORYCLERK_SQLBUFFERPOOL (node 0)       KB


VM Reserved                              12954432

VM Committed                             655624

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          20682112

 

(6 rows affected)

 

MEMORYCLERK_SQLQUERYEXEC (node 0)        KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          4448

 

(6 rows affected)

 

MEMORYCLERK_SQLOPTIMIZER (node 0)        KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          1368

 

(6 rows affected)

 

MEMORYCLERK_澳门金沙vip,SQLUTILITIES (node 0)        KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          176

 

(6 rows affected)

 

MEMORYCLERK_SQLSTORENG (node 0)          KB


VM Reserved                              10048

VM Committed                             10048

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          17200

 

(6 rows affected)

 

MEMORYCLERK_SQLCONNECTIONPOOL (node 0)   KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          2608

 

(6 rows affected)

 

MEMORYCLERK_SQLCLR (node 0)              KB


VM Reserved                              6302848

VM Committed                             17884

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          12384

 

(6 rows affected)

 

MEMORYCLERK_SQLSERVICEBROKER (node 0)    KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          656

 

(6 rows affected)

 

MEMORYCLERK_SQLHTTP (node 0)             KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          8

 

(6 rows affected)

 

MEMORYCLERK_SNI (node 0)                 KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          144

 

(6 rows affected)

 

MEMORYCLERK_SNI (node 64)                KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          16

 

(6 rows affected)

 

MEMORYCLERK_SNI (Total)                  KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          160

 

(6 rows affected)

 

MEMORYCLERK_FULLTEXT (node 0)            KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          296

 

(6 rows affected)

 

MEMORYCLERK_SQLXP (node 0)               KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          16

 

(6 rows affected)

 

MEMORYCLERK_BHF (node 0)                 KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          208

 

(6 rows affected)

 

MEMORYCLERK_XE_BUFFER (node 0)           KB


VM Reserved                              4608

VM Committed                             4608

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          0

 

(6 rows affected)

 

MEMORYCLERK_XTP (node 0)                 KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          16

 

(6 rows affected)

 

MEMORYCLERK_HOST (node 0)                KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          472

 

(6 rows affected)

 

MEMORYCLERK_SOSNODE (node 0)             KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          55328

 

(6 rows affected)

 

MEMORYCLERK_SOSNODE (node 64)            KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          2432

 

(6 rows affected)

 

MEMORYCLERK_SOSNODE (Total)              KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          57760

 

(6 rows affected)

 

MEMORYCLERK_SOSOS (node 0)               KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          192

 

(6 rows affected)

 

MEMORYCLERK_SOSMEMMANAGER (node 0)       KB


VM Reserved                              79224

VM Committed                             79080

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          0

 

(6 rows affected)

 

MEMORYCLERK_FULLTEXT_SHMEM (node 0)      KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              96

SM Committed                             96

Pages Allocated                          0

 

(6 rows affected)

 

MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB


VM Reserved                                    0

VM Committed                                   0

Locked Pages Allocated                         0

SM Reserved                                    0

SM Committed                                   0

Pages Allocated                                192

 

(6 rows affected)

 

MEMORYCLERK_FILETABLE (node 0)           KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          16

 

(6 rows affected)

 

MEMORYCLERK_XE (node 0)                  KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          3880

 

(6 rows affected)

 

MEMORYCLERK_SQLLOGPOOL (node 0)          KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          15224

 

(6 rows affected)

 

MEMORYCLERK_LWC (node 0)                 KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          32

 

(6 rows affected)

 

MEMORYCLERK_FSCHUNKER (node 0)           KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          48

 

(6 rows affected)

 

CACHESTORE_OBJCP (node 0)                KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          203560

 

(6 rows affected)

 

CACHESTORE_SQLCP (node 0)                KB


VM Reserved                              0

VM Committed                             0

Locked Pages Allocated                   0

SM Reserved                              0

SM Committed                             0

Pages Allocated                          3322304