In today’s digital age, staying up-to-date with the latest technology trends is crucial. Social media platforms like Twitter have become immensely popular, connecting people from all walks of life. It was through Twitter that I had the pleasure of meeting Kevin Mckenna, a SQL and .NET expert. Kevin shared some valuable insights on accessing performance counters from system views in SQL Server, and with his permission, I am sharing this information with you.

Many people are unaware that SQL Server provides easy access to performance information without the need for custom applications or perfmon. The key to accessing this information lies in the system view called sys.dm_os_performance_counters. This view allows you to retrieve SQL Server performance counter information that is typically viewed through perfmon.

Let’s start with a simple example:

SELECT * FROM sys.dm_os_performance_counters

This query will return a list of performance counter information, including data from Buffer Manager, Buffer Partition, Buffer Node, General Statistics, Locks, and more. It’s a comprehensive view that provides valuable insights into the performance of your SQL Server.

For a more focused view, let’s concentrate on the Buffer Manager:

SELECT * FROM sys.dm_os_performance_counters WHERE OBJECT_NAME = 'SQLServer:Buffer Manager'

This query will display specific performance counter information related to the Buffer Manager. The columns returned include object_name, counter_name, instance_name, cntr_value, and cntr_type. These columns provide valuable information about the performance of your SQL Server.

Now that we have access to this information, what can we do with it? One option is to create a job that samples a particular value on a timed basis and stores it in a table. This allows you to view average values and track trends over time. For example, you could chart the number of active transactions during the workday by storing the values in a table and querying them later.

Here’s an example of how you can store the values in a table:

CREATE TABLE ActiveTrans_sqlGenus (
  obj_name NCHAR(128),
  counter_name NCHAR(128),
  instance_name NCHAR(128),
  cntr_value BIGINT,
  cntr_type INT,
  datestamp DATETIME
)

INSERT INTO ActiveTrans_sqlGenus
SELECT OBJECT_NAME, counter_name, instance_name, cntr_value, cntr_type, GETDATE()
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'SQLServer:Databases'
  AND counter_name = 'Active Transactions'
  AND instance_name = 'sqlGenus'

Assuming you are consistently inserting values into this table, you can then retrieve the entries for a specific time range:

SELECT * FROM ActiveTrans_sqlGenus
WHERE datestamp BETWEEN '06/02/2009 09:00' AND '06/02/2009 17:00'

This query will return all the entries between 9am and 5pm, allowing you to analyze the performance of your SQL Server during that time period.

While this is a simple example, it demonstrates the power of accessing SQL Server performance counters through system views. By manipulating and analyzing this data, you can gain valuable insights into the health and performance of your database.

It’s important to note that SQL Server’s system views only provide information specific to SQL Server itself. Information related to processors, network, and disk performance still needs to be obtained through performance counters. However, the sys.dm_os_performance_counters view provides more than enough information to keep your database in excellent shape.

If you’re interested in learning more about the sys.dm_os_performance_counters view, you can find additional information on the MSDN website.

Stay tuned for more SQL Server tips and tricks!