A topic I come back to on a regular basis is monitoring your database servers. This is one of the most overlooked practices on database servers. However, monitoring your server can provide a wealth of information.
There are a number of ways you can monitor your server. The best ways available are found in tools such as Spotlight. Some open systems tools are available. However, they tend to lean towards linux servers for implementation, or monitoring. Thus, if you are monitoring SQL Server, you need something that can monitor windows performance.
Of course you can roll your own monitoring using Windows Profiler. It takes more work on your part. But, if money is a factor, you can get a poor mans implementation up, saving the results to disk or a database.
So, what do you monitor? Here are some things I like to watch:
CPU utilization – Nice to separate for SQL Server, Operating System, and other apps
Memory Usage – Amount used, amount available
Cache Hit Ratio – Number of times necessary data was found in cache, not requiring reads from disk
Here are a few things I may turn on if I suspect performance degredation:
Disk Wait Times (read/write) – long waits result in degraded performance
Page Faults – Helps establish memory pressure by swapping memory to disk
You don’t need to test these values every second to get a trend. A range from fifteen seconds to a minute will probably provide you with more data that you can analyze. However, it will provide you with a trend that you can verify, and compare to your current performance. The comparision should tell you if your current performance is out of line with history.
Cheers,
Ben