March 23, 2011 at 7:36 am
Quest put out a PDF of the most interesting Perfmon counters. Its written by some SQL celebs I trust, so its top notch. I have taken the DMV counters and at the end of every day I calculate daily averages for them via Agent job and write the avgs to a trend analysis table. I read the table in SSRS and add a sparkline to show the movement of each counter over time. I get the perfmon counter trends from an MDW I've setup.
I use powershell to loop thru my instances and capture counters every 15secs during working hrs and this creates a 25m row table by the end of the day. I then run a job to calculate daily avgs then truncate the raw data.
I'm thinking of conditionally formatting the counter values in SSRS to show me where the recommended Quest thresholds have been breached by a daily average.
I'm the only DBA at a mid-sized company (I look after 3TB of data) and just wanted some feedback if this all sounds like a good plan and if anyone else does anything similar.
My boss is promising me SCOM at some point for real time alerting / analysis but for now i'm just trying roll somethin useful of my own!
March 23, 2011 at 8:10 am
Yeah, I'd say it sounds fairly reasonable. As long as the Posh script isn't adding to the overhead on your systems. You can just use perfmon to spit out the counters to a file and then move the file into a database. Other than that as a suggestion, it sounds like you've built a monitoring solution.
Now, what about query performance?
"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
March 23, 2011 at 1:38 pm
I would tackle this slightly differently, mainly to prevent errors from stopping your process.
I'd use POSH to grab the data on each server, from each server, and store it locally. Then use a separate script to roll up the data. that way if the POSH dies, or there's a server issue, you haven't stopped collecting data. Also, that data is available locally on each machine in case it's needed.
July 26, 2011 at 11:01 pm
Hi
What you can do is use perfmon (or your powershell) to get data.
15 seconds interval is a bit much, most of the counters can fluctuate, so you don't want a ton of false positives.
Rather take it down to a minute or even 5.
Critical counters such as deadlocks \ sec you want to know about immediately.
But generally page life, you can investigate after the fact.
You idea sound good if it you can do what you can with powershell you are more than half way there.
usually look at a sustained counter before alerting, i.e. a block for 2 minutes, 90% for longer than 10 minutes etc.
Cheers
Jannie
July 27, 2011 at 6:03 am
Sounds interesting. Can you share the scripts you use? I can share my tuning experience once it's on my system ;-).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply