September 15, 2009 at 3:12 pm
Ive been charged with setting up some form of monitoring on our sql servers. it will collect data from performance monitor such as cpu stats, and what not.
So as i monitor the most crucial items to begin with, Im wondering what do you guys monitor on a daily basis in perfmon on your sql servers, and what are you checking for when you monitor that particular item?
September 15, 2009 at 3:16 pm
September 16, 2009 at 6:42 am
Huge topic and you're going to get disagreement from almost everyone one counter or another. Best approach is to start with simple, basic counters and expand from there. I'd focus on anything that monitors either queues or wait states. That's where you can determine if you have problems most of the time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 17, 2009 at 12:03 am
If your environment is very huge and company can afford you can use MOM, redgate etc. third party tool to monitor your daily activity as well as much more other things too!
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
September 17, 2009 at 2:54 pm
Here is what we have setup on demand on key servers as a 'default':
General State Counters
======================
Memory - Available MBytes
Memory – Pages/sec
Network Interface – Bytes total/sec
Physical Disk - % Idle Time
Physical Disk - Avg. Disk Queue Length
Physical Disk – Disk Transfers/sec
Processor - % Processor Time
SQL Server Access Methods - Full Scans/sec
SQL Server Buffer Manager Object - Buffer Cache Hit Ratio
SQL Server Buffer Manager Object - Page Life Expectancy
SQL Server Memory Manager - Memory Grants Pending
System - Processor Queue Length
SQL Server Database - Transactions/Sec
SQL Server Databases - Log Growths
SQL Server Databases Application Database - Percent Log Used
SQL Server Databases Application Database - Transactions/sec
SQL Server Latches – Average Latch Wait Time
SQL Server Locks – Average Wait Time
SQL Server Locks - Lock Timeouts/sec
SQL Server Locks – Lock Waits/sec
SQL Server Locks - Number of Deadlocks/sec
We also have the following perfmon logging setup gathing information 24x7 on those same key servers:
Ongoing Monitoring - By Object
==================
Objects tracked:
Memory
Network Interface
Paging File
Physical Disk
Processor
SQL Server: Access Methods
SQL Server: Buffer Manager
SQL Server: Databases
SQL Server: Latches
SQL Server: Locks
SQL Server: Memory Manager
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply