Contadores

 SQL Performance Counters  |
Object | Counter | Preferred Value | Description |
SQLServer:Access Methods | Forwarded Records/sec | < 10 per 100 Batch Requests/Sec | Rows with varchar columns can experience expansion when varchar values are updated with a longer string.  In the case where the row cannot fit in the existing page, the row migrates and access to the row will traverse apointer.  This only happens on heaps (tables without clustered indexes). Evaluate clustered index for heap tables.  In cases where clustered indexes cannot be used, drop non-clustered indexes, build a clustered index to reorg pages and rows, drop the clustered index, then recreate non-clustered indexes. |
SQLServer:Access Methods | Full Scans / sec | (Index Searches/sec)/(Full Scans/sec) > 1000 |This counter monitors the number of full scans on base tables or indexes. Values greater than 1 or 2 indicate that we are having table / Index page scans. If we see high CPU then we need to investigate this counter, otherwise if the full scans are on small tables we can ignore this counter.  A few of the main causes of high Full Scans/sec are
• Missing indexes
• Too many rows requested
Querieswith missing indexes or too many rows requested will have a large number of logical reads and an increased CPU time. |
SQLServer:Access Methods | Index Searches/sec | (Index Searches/sec)/(Full Scans/sec) > 1000 | Number of index searches. Index searches are used to start range scans, single index record fetches, and to reposition within an index. Index searches are preferable to index andtable scans.  For OLTP applications, optimize for more index searches and less scans (preferably, 1 full scan for every 1000 index searches). Index and table scans are expensive I/O operations. |
SQLServer:Access Methods | Page Splits/sec | < 20 per 100 Batch Requests/Sec | Number of page splits per second that occur as the result of overflowing index pages. Interesting counter that can lead usto our table / index design. This value needs to be low as possible. If you find out that the number of page splits is high, consider increasing the fillfactor of your indexes. An increased fillfactor helps to reduce page splits because there is more room in data pages before it fills up and a page split has to occur.Note that this counter also includes the new page allocations as well anddoesn’t necessarily pose a problem.  The other place we can confirm the page splits that involve data or index rows moves are the fragmented indexes on page splits. |
SQL Server:Buffer Manager | Buffer Cache hit ratio | > 90% | This counter indicates how often SQL Server goes to the buffer, not the hard disk, to get data. The higher this ratio, the less often SQL Server has to go to the hard disk tofetch data, and performance overall is boosted. Unlike many of the other counters available for monitoring SQL Server, this counter averages the Buffer Cache Hit Ratio from the time the last instance of SQL Server was restarted. In other words, this counter is not a real-time measurement, but an average of all the days since SQL Server was last restarted. In OLTP applications, this ratio shouldexceed 90-95%. If it doesn't, then you need to add more RAM to your server to increase performance. In OLAP applications, the ratio could be much less because of the nature of how OLAP works. In any case, more RAM should increase the performance of SQL Server OLAP activity. |
SQL Server:Buffer Manager | Free list stalls/sec | < 2 | Free list stalls/sec is the frequency with which requests foravailable database pages are suspended because no buffers are available. Free list stall rates of 3 or 4 per second indicate too little SQL memory available. |
SQL Server:Buffer Manager | Free pages | > 640 | Total number of pages on all free lists. |
SQL Server:Buffer Manager | Lazy Writes/Sec | < 20 | This counter tracks how many times a second that the Lazy Writer process is...