Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from stackoverflow.com May 24 '11 at 0:10

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 2 down vote accepted

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.

  1. Look at these diagnostic queries from Glenn Berry and find out the expensive procs based on IO, CPU etc...

http://sqlserverperformance.wordpress.com/2011/05/16/may-2011-sql-server-2008-diagnostic-information-queries/

  1. Look at Perfmon counters and act upon what you find that stand out.

http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

  1. Look at memory usage, plan cache etc...

  2. Look at sys.dm_io_virtual_file_stats and identify if you have IO bottlenecks

  3. Proper Index maintenance including updating the statistics

  4. Use sp_whoisactive to look at the current activity on the server.

http://sqlblog.com/files/folders/release/entry35240.aspx

This is a very high level starting point and don't consider this as a final list though.

share|improve this answer
    
Thanks for this info. I will look into this now –  King Kyle May 24 '11 at 1:39
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.