performance parameters.,,,

Dear all

Can any one provide me details of the parameters to be monitored in sql server 2005

Currently i am monitoring

SQLServer : Buffer Manager : Buffer Cache Hit Ratio

SQLServer : Databases : Log Flushes / Sec

SQLServer : Databases: Transactions / Sec

SQLServer : Access Methods : Page Splits / Sec.....

1) Please provide if any other relevant parameters to be monitored by perfmon.

2) Which are the most essential parameters to be monitored.

3) Is there any query or trace that will provide me performance details in SQL.

4) Can i have a query that get me the most resource consuming process or query in sql server.

5) wht parameters do i need to monitor on weekly and monthly basis for sql server 2005.

It would be very helpful if you guys could provide me help on these.

Thanks in advance

That always depends upon what you are attempting to monitor. However, that said, there are a few 'base' counters to monitor. I've included a group of articles that will provide you excellent information about performance monitoring and performance tuning.

Performance Audit
http://www.sql-server-performance.com/articles_audit.asp
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

Performance -Link Server Performance Tips
http://www.sql-server-performance.com/linked_server.asp

Performance Monitoring
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx Performance WP's
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx Troubleshooting Performance 2005
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
http://support.microsoft.com/?id=271509 Script to Monitor Blocking

Performance Tuning -Articles
http://www.sql-server-performance.com/articles_performance.asp

Performance Tuning –Hardware
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp

|||

As Arnie said, it really depends on your workload, and what your normal baseline values are. Having said that, here are some basic "rules of thumb" for those counters:

SQLServer : Buffer Manager : Buffer Cache Hit Ratio - Higher is better, below 90% is considered very bad. Its one sign of memory pressure.

SQLServer : Databases : Log Flushes / Sec - Lower is generally better

SQLServer : Databases: Transactions / Sec - This is one measure of how "busy" your system is. Batch Requests/Second is another measure that can be more helpful.

SQLServer : Access Methods : Page Splits / Sec - Lower is better. Having indexes with very high fill factors on volatile tables will cause more page splits, which leads to fragmented indexes, which causes more I/O.