SQL Server 2005 PERFMON counter spikes in Transactions/sec and Buffer Cache Hit ratio, what does it say?

SELECT
      (CAST(SUM(CASE LTRIM(RTRIM(counter_name)) WHEN 'Buffer cache hit ratio' THEN CAST(cntr_value AS INTEGER) ELSE NULL END) AS FLOAT) /
      CAST(SUM(CASE LTRIM(RTRIM(counter_name)) WHEN 'Buffer cache hit ratio base' THEN CAST(cntr_value AS INTEGER) ELSE NULL END) AS FLOAT)) * 100
      AS BufferCacheHitRatio 

FROM 
      sys.dm_os_performance_counters 
WHERE 
      LTRIM(RTRIM([object_name])) LIKE '%:Buffer Manager' AND 
      [counter_name] LIKE 'Buffer Cache Hit Ratio%'

As you may have seen the DMV sys.dm_os_performance_counters, by its name is an useful DMV when you need to capture the server resources information. Since SQL 2005 inception I have been using this view to obtain system information rather than running Performance Monitor (SYSMON). So what you see on the results from this query will return the Buffer Cache Hit Ratio for the current moment on the server. This is the percentage of pages requested by SQL Server that were found in memory, you should always see the value over 90% and if it is much lower than that, it means that your server is going to disk to retrieve data pages, and it may be a sign that your server needs more memory. If in case your SQL Server is not a dedicated one then make sure to run SYSMON to capture non-SQL Server related data as the above DMV will only get for SQL Server.

So when you see a jump on the above mentioned counters then you should be concerned about load on your SQL Server, such as open transactions and bulk load tasks. As the Transactions/Sec does not measure activity or give information unless a query is specified inside a transaction (BEGIN..END).  So in this case you could obtain value for Batch Requests/sec too that will give information about batches sent to the server even they are not part of a transactional mode. So in this case you should monitor the SELECT statemented queries along with UPDATE/INSERT/DELETE statements within a stored procedure or view.

As you may be aware that SQL Server 2005 uses the TEMPDB extensively where in this case you will observe this behaviour of transactions/sec will shoot up or a spike within this counter.  This may occur if you have enabled AUTO_SHRINK on any of the database or any database is waiting to increase its size such as with AUTOGROW settings.  To see futher even though if you use the PROFILER to capture these events you will not get anything shown out of SQLStatement or Batches event, what you will see is GhostCleanup Task if your database has any of the settings above. So not that easy if you need get accurate numbers on transactions/sec on your database,  as referred in the beginning you need to be clear that Transactions/sec shows number of transactions (local, dtc and network based)  stored for the database and Active transactions are number of active 'update'  transactions for that database.  Another counters you should observe is 'buffer cache ratio' will be lesser for larger database as compared to transactions/sec counter, this is normal when you have the workload and number of disks the data has been spread out.

During the above Performance audit task I have accumlated the counters such as Object: - SQLServer:Access Methods, Workfiles Created/Sec and another DMV sys.dm_db_index_physical_stats to get information regarding data and index information for data tables and views. So what you will see within the number of work files created per second when the TEMPDB workfile are used in processing the operations such as when the amount of data being processed is too big to fit into the available memory which is called as 'hash operation'.  So using the Access Methods: Workfiles Created/sec counter you will get more details in similar to work tables only by this hash operation,  also these work files are used to store temporary results for hash joins and hash aggregates within your queries.

As it referred from the documentation worktables are internal temporary objects that are created by when the operations are in progress such as sorting operations, unions, and other similar things. Whereas a workfile is a similar temporary object, but it is something created strictly by a hashing operation.  So to optimize both these operations you can lookup the index creation and their defragmentation methods. For instance refer to this Webcast and review this TEMPDB-SQL2005 document for more information on best practices methods.