How to check the load on sql server

Question

Post reply

How to check the load on sql server

bobrooney.81

SSC-Addicted

Points: 468
More actions
May 21, 2022 at 5:50 am

#4036574

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
May 23, 2022 at 12:10 pm

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 1

Save yourself some time... Look for Adam Machanic's "sp_WhoIsActive" code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bobrooney.81 SSC-Addicted Points: 468 More actions · Answer 2

Anyone has any perfmon.msc file to measure how busy is my sql server?

bobrooney.81 SSC-Addicted Points: 468 More actions · Answer 3

bobrooney.81

SSC-Addicted

Points: 468

May 22, 2022 at 12:26 pm

#4037122

How can sp_whoisactive help?

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 4

bobrooney.81 wrote:

How can sp_whoisactive help?

Have you searched for it yet? No sense in me repeating something that's readily available. 😀 Reading about it is a lesson about SQL Server in itself.

But, to summarize, it can tell you what your worst queries are and how much of a load their putting on your system. Brent Ozar also has some awesome tools in his "First Responder Kit", which is totally free.

The good part of both is you can read the code to see what they're looking at and learn even more.

If you don't want to go either route, then buy some monitoring software.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 5

bobrooney.81 wrote:

Anyone has any perfmon.msc file to measure how busy is my sql server?

I do... but PerfMon is a picky thing... what works on one box frequently doesn't work on another. I can send you a list of what's in my PerfMon so that you can look for the same things.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bobrooney.81 SSC-Addicted Points: 468 More actions · Answer 6

Jeff Moden wrote:

bobrooney.81 wrote:
Anyone has any perfmon.msc file to measure how busy is my sql server?
I do... but PerfMon is a picky thing... what works on one box frequently doesn't work on another. I can send you a list of what's in my PerfMon so that you can look for the same things.

Please send it and I' ll use it on my servers. Apart from Batch request/sec , cpu % time what else can I check?

Ken McKelvey SSCoach Points: 18842 More actions · Answer 7

Here is an article by Brent Ozar which includes performance counters:

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

I suspect the main use for PerfMon information is when there seems to be load on the server but not on SQL. You can then ask the sysadmins what is happening using a tool they understand.

As a DBA most of the monitoring you need involves DMVs and extended events. I would take Jeff's advice and look at sp_whoisactive.

Jameswalter Newbie Points: 8 More actions · Answer 8

Once you connect to your SQL Server or Azure SQL instance, you can select Reports > Performance Dashboard and see the current and historical values of CPU usage. Here you can find the query texts of the top resource consumers and identify the queries that are causing the CPU issues.