What are some ways I can constantly monitor and report on my production sql server so that I can determine where locks and performance improvements can be made?
migrated from stackoverflow.com May 24 '11 at 0:10
This question came from our site for professional and enthusiast programmers.
This is a very open ended question and the answer varies whom you talk to as there is so much that can be done.
At a minimum, you should do the following.
This is a very high level starting point and don't consider this as a final list though.
Being able to monitor the SQL server continuously can provide valuable information about performance and effectiveness. Fortunately, these capabilities are fairly straightforward to use for those with administrative capabilities on the server. When the server was first installed, certain performance measurements were automatically installed along with it. The performance tools allow for real-time monitoring, local monitoring, and remote monitoring. Additionally, they can be used to create logs and charts based upon the performance which can lend themselves to analysis and reports of the SQL server.
Real Time, Continuous Monitoring
The administrators can access and modify these monitors by going to Start, Programs, Administrative Tools, and Performance. The administrator will want to add counters to monitor the server according to their desired performance criteria. They can elect to add all the counters available, or hand select particular monitoring tools. Some of the more popular and relevant tools include the capacity to monitor the memory usage of the server, the CPU activity, paging, the amount of time a select disk is busy, and the amount of time the processor is performing operating system processes.
Logging and Reporting Information
Once the real-time counters have been established, administrators should then proceed to the Performance Logs and Alerts window. From there, they can right click on the Counter Logs and select New Log Settings. They should then add the counters they want to have recorded. There is also an option to add a specific period of data to record. If this is not utilized, then the system will continue to record the information until it is personally stopped.
After the period of logging the data is completed, the information is easily converted to charts and graphs. Begin by opening the System Monitor, click on the View Log File Data button, followed by a click on the Add button. If the information was logged for an extended period of time and all the data is not required for the analysis, the Time Range sliding button can be used to select a specific period of time. Under the Data tab, the administrator selects the counters to be included, followed by the Graph and Appearance tabs to select their preferences in that area. The information will then be converted to an easy to view chart. The information can also be exported to Excel for additionally monitoring and reporting.