목적 : 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
감사합니다. |
'연구개발 > DBA' 카테고리의 다른 글
UPDATE를 통한 SQL서버 엔진 내부 들여다 보기 (0) | 2011.08.14 |
---|---|
SSIS VS Linked Server의 성능비교 (0) | 2011.08.14 |
SET VS SELECT 속도 측정 (0) | 2011.08.14 |
Server 로긴 유저 및 각 DB별사용자 권한 조회 (0) | 2011.08.14 |
Select를 통한 변수 선언의 위험성 (101111) (0) | 2011.08.14 |