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.

Env:
OS: Windows 2003 Std.
SQL: SQL 2005
SQL-LIVE-Monitor.jpg
bubaibhattaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dipopoCommented:
I would use Idera free tool, more intuitive in my opinion.

http://www.idera.com/productssolutions/freetools
0
Tony303Commented:
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.

Memory
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).


I/O
% 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

CPU
% Processor time                  Should be consistantly below 90%
Processor Queue length            Obviously waiting is bad, if value over 2 then bad.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
0
Tony303Commented:
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.

http://www.google.co.nz/search?q=sql+activity+monitor&source=lnms&tbm=isch&sa=X&ei=BrcwUuHiJIuhigfgnYDgBA&ved=0CAcQ_AUoAA&biw=768&bih=928&dpr=1#biv=i%7C6%3Bd%7Cy6gJRW2KwmNsBM%3A
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.