Performance baseline report

Hi all,

I want to create a server performance baseline report for my database server. I know I can use System Mointor and SQL Profiler to monitor the server by reading the "SQL Server Books Online". However, there have too many counters, I don't know which counters should I use. If I choose to use the counter, I don't know what is the expect value for the counter.

For example, SQL Server: Buffer Manger Object has almost 22 counters. Which counters should I monitor? If I monitor AWE Lookup Maps/Sec, then what is the expect value for the good performance.

Anyone know a good refer or test book can help me to create a basline report

Thank a lot!

The following articles should help you going:

http://www.sql-server-performance.com/articles/audit/perform_performance_audit_p1.aspx

http://www.sql-server-performance.com/articles/per/10_baselining_tips_p1.aspx

-Sue

|||

Thanks the articles are really helpfull. Do you think the SqL Server Performance Monitoring and Management Tools such as SQL Diagnostic Manager or Spotlight on SQL Server Enterprise are helpful? If I design to buy those tools, do I need to create my own performance baseline report.

|||

The decision on purchasing those types of tools often depends on how many servers, databases you need to monitor per DBA and the nature of the databases, how critical they are to the business. Sometimes a "lightweight" shop can get by just buy rolling their own monitoring systems. But if you can get the money for the purchase, it certainly won't hurt! And some of them can really help a more junior level person get up to speed.

Personally, I liked Diagnostic Manager when I used it. I don't think you would really need to baseline separately if using DM. It has a repository to store the data captured and reporting functionality. It has built in reports and you can also write your own. So you can do trend reporting, analysis from a certain point in time and view changes, variances, etc. I know you can download a demo and try it out. Their support was good, company was helpful when evaluating this product - as well as others.

It's been too many years since I used Spotlight and years ago I didn't care for the performance overhead and all the junk in put in the database. I didn't like it, didn't feel it added much value and didn't like cleaning up from the install when we got rid of it. I know they've changed a lot since so I can't really give a decent opinion.

Not exactly the same thing but somewhat along the same lines and as long as we're on the general subject, have you looked at the Performance Dashboard reports from Microsoft? They are more to provide information at a given point in time in terms of performance. But you can export the reports out to files if needed. They are lightweight and do a good job of assessing performance issues, allowing drill through into various areas. You need to be on SQL 2005 SP2 to use them. No other requirements and they are free. Check the following link for a brief overview and a link to the download: http://blogs.msdn.com/sqltips/archive/2007/03/29/sql-server-2005-performance-dashboard-reports.aspx

-Sue