This article (here) from LogicMonitor provides some interesting performance counters to review to isolate potential SQL Server performance issues. The performance counter charts are from the LogicMonitor monitoring tool, however, the same counters can be captured using your favourite performance counter capture tool.

In this article’s case, the SQL Server Full Scans had significantly increased, which also correlated to a much higher Write IO workload. However, at the same time the number of Work Files and Work Tables in the TempDB had also significantly increased. Therefore a review of temporary objects in the TempDB would assist in identifying the source of the higher TempDB activity.

The following Microsoft SQL Server 2005 article Working with tempdb in SQL Server 2005 (here) is still relevant with respects to Tempdb issue diagnosis. Also check out the Tempdb section in the Microsoft Whitepaper Troubleshooting Performance Problems in SQL Server 2008 (here)

The essence of the discussion is that you need set of performance counters that you periodically check to determine the current health of your SQL Server and to trigger the DBA to dive deeper into potential issues before the problems get out of hand.

Note the that DBInsight Monitor Service from DBInsight is based on the LogicMonitor framework but is heavily customised and enhanced to include more in-depth SQL Server monitoring and alerting. For a free 14 day trial call us on 1300 914 460 or email contact@dbinsight.com.au