performance counters in SQL Server

Question

performance counters in SQL Server

Miiimy7 41

Hello!

i want to monitor SQL Server in SQL Server (without using Perfmon), so i'll be using sys.dm_os_performance_counters. There's a lot of counters returned by this DMV.

First problem is i don't know which one are important.
Second problem is i don't know if it is enough to monitor SQL Server (SQL Server system included: CPU, Network, System,Disk,Memory,etc.) or do i need other DMVs
Third problem is : by listing counters in one CTE, i don't know what values to put on instance name.

Can you guys help me?
Thanks

3 answers

Your answer

Answer 1

Tom Phillips 17,771

I suggest you start here:

https://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

Miiimy7 41 Reputation points

2021-03-31T21:03:15.747+00:00

hello,
yes thank you!
But this article is about perfmon counters. SQL Server counters from sys.dm_os_performance_counters are included to perfmon.
But i want to add other counters ... I also want to monitor SQL Server OS... Process, processor, system, logicaldisk, network from SQL Server.
Tom Phillips 17,771 Reputation points

2021-04-01T16:39:17.403+00:00

SQL Server is not the tool to use to monitor general server level performance. There are many tools to do that task.

Answer 2

Olaf Helper 47,436

But i want to add other counters ... I also want to monitor SQL Server OS... Process, processor, system, logicaldisk, network from SQL Server.

SQL Server knows only it's own performance counter, not others.
How could be SQL Server aware of e.g. Exchange Server performance counter?

Use PerfMon or other tools to monitor performance counter.

Miiimy7 41 Reputation points

2021-04-01T06:52:44.497+00:00

Hello,
i want to monitor SQL Server OS. Each instance of SQL Server has its own SQLOS.
https://blog.sqlauthority.com/2015/11/11/sql-server-what-is-sql-server-operating-system/
tibor_karaszi@hotmail.com 4,316 Reputation points

2021-04-01T10:37:36.153+00:00

That is not how things work. The lower layers on SQL Server are called SQLOS, but it is only the parts that interacts with the "real" OS (Windows or Linux). SQLOS doesn't replace Windows/Linux. Bottom line is that you can only see SQL Server's own perfmon counters through the DMV,

To get things out side SQL Server, you need to be outside SQL Server. for instance using Powershell and GetCounter().,

Also note that the stuff you see in the DMV are the raw values, So make sure you read this before using the DMV: https://techcommunity.microsoft.com/t5/sql-server-support/interpreting-the-counter-values-from-sys-dm-os-performance/ba-p/317824
Miiimy7 41 Reputation points

2021-04-01T11:51:05.707+00:00

so counters from sys.dm_os_performance_counters are enough to monitor SQL Server? Or do i need more dmvs?

Answer 3

Hi @Miiimy7 ,

You can use sys.dm_os_performance_counters DMV collect counter information that you would receive from PerfMon for the various SQL Server counters. Let me stress that: this is for the various SQL Server counters. You will not be able to receive counter information from outside the SQL Server stack from this DMV. However for a quick and simple query to return information about your SQL Server instance's performance this is a great tool.

Refer to the blog SQL Server Performance Counter DMV sys.dm_os_performance_counters to get more information.

As Olaf mentioned, performance monitor is a better tool for your requirement.

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Share via

performance counters in SQL Server

3 answers

Your answer