question

Miiimy7-1570 avatar image
0 Votes"
Miiimy7-1570 asked TomPhillips-1744 commented

performance counters in SQL Server

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


sql-server-generalsql-server-transact-sqlazure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 commented
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered Miiimy7-1570 commented

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.



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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/

0 Votes 0 ·

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

0 Votes 0 ·

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

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @Miiimy7-1570,

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.