What Metrics to you monitor on your SQL Server? And now often do you check?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » What Metrics to you monitor on your SQL...

What Metrics to you monitor on your SQL Server? And now often do you check?

Rate Topic

Display Mode

Topic Options

Author

Message

samalex

Posted Thursday, November 29, 2012 9:29 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 135, Visits: 554

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 -

Post #1390664

Grant Fritchey

Posted Thursday, November 29, 2012 10:07 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 13,383, Visits: 25,189

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1390705

samalex

Posted Tuesday, December 04, 2012 10:14 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 135, Visits: 554

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

Post #1392603

Grant Fritchey

Posted Tuesday, December 04, 2012 11:08 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 13,383, Visits: 25,189

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1392631

« Prev Topic | Next Topic »

Permissions