what to monitor

Question

what to monitor

KATHLEEN Y ZHANG

Hall of Fame

Points: 3534
More actions
February 29, 2008 at 3:43 pm

#122477

I need to come up with a list of database objects, system objects to set up a full monitoring system. Other than a third party tool, does anyone have experience setting it up by just using sql or windows and may I know what you have included on your list?
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 1

Btw, we have transactional replication, log shipping in our environment. It would be great to know what to monitor for these.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Heh... sounds like you're trying to build an electronic DBA... it would be cheaper in the long run if you just hired one. Seriously... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 3

I know its impossible to get everything covered, but I am trying to get things as much automated as possible. Something like alerts, say if a process has been blocking for more than 1 min, shoot me an email with the text data, just an example.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Sure... that would be a good start... I guess I'd set up the following alerts... of course, you have to know what's right for your system or you'll be up all night...

The blocking alert that you mentioned.

Autogrow on TempDB.

Autogrow on any database that I had presized (basically, any production database) that shouldn't autogrow.

Total number of deadlocks per second (anything over 0 is bad in my book)

% Processor (I'd look for things over 95% for more than 5 minutes on a multiprocessor box)

% Disk (I'd look for things over 90% for more than 5 minutes)

I might set something for logins. The server has it's own logins as kind of a heart beat... if it can't, something is really wrong and maybe some CPU's are pegged. Depends on the system, though.

If it's dedicated to heavy ETL, the settings will be a lot different than a public facing server, so like anything else, it all "depends".

I'd turn on trace flag 1204 (If I remember correctly) to capture any deadlocks in the system log for troubleshooting regarless of the system type. On systems that I have full control over, I will not tolerate any code that creates a deadlock... there's just no excuse for deadlocks.

Depending in how critical the system is, I might write a soft of sp_SpaceUsed on "steriods" and have it log the growth of each or targeted tables by number of pages and rows. That same scheduled job might send me an email with a mini-report (simple output from a SELECT) with the top 10 or 20 rowcount and percentage changes.

I'd also have a scheduled job that would read the log and report the deadlock counts, sources, victims, date/time, etc, as well as storing that information in a tracking table by date and time. Every DBA should have his/her private DBA database for tools and tables like this. I believe you can have alerts write to a table, as well.

Each system is different... some are pure servers, some are a mixed bag of yuck. Some are public facing, some are not. Some are heavy I/O like ETL servers, some are heavy in the nightly processing, and some are heavy reporting servers. Most of the alerts I choose are based on % of one type or another. And, like I said, there is NEVER an excuse for deadlocks so far as I'm concerned. Work should stop and heads should role when one occurs... Microsoft would, of course, disagree with me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 5

You need to monitor few many things. Starting from the sql server services to the OS counters. Check for performance monitoring and tuning in SQL Server books online.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com