We Can measuring Performance by using below tools:
· Performance Monitor.
· SQL Traces & SQL Server profiler.
· Extended events.
· Activity monitor.
· DMV's, Database tuning advisor.
Performance Monitor Counters:
• Processor:% Processor Time
• If more than 80-90% for long periods, you might have a CPU bottleneck.
• System: Processor Queue Length
• Should be less than 4 per processor.
• If higher for long periods, you have CPU problem.
• Network Interface: Bytes received/sec
• Network Interface: Bytes sent/sec
• Compare to adapter bandwidth
• Memory: Memory: Page Faults/sec, pages/sec
• monitor disk paging which might cause high disk usage and thus, reduce performance significantly.
• SQL Server: Memory Manager: Total Server Memory ,Target Server Memory
• Counters should be identical or close
• SQL Server: Buffer Manager: Buffer Cache Hit Ratio
• Should be 90% or higher
• PhysicalDisk: Avg. Disk sec/read, sec/write
• Determines how long a read or write operation takes. Should be lower than 20ms (0.02s) at all times
• PhysicalDisk: Avg. Disk reads/sec, writes/sec
• Determines how many read or write operations occur.
•A single 15krpm disk supports ~180 iops
• PhysicalDisk: Current Disk Queue Length:
• Watch out for high values.
• SQL Server: Buffer Manager : Page Reads/sec ,Page Writes/sec
• Monitor disk activity caused by SQL server
• SQLServer: Access Methods : Full scans/sec
• How many tables scans occur per sec
• SQLServer: SQL Statistics : Batch Requests/sec
• How many batches are executed per sec
• Compare to next counter (should be higher)
• SQLServer: SQL Statistics : Compilations/sec
• How many plans are compiled per sec
• Compare to next counter (should be higher)
• SQLServer: SQL Statistics : Recompilations/sec
• How many plans are recompiled per sec
• SQLServer: Locks : Lock requests/sec
•SQLServer: Locks : Lock waits/sec
•SQLServer: Locks : Average Wait Time (ms)
• Lock statistics (requests vs. waits)
No comments:
Post a Comment