May 29, 2008 at 3:12 pm
I am trying to create an automated performance monitoring tool. This tool should be looking at the server performance( failing jobs, blocking, deadlocks, etc[ please add more item if you have some suggestion] )and track the errors in a file.
Is there any built in tool to do this? I was planning to use perfmon and profiler and send the result to a file. Is there any other way? I am in the process of gathering as many idea as possible to generate a cool tool. Please chip in some ideas!
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
May 30, 2008 at 6:01 am
Blocking will require more than perfmon & profiler. Do a search in the scripts and you'll find some timed scripts that will gather that data for you.
SQL Server 2008 is coming out with a built-in monitoring tool. Otherwise, I'd suggest hitting the dynamic management views for what data they have.
There are third party tools that aren't that expensive. Buy vs. build, especially for something as big as what you outlined seems like it's going to fall on the buy side. Take a look at the monitoring tool currently in beta over at Red Gate.
"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
May 30, 2008 at 6:44 am
Thanks for your input.....
Actually, on the "buying" thing, I have also approached my company to do that, but unfortunately they have spent that budget into some other tool and that tool does not have enough licensing for the whole floor to use it. So, I have to stick with this idea still i guess.
Okay, as for the blocking, I really appreciate your help for the scripts. It is really good. As part of that, I was also thinking to put all the data (errors, jobs, information etc) to a SQL table. From there, I plan to create a report to make it nice. What do you think of this?
Or should I start using SSIS to do something similar?
( We don't have SMTP though :crying: )
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
May 30, 2008 at 6:51 am
Well, if you're going to build it out... probably more than one table, even several. I say this because I know that blocking information, deadlocks, and trace/profiler, data are all different. You'll need to store them in different locations.
Do you have access to Reporting Services? If so, build a dash board there that will just show five or six things like blocking, deadlocks, cpu, stored procedures... and then then green, red or yellow. If something is red or yellow, allow the user to click on it and then see detailed views from the specific places where that data is stored.
It's funny, I say buy over build, but one of the guys in our shop has been building a tool for running Profiler and collecting and aggregating it's data in an automated fashion because we haven't found a tool that will do what we need there. My hope is to combine that with the data we're collecting through Operations Manager 2007 into a dashboard just like I described. We'll see how it all works out.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy