SQL Live Monitor

Hi All,

I have been assigned to maintain few SQL Server that are available in our VM environment. I searched in the internet and found a free application "SQL Live Monitor". I understand that this is basically a live display of different Perfmon counters.

SQL live monitor
Being a Citrix Administrator, I dont understand the significance of all the counters from SQL perspective. Can someone tell me which of them are really important and what action/ actions needs to be taken just in case if ther is some serious change in value happens.

OS: Windows 2003 Std.
SQL: SQL 2005
Who is Participating?
Tony303Connect With a Mentor Commented:
I take a holistic approach to SQL Server's in organisations.
It takes time to get the "feel" of how the business operates and how these system monitors reflect the business demands.

Say, if there is monthly invoicing going on, then you may see a big hit on the CPU for instance.
But, if this happens only once a month, for an hour or 2, is it a problem?.

But basically, monitoring can be split into 3 areas.
Below is a small guide, and certainly just a scratch at performance monitoring.
I wonder how many of these Indicators are in the SQL Live tool.
Although the tool seems to have let you know in red the problem area?

Hope it helps.

Pages/Sec                        Should never be consistantly higher than 0.
Page Faults/Sec                  Should be a low number
Buffer cache hit ratio            A high number here is good, >85%. Hitting cache rather than hard disk is desirable
Total Pages                  A low number is bad, meaning data is coming from disk rather than memory.
Page Life Expectancy                                  A high number here is good, pages are being replaced from disk to memory for use. If it is in the hours then the memory is sufficient to supply data rather than disk (slower).

% Disk Time                  Shows time disk responds to read/write requests, should be < 90%.
Avg Disk Queue length            Shows the avg number of read/writes in queue, ought not to be 2 x the number of drives.
Disk reads/sec                        Needs to be compared to the disk throughput ratings for the HD you are using                  
Disk rights/sec                        Needs to be compared to the disk throughput ratings for the HD you are using

% Processor time                  Should be consistantly below 90%
Processor Queue length            Obviously waiting is bad, if value over 2 then bad.
I would use Idera free tool, more intuitive in my opinion.

bubaibhattaAuthor Commented:
Thanks Tony, this is exactly what I was asking for. This tool certainly is not the ultimate one but a basic indicator.

Being a system administrator I understand some part of it since they also included on my general performance checklist. Since I never worked on SQL server, I basically was trying to understand the prime counters and their significance. I googled a lot and collected lots of info. But I no where could find what are area that I need to concentrate when users complain me about slowness in SQL server.

Can you help me on that?
Tony303Connect With a Mentor Commented:
The first place to go is the SQL Server Management Studio, I assume you have it on a management server or you'll have to connect to the SQL Server.

Once there, right click the server node, Activity Monitor. This will show CPU, processes, waits and expensive queries. Get the feel for what is "normal", then you'll get a feeling when things get busy.

You can right click a process and see the query syntax which should give you an idea about what processes are being run.
On the most expensive query screen you can right click also and see the query syntax and any suggested index creation.

I have my screen set up like this below... However, DBA work is much much deeper than this cursory look at the processes.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.