Here is my “compil” about SQL Server Performance:

SQL Nexus tool: http://sqlnexus.codeplex.com/

Microsoft SQL performance monitoring and tuning HOW-TO: http://msdn.microsoft.com/en-us/library/ms187830(v=sql.105).aspx

Performance analysis with SQL 2008 activity monitor tool : http://www.mssqltips.com/sqlservertip/1917/performance-analysis-using-sql-server-2008-activity-monitor-tool/

SQL IO statistics: http://novicksoftware.com/Articles/sql-server-io-statistics.htm

Free Microsoft tools to use perfmon for SQL : http://www.mssqltips.com/sqlservertip/1607/free-microsoft-tools-to-help-setup-and-maintain-perfmon-for-sql-server/

Tips for Using SQL Server Performance Monitor Counters : http://www.sql-server-performance.com/2005/sql-server-performance-monitor-coutners/

in short:

– first check the well-known Windows OS counters and their thresholds,

For SQL server typically the well-known counters are below:

– A key counter to watch is the SQL Server Buffer Manager Object: Buffer Cache Hit Ratio : The higher this ratio, the less often SQL Server has to go to the hard disk to fetch data, and performance overall is boosted

SQLServer: SQL Statistics: Batch Requests/Sec counter : over 1000 batch requests per second indicates a very busy SQL Server

– SQLServer: SQL Statistics: SQL Compilations/Sec counter : over 100 compilations per second, then you may be experiencing unnecessary compilation overhead

– SQLServer : User Connections : This shows the number of user connections, not the number of users

– SQL Server Locks Object: Average Wait Time (ms) : If your users are complaining that they have to wait for their transactions to complete

– SQL Server Backup Device Object: Device Throughput Bytes/sec (You will also want to use the Physical Disk Object: Avg. Disk Queue Length counter to help    collaborate your suspicions) : If you suspect that your backup or restore operations are running at sub-optimal speeds

– Consider watching these two counters: SQLServer:Memory Manager: Total Server Memory (KB) and SQLServer:Memory Manager: Target Server Memory (KB). The first counter, SQLServer:Memory Manager: Total Server Memory (KB), tells you how much the mssqlserver service is currently using. This includes the total of the buffers committed to the SQL Server BPool and the OS buffers of the type “OS in Use”.

 

Free tools:

http://www.manageengine.com/sql-performance-monitor/sql-server-monitoring-index.html

http://www.idera.com/en/ProductsSolutions/FreeTools.aspx