question

Bobsql-8788 avatar image
0 Votes"
Bobsql-8788 asked Yufeishao-msft commented

perfmon counter to check the load on the server

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

sql-server-general
· 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.

Hi

check the load on the server

Check the load of what to where?!?

Do you mean to check INSERT data to table, or maybe loading data page into the buffer memory?

Anyway, this toturial can help you better use SQL Server Perfmon so maybe this will cover your needs:

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

0 Votes 0 ·

I meant how to measure how busy is SQL Server?

0 Votes 0 ·
pituach avatar image
1 Vote"
pituach answered

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://docs.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

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.

Yufeishao-msft avatar image
1 Vote"
Yufeishao-msft answered Yufeishao-msft commented

Hi @Bobsql-8788,

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.




· 1
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.

If the reply above was helpful please mark as accepted answer so it can be helpful for other community members with same questions.

Regards

0 Votes 0 ·
Bobsql-8788 avatar image
0 Votes"
Bobsql-8788 answered

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

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.

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered

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

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.