perfmon counter to check the load on the server

Question

Hi,

I am using SQL Server 2016 EE. Want to know what are the list of minimal perfmon counters to check the load on the SQL Server and why there are perfect to check the load on the server. I want to trend this over a period of time.

Thanks,
Bob

Answer 1

Hi,

I meant how to measure how busy is SQL Server?

You need to be more focused. By "how busy is SQL Server" do you mean the CPU of the machine, Memory or maybe number of transactions per second or Maybe you want t monitor the IO

As a start point to get most of the information in a GUI interface, you can use Activity Monitor in SQL Server Management Studio

https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/open-activity-monitor-sql-server-management-studio?view=sql-server-ver15&WT.mc_id=DP-MVP-5001699

Or you can use the dashboard reports of the SQL Server Management Studio

https://www.sqlshack.com/how-to-collect-performance-and-system-information-in-sql-server/

For more details and using transact SQL queries we should know what exactly do you want to monitor, as I mentioned above

Answer 2

Hi @Bob sql ,

You can refer to this:Windows Performance Monitor Counters for SQL Server
From this article, it has a list of Perfmon Objects commonly used to review SQL Server performance along with their descriptions and a guide of expected values.

To measure how busy is SQL Server, you still can consider the Data Collection and MDW(Management Data Warehouse), it is a feature for performance monitoring and tuning available in SSMS. It has some built into data collection sets server activity, disk usage, and query statistics

https://solutioncenter.apexsql.com/monitor-sql-server-queries-find-poor-performers-activity-monitor-and-data-collection/

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Can you please provide specific counters so that I can check while doing Live Troubleshooting. like batch requests/sec etc...

Answer 4

according to this:https://www.mssqltips.com/sqlservertip/6933/windows-performance-monitor-sql-server-introduction/

Start> Search enter Performance Monitor in the search box to start performance monitor