SQL 2000 Monitor your server

Monitoring your SQL Server

Your mission is to detect and eliminate performance bottlenecks. 

Run a health check and get the most out of your SQL 2000 servers.  One of the most satisfying feelings is improving performance by finding bottlenecks on your network.  As a side effect you will learn how the server components work and how they compete for resources.

Always monitor the ‘big four’ counters : Memory, Processor, Disk and Network.  If you choose one counter in isolation it can lead to the wrong conclusions.  One company measured just disk counters; they found long queues and slow access times.  Their conclusion was the disk was the bottleneck and so they bought new disks.  Unfortunately, the problem persisted and they called me in to investigate.  By setting up a performance monitor log, I discovered that the true problem was lack of RAM causing excessive paging, this  made a memory problem appear as a disk problem.  The lesson: incomplete monitoring meant a waste of time and money, so always monitor these four objects:-

• Memory – Do you have enough RAM?

• Processor – Is your CPU being stressed?  Do you need more processors?

• Disk – Is the queue to your disk the bottleneck?

• Network – What is the Network Utilisation?  Do you need to subnet?

Be warned, there are tens if not hundreds of counters. ‘Server Health Check’ is a huge topic and my aim is to get you started.  As you gain experience so you become expert.  The counters that you see in SYSTEM MONITOR depend on the services that you install; for example Exchange and SQL have their own specialist counters which are only added when you install those products.

Question: Is System Monitoring and art or a science?  Methodical recording and analysis is important, but there is no substitute for experience and interpretation of the data.

Tools:

PerfMon.exe = System Monitor (Formerly performance monitor).

To get started, on a Windows machine go to your START Button, select RUN, type Perfmon then ENTER.

Run PerfMon from a workstation or laptop.  The first report I ever did was criticised because the customer said my figures were biased because PerfMon itself was causing the bottleneck.  They were wrong but I learnt from the experience and I now always use an an independent machine to monitor the servers.

Task Manager

Useful for a quick look at processes and performance.  Task manager is the poor man’s system Monitor.

Resource Kit:

PViewer, Tlist, Pfmon, PMon, Perfmtr

Tactics and Strategies

Assuming that you have looked at live data in the System Monitor, the next step is to create a performance log.  So, go to Start, Run, Perfmon; then look for ‘Performance Logs and Alerts’, Counter Logs.  Next right-click ‘Counter logs’ and select ‘New log settings’ and give it a name.  Now comes the crucial step, click on the ‘Add’ button and make your selections.  I know it is easy to say make your selections, but it is here that your skill and perseverance, together with advice will make you an expert.

Baseline

The idea of the baseline is to look at server performance when there are no users on the network.  A baseline will give you a picture of the server when it is just ticking over.  So record the log at night and compare the counters to when the users are at work.

System Monitor Tips:

• Use CTRL H or the Highlight Button on the tool bar
• Check the EXPLAIN button on the counter that you are interested in
• Create Counter Logs
• Create HTML files from the Counter Logs (Save AS HTML)
• For intermittent problems, set Alert Logs.  Then check the Application log in the Event Viewer

For specific advice see : –

1. Memory – Check you have enough RAM

2. Processor – See if your CPU being stressed

3. Disk – Is disk access your bottleneck?

4. Network – What is the Network Utilisation?