MVP recommends investigating the following performance metrics to examine SQL performance. 

SQLServer : Memory Manager : Total Server Memory 
SQLServer : Memory Manager : Target Server Memory 

These two counters are the best way to know if SQL Server has enough RAM to operate properly. 

Total Server Memory indicates the current RAM currently in use by the SQL Server Service. This value includes the total number of buffers committed to SQL Server and the OS buffers of the type OS in use. 

Target Server Memory indicates how much RAM the SQL Server Service would ideally like to have. 

Generally, if the Total Server Memory counter is less than the Target Server Memory, then SQL Server has enough memory to run efficiently. 

If, over time, the Total Server Memory value is equal to or higher than the Target Server Memory, the SQL Server Service may be under serious memory pressure and an increase in RAM is needed. 

By the way, don't just add physical RAM to the server.(You didn't think it was going to be that easy, did you?) 

You need to have the Server Administrator look at the properties of the SQL Server (using SQL Server Enterprise Manager in SQL Server 2000 or SQL Management Studio in SQL Server 2005). 

Check the service is configured to use the newly installed RAM once it is added. If SQL Server is configured to use a fixed amount of memory, adding physical RAM is useless - you need to the limit once the memory has been installed. 



SQLServer : Access Methods : Page Splits/Sec 

Page splitting cause excessive I/O on your SQL Server machine. Page splitting generally starts when a page of SQL Server data becomes full and is then split between the current and a newly allocated page. 

Occasional page splitting is a normal condition in SQL Server, but when this particular counter is high, excessive disk I/O is generally the result - with subsequent slow performance to the clients of the SQL Server. 

If this counter exceeds 100 over 25% of the time you have a problem. The solution is to increase the FILL FACTOR value in SQL Server Properties - Database Settings. 




SQLServer : SQL Statistics : Batch Requests/Sec 
SQLServer : Databases: Transactions/Sec 

Getting a feel for how busy SQL Server actually is can be complicated. A good starting point is Batch Requests/Sec. 

A DBA may state that Transactions/Sec is a more accurate measurement, but this is incorrect because Transactions/Sec only measures activity that is inside an actual transaction and not all activity. This can produce seriously skewed results. 

Batch Request/Sec measures all SQL Server activity. 

In general, Batch Request/Sec over 1,500 shows a very busy SQL Server environment. 

But this is in general -- if the server is running on a modern SMP machine sitting on a gigabit network segment this figure can be as high as 3,000; a typical 100 megabit network card can handle about 4,000 batch requests per second. 

If the machine is a uni-processor, and over 1,500, tell your boss you need a bigger machine... 



SQLServer : Buffer Manager : Buffer Cache Hit Ratio 

This counter is an indication of how often SQL Server is able to get data from the RAM buffer instead of the hard disk. A higher ratio is an indication of better performance. 

A good target for the Cache Hit Ratio is anything above 85 percent. Anything less than 85% indicates the server needs more RAM. 

Remember the counter is an accumulation from the time the SQL Server Service is started. The SQL Server Service should be restarted and watched for several hours before an accurate picture of the Buffer Cache Hit Ratio can be gathered. 



Memory : Pages Per Second 

This value should always be less than 100 on a production SQL Server. 

This particular counter is one of the most overlooked values in the entire Windows operating system. If the value for Pages Per Second is above 100 on a regular basis, the operating system itself may not have enough RAM. 

Check the properties of the SQL Server and ensure SQL Server is not using 100 percent of the RAM on the server. Ensure enough RAM is allocated to Windows for other tasks. 

A quick and dirty way of measuring this is to simply use Task Manager to view the value for Available Physical Memory on the Performance tab. 

Do not confuse this counter with Page Faults Per Second 

A Page Fault means the required page is not currently paged in, but it may be in the discarded page list in memory. If the page is in the discarded page buffer, that is, it's in another buffer still in RAM, a physical page-in from disk is not needed; the Pages Per Second is not incremented. 


Is Defragmentation Necessary? 

If ODBC timeouts are an issue, fragmentation may be the cause. 

You can buy a third-party defragger. 

Because SQL Server data files and transaction logs tend to be very large, extensive fragmentation may be a real issue. I/O access can be slowed significantly. 

Fragmentation is particularly evident when range scans and table scans take place, as these operations read a large amount of data. If the drive head has to move all over the drive platter to locate the required data, time is wasted and timeouts can occur.