Performance Counters

The following list of performance counters have been modified to include the most pertinent in identifying the bottleneck of your system. This list is for reference purposes. If needing to troubleshoot slowness read troubleshooting sql server slowness. Memory Object: – Memory Counter: – Available Mbytes Preferred Value: – > 2000MB Description: With 64 bit OS’s the available bytes recommendation has increased. Object: – Memory Counter: – Pages/Sec Preferred Value: – < 50 Description: - Pages/sec is the rate at which pages are read from or written to disk to resolve hard page faults. Indicative of possible page file usage, and definitely a memory bottleneck somewhere on the system. Object: - Paging File Counter: - %Usage Preferred Value: - < 5% Description: - Current amount of Page file being used. This is a key number when weighing the amount of memory allocated to the OS. If this number is high, then the OS is choked for RAM. Either increase the RAM on the box or deallocate from SQL. If this is between 10% and 25% you should reboot. Object: - Paging File Counter: - %Usage Peak Preferred Value: - < 15% Description: - Show the peak the page file got to since the last reboot. Usually this is not too far off from the page file usage %. Unfortunately when page file issues happen, they do not correct themselves without a reboot. Object: - SQL Server:Buffer Manager Counter: - Page Life Expectancy Preferred Value: - > 300 Description: – The single […]

Continue reading ...

Find Missing Table and Index Statistics

SELECT
Last_Updated = STATS_DATE(si.id, si.indid)
,TableName = object_name(si.id)
,Name = RTRIM(si.name)
,Size = DATALENGTH (si.statblob)
FROM
sysindexes si WITH (nolock)
WHERE OBJECTPROPERTY(si.id, N’IsUserTable’) = 1
–AND INDEXPROPERTY (si.id , si.name , ‘IsAutoStatistics’ ) = 0
order by last_updated, tablename

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!