November 29, 2012 at 9:29 am
We use SQL Monitor to keep tabs on two of our bigger production servers, but given we have about 12 SQL Servers total and just can't afford to license every one with SQL Monitor I'm building a basic process to compile data from each of our Servers into a single database for reporting. I already do this for disk space and SQL Jobs, which is nice getting a report emailed to me from SSRS every morning seeing job statistics and disk space usage and trends on every server ... but what else do you monitor? I'm adding the database size (Log and Data files), user load, last backups, and a few other odds and ends, but anything else that might be good to trend over time?
For now I have one server with a DB that gets most of this data via server links, but I'm working on a canned Maintenance DB that I'll roll out to each of our Servers (SQL 2000, 2005, and 2008) with all the scripts and tables in place to track everything, then I'll just schedule a procedure to run either hourly or maybe every 30 minutes to create a snapshot of the data which will still be pulled to a central DB for reporting.
Any things or suggestions on this? Anyone else running something similar?
Thanks -
November 29, 2012 at 10:07 am
I've built my own monitoring system several times. I wrote it all down in Chapters 2 & 3 of my book. I use a combination of performance monitor, SQL Server's Dynamic Management Objects (DMO) and extended events. With that, while it's work, you can get everything you need.
"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
December 4, 2012 at 10:14 am
Hi Grant,
Wow thanks for the reply, I'm a huge fan of your site and enjoyed your talks at SQL In The City in Austin earlier this year. Also which one of your books has the two chapters you mentioned? I have your book SQL Server Execution Plans which is great, but that doesn't appear to be the one with these two chapters.
And one unfortunate bummer is several of these servers I'm monitoring are SQL 2000 (yeah I know...) so I don't have the DM tables to use. Hopefully most or all will be replaced in FY13, but we'll see.
Take care --
Sam
December 4, 2012 at 11:08 am
It's the book on query tuning. I'd suggest you get the 2008 version of the book. Most of what's in it will be applicable to 2000, unlike the 2012 book which seriously switches gear to support all the new functionality.
"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