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

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

[quoted text, click to view]

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.

[quoted text, click to view]

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

Hi

If your SQL Server counters are missing check out:
http://support.microsoft.com/default.aspx?scid=kb;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

[quoted text, click to view]

- 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."

[quoted text, click to view]