469,267 Members | 1,029 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,267 developers. It's quick & easy.

Desperate! - Need simple MSSQL server monitoring scripts, ideas, etc that can be performed by operator types (non-programmers)

Does anyone have any basic, simple scripts of sp's that I can give my
computer operators to use to monitor for serious conditions on our sql
servers? We are new in the ms-sql arena, a small shop and we cant
really purchase any tools to monitor these servers, but we need some
basic checks that we can use to make sure a server is performing in a
reasonable fashion, no blocks, cpu<xx%, memory not pegged, IO working,
etc...and we need to be able to do this without bugging our
programmers every time we are wondering why things are slowwnig down.

We are a split shop between as400s and sql servers, and our operations
staff has no problem performing this type of duty on the 400s. I think
since the sql servers are so easy to deploy into production, the idea
of how to monitor these things health got lost in the shuffle and
allowing the programmers to do it just serves to destabilize our
environment.

Please help! Our shop has turned to chaos since we went live with
MSSQL centric applications and everytime one programmer "resolves an
issue" some other programmers application starts to act up.
Jul 20 '05 #1
6 1930
I think that if you have AS400's, you should have the funds for a monitoring
tool.
Look at a product called "Spotlight on SQL Server" from Quest Software
www.quest.com . This 1 screen interface looks like the helm of the Starship
Enterprise.
Just by observing the screen, your operators can see if anything is wrong.
It has built in alarms that you can customize with your own thresholds (ie,
CPU > xx%, memory low, Cache hit ratio less than 90%, etc).

Oscar...

"freakyfreak" <em******@netscape.net> wrote in message
news:70**************************@posting.google.c om...
Does anyone have any basic, simple scripts of sp's that I can give my
computer operators to use to monitor for serious conditions on our sql
servers? We are new in the ms-sql arena, a small shop and we cant
really purchase any tools to monitor these servers, but we need some
basic checks that we can use to make sure a server is performing in a
reasonable fashion, no blocks, cpu<xx%, memory not pegged, IO working,
etc...and we need to be able to do this without bugging our
programmers every time we are wondering why things are slowwnig down.

We are a split shop between as400s and sql servers, and our operations
staff has no problem performing this type of duty on the 400s. I think
since the sql servers are so easy to deploy into production, the idea
of how to monitor these things health got lost in the shuffle and
allowing the programmers to do it just serves to destabilize our
environment.

Please help! Our shop has turned to chaos since we went live with
MSSQL centric applications and everytime one programmer "resolves an
issue" some other programmers application starts to act up.

Jul 20 '05 #2
Are you aware that from your own desktop you can run Windows PERFMON and set
the source for the data to your database server?

PERFMON will show you MANY SQL Server performance counters that you can
monitor either graphically or do a capture for later analysis.

"freakyfreak" <em******@netscape.net> wrote in message
news:70**************************@posting.google.c om...
Does anyone have any basic, simple scripts of sp's that I can give my
computer operators to use to monitor for serious conditions on our sql
servers? We are new in the ms-sql arena, a small shop and we cant
really purchase any tools to monitor these servers, but we need some
basic checks that we can use to make sure a server is performing in a
reasonable fashion, no blocks, cpu<xx%, memory not pegged, IO working,
etc...and we need to be able to do this without bugging our
programmers every time we are wondering why things are slowwnig down.

We are a split shop between as400s and sql servers, and our operations
staff has no problem performing this type of duty on the 400s. I think
since the sql servers are so easy to deploy into production, the idea
of how to monitor these things health got lost in the shuffle and
allowing the programmers to do it just serves to destabilize our
environment.

Please help! Our shop has turned to chaos since we went live with
MSSQL centric applications and everytime one programmer "resolves an
issue" some other programmers application starts to act up.

Jul 20 '05 #3
Thanks DHatheway, this is kind of cool. one question though, I cant
figure out how to connect perfmon to my server...runs fins at my pc
for my pc.
Jul 20 '05 #4
Hi

If your SQL Server counters are missing check out:
http://support.microsoft.com/default...b;en-us;227662

If it is to the run the application then either type in permon.msc /s from
the run command or a command prompt or use the option on the administrator
menu.

I would not recommend running this over the network, but you can change the
computer being monitored when you add your counter (Select counters from
computer).

John

"freakyfreak" <em******@netscape.net> wrote in message
news:70**************************@posting.google.c om...
Thanks DHatheway, this is kind of cool. one question though, I cant
figure out how to connect perfmon to my server...runs fins at my pc
for my pc.

Jul 20 '05 #5
....useful (sarcasm implied)
Jul 20 '05 #6
- Fire up PERFMON (start | run - then enter PERFMON in the RUN dialog box)
- It should pop up with a chart window populated by nothing. If not, click
"system monitor" from the tree on the left side.
- If you have a chart with entries, click on each line description in the
bottom right and delete each one (delete key).
- Then, right-click anywhere on the chart. This gets you a context menu
that includes "add counters..." Pick that option.
- Now you're looking at a dialog box that includes radio button options for
"use local computer" and "select counters from computer".
- Click on the "select counters from computer" and put the name of the
computer you want to monitor in the text box below (may have to use
double-slashes - e.g.: \\mydbserver)
- Click on the "Performance Counters" listbox. It will refresh with a list
of performance counters available on the remote computer (if it's a database
server, it should include a number of SQL Server performance counter
categories.
- Start selecting the ones you think relevant.

If you don't see counters for SQL Server listed for your database server,
John Bell's post of 10/9 5:05am points you to a resource to help you with
that problem.

There are also options for logging PERMON information. It's not what I'd
call intuitive but, once you get going this far, I think you'll be able to
figure it out.

Another option you have for PERFMON is to use Windows Terminal Server to run
a session on your database server and run PERFMON there. PERFMON running in
a WTS session on your database server will default to that server. I prefer
not to use WTS sessions on my database servers but it is a reasonable thing
to do for some management tasks. I'd strongly advise against doing anything
that would reduce security, like install or use Office or Internet Explorer,
etc. I keep my database servers "clean."

"freakyfreak" <em******@netscape.net> wrote in message
news:70**************************@posting.google.c om...
Thanks DHatheway, this is kind of cool. one question though, I cant
figure out how to connect perfmon to my server...runs fins at my pc
for my pc.

Jul 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Lenny Palozzi | last post: by
1 post views Thread by Water Cooler v2 | last post: by
14 posts views Thread by Kukurydz | last post: by
3 posts views Thread by Ian Frawley | last post: by
4 posts views Thread by =?ISO-8859-9?B?Wf1sbWF6?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.