목적 : Perfmon을 통한 DB 성능 모니터링 시 사용할 수 있는 항목을 알아보자

 

sql server 2005 database engine performance counters

 

SQL Server 2005 Database Engine Performance Counters    
       
SQLServer:Access Methods SQLServer:Buffer Manager SQLServer:Buffer Node SQLServer:Buffer Partition
AU cleanup batches/sec                            AWE lookup maps/sec                         Database pages                                           Free list empty/sec                                
AU cleanups/sec                                        AWE stolen maps/sec                         Foreign pages                                               Free list requests/sec                           
By-reference Lob Create Count               AWE unmap calls/sec                           Free pages                                                   Free pages                                           
By-reference Lob Use Count                     AWE unmap pages/sec                       Page life expectancy                                      
Count Lob Readahead                             AWE write maps/sec                            Stolen pages                                                 SQLServer:Databases
Count Pull In Row                                     Buffer cache hit ratio                          Target pages                                               Active Transactions                               
Count Push Off Row                                 Buffer cache hit ratio base                   Total pages                                                   Backup/Restore Throughput/sec          
Deferred dropped AUs                              Checkpoint pages/sec                          Bulk Copy Rows/sec                              
Deferred Dropped rowsets                      Database pages                                  SQLServer:Cursor Manager by Type Bulk Copy Throughput/sec                   
Dropped rowset cleanups/sec                  Free list stalls/sec                               Active cursors                                                Data File(s) Size (KB)                            
Dropped rowsets skipped/sec                 Free pages                                          Cache Hit Ratio                                             DBCC Logical Scan Bytes/sec               
Extent Deallocations/sec                           Lazy writes/sec                                  Cache Hit Ratio Base                                    Log Bytes Flushed/sec                          
Extents Allocated/sec                               Page life expectancy                            Cached Cursor Counts                                  Log Cache Hit Ratio                              
Failed AU cleanup batches/sec                  Page lookups/sec                               Cursor Cache Use Counts/sec                     Log Cache Hit Ratio Base                      
Failed leaf page cookie                            Page reads/sec                                   Cursor memory usage                                   Log Cache Reads/sec                            
Failed tree page cookie                             Page writes/sec                                  Cursor Requests/sec                                    Log File(s) Size (KB)                             
Forwarded Records/sec                           Readahead pages/sec                         Cursor worktable usage                               Log File(s) Used Size (KB)                     
FreeSpace Page Fetches/sec                    Reserved pages                                  Number of active cursor plans                      Log Flush Wait Time                             
FreeSpace Scans/sec                               Stolen pages                                        Log Flush Waits/sec                              
Full Scans/sec                                           Target pages                                       SQLServer:Cursor Manager Total Log Flushes/sec                                   
Index Searches/sec                                  Total pages                                        Async population count                                 Log Growths                                         
LobHandle Create Count                           Cursor conversion rate                                 Log Shrinks                                          
LobHandle Destroy Count                         SQLServer:Catalog Metadata Cursor flushes                                              Log Truncations                                    
LobSS Provider Create Count                  Cache Entries Count                              Percent Log Used                                 
LobSS Provider Destroy Count                  Cache Entries Pinned Count               SQLServer:Exec Statistics Repl. Pending Xacts                              
LobSS Provider Truncation Count             Cache Hit Ratio                                    Distributed Query                                          Repl. Trans. Rate                                  
Mixed page allocations/sec                       Cache Hit Ratio Base                          DTC calls                                                       Shrink Data Movement Bytes/sec          
Page Deallocations/sec                              Extended Procedures                                   Transactions/sec                                  
Page Splits/sec                                         SQLServer:CLR                                  OLEDB calls                                                    
Pages Allocated/sec                                 CLR Execution                                        SQLServer:Plan Cache
Probe Scans/sec                                         SQLServer:Locks Cache Hit Ratio                                     
Range Scans/sec                                      SQLServer:General Statistics Average Wait Time (ms)                                Cache Hit Ratio Base                            
Scan Point Revalidations/sec                    Active Temp Tables                              Average Wait Time Base                              Cache Object Counts                            
Skipped Ghosted Records/sec                  Event Notifications Delayed Drop       Lock Requests/sec                                        Cache Objects in use                           
Table Lock Escalations/sec                        HTTP Authenticated Requests             Lock Timeouts (timeout > 0)/sec                  Cache Pages                                         
Used leaf page cookie                              Logical Connections                            Lock Timeouts/sec                                          
Used tree page cookie                              Logins/sec                                           Lock Wait Time (ms)                                     SQLServer:SQL Statistics
Workfiles Created/sec                              Logouts/sec                                        Lock Waits/sec                                              Auto-Param Attempts/sec                     
Worktables Created/sec                           Mars Deadlocks                                   Number of Deadlocks/sec                             Batch Requests/sec                             
Worktables From Cache Base                  Non-atomic yield rate                           Failed Auto-Params/sec                        
Worktables From Cache Ratio                   Processes blocked                               SQLServer:Memory Manager Forced Parameterizations/sec              
  SOAP Empty Requests                        Connection Memory (KB)                               Safe Auto-Params/sec                           
SQLServer:Latches SOAP Method Invocations                   Granted Workspace Memory (KB)                 SQL Attention rate                               
Average Latch Wait Time (ms)                  SOAP Session Initiate Requests         Lock Blocks                                                   SQL Compilations/sec                           
Average Latch Wait Time Base                 SOAP Session Terminate Requests      Lock Blocks Allocated                                    SQL Re-Compilations/sec                     
Latch Waits/sec                                        SOAP SQL Requests                           Lock Memory (KB)                                         Unsafe Auto-Params/sec                       
Number of SuperLatches                          SOAP WSDL Requests                         Lock Owner Blocks                                         
SuperLatch Demotions/sec                       SQL Trace IO Provider Lock Waits      Lock Owner Blocks Allocated                        SQLServer:Transactions
SuperLatch Promotions/sec                     Temp Tables Creation Rate                 Maximum Workspace Memory (KB)                Free Space in tempdb (KB)                    
Total Latch Wait Time (ms)                        Temp Tables For Destruction              Memory Grants Outstanding                        Longest Transaction Running Time      
  Trace Event Notification Queue           Memory Grants Pending                                NonSnapshot Version Transactions      
SQLServer:Wait Statistics Transactions                                       Optimizer Memory (KB)                                 Snapshot Transactions                        
Lock waits                                                 User Connections                                SQL Cache Memory (KB)                                Transactions                                         
Log buffer waits                                          Target Server Memory (KB)                          Update conflict ratio                             
Log write waits                                         SQLServer:User Settable                  Total Server Memory (KB)                              Update conflict ratio base                     
Memory grant queue waits                      Query                                                   Update Snapshot Transactions            
Network IO waits                                          Version Cleanup rate (KB/s)                  
Non-Page latch waits                               SQLServer:SQL Errors                        Version Generation rate (KB/s)            
Page IO latch waits                                   Errors/sec                                             Version Store Size (KB)                         
Page latch waits                                          Version Store unit count                      
Thread-safe memory objects waits               Version Store unit creation                   
Transaction ownership waits                       Version Store unit truncation               
Wait for the worker                                        

Workspace synchronization waits       

 

감사합니다.   

 

+ Recent posts