July 19, 2008 at 6:00 pm
Hey guys,
I started my new project a week ago. My manager wants a daily health check of the servers. When i suggested about the perfmon counters like %processer time etc. he went beserk and stated that we have already tools to check the health of the systems, so now he wants only counters on the database level.
If i am suggesting any counter that even remotely has to do with hardware of the system like memory he is going wild and says he wants counters exclusively for SQL Server databases. Can any of you guys suggest any useful database related counters that would give some meaningful and useful results on a day to day basis.
July 19, 2008 at 9:38 pm
Wow, sounds like a rough gig. :w00t: This link gives a brief overview of some of the key counters that you might want to look at - http://www.extremeexperts.com/SQL/Articles/SQLCounters.aspx
There really are different sets to look at based on what your area of concern are. Since you mentioned memory, it is good to start looking at the counters in the buffer manager area, cache manager and memory manager. The basic counters are again mentioned in here.
Another great link that will help you get started on this is http://www.scribd.com/doc/399016/Tips-for-Using-SQL-Server-Performance-Monitor-Counters
One key counter that is often overlooked is the "Page Life Expectancy" counter in the SQL Server Buffer Manager counter set. Typically you want this to be above 300 on average. If you are less than this, or really low then you are most likely experiencing memory pressure.
You could also search for something like "SQL Server Memory Pressure" to get some other great insight on this and some great steps to isolate some of your issues.
Hopefully this helps and will ease some of the pressure that you are feeling from the manager. Never a fun experience.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 20, 2008 at 8:07 am
Hey thanks for the first link. looks kinda useful. The second link i already bumped into it but still thanks anyways. Yeah i know page life expectancy is a good counter but it wasnt good enough for my manager.:angry: I have come across some counters like forwarded records/sec this counter along with batch requests/sec gives a good indication if there are enough indexes present on the database.
why i like this counter is because in one of the databases i see only 4 clustered indexes for 33 tables present there. Looks like a good place to start. What do you guys have to say about this.
July 20, 2008 at 11:58 pm
It depends what you're looking for. What's your (or your manager's) definition of database health?
I'd start with things like Transactions/sec (to get the throughput), Buffer cache hit ratio and page life expectency (to see how well the data cache is been used), Checkpoint pages/sec and lazy writes/sec (to see if SQL is under memory pressure), locks/sec and avg lock wait time (to see if there's a locking problem), full scans/sec (to see how well the indxeing is working) and Page splits/sec (because page splits are expensive and cause fragmentation)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2008 at 9:02 am
Your boss seems very adament about what they don't want to see, so have you asked the question about what they do want to see? 😉 On a bit of a serious note though it is worth asking because you can come up with some smashing stats that give a lot of database information, and it's meaningless because no-one understands what they're seeing.
An alternative means of monitoring might be the SQL Health and History tool (SQLH2) which you can download here:
See if that silences the boss 🙂
July 22, 2008 at 10:41 am
Thanks Phil that was very helpful. I've also found the site below is helpful too. It has an easy to understand guide for using performance monitor.
Derkin, I've been in IT for many years and your boss sounds like a complete ***. I'd take a look at the "tools" you already have and see if you can figure out a way to win him over.
This site has nice explantion of why you should look at the big picture, instead of focusing on a single issue (CPU, Ram, disk I/o, etc)
http://www.computerperformance.co.uk/HealthCheck/Server_Health_Check.htm
Please post back and let us know what you learned.
Cheers
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply