SQL Server built-In monitoring components

First, It should have been determined on where/what to monitor for the current situation. right ? Yes!

And, select an appropriate tools to proceed with. It can be either from Windows Itself Or SQL Server specific

Windows Monitoring Tools:

• System Monitor
• Performance Monitor
• Task Manager
• TypePerf

SQL Server Tools:

• Activity Monitor
• Database Console Commands (DBCC)
• Distributed Replay Utility
• Dynamic Management Views and Functions
• Extended Events
• Functions
• SQL Server Management Studio Graphical Showplan
• SQL Server Profiler
• SQL Trace
• System Stored procedures
• Trace flags

Note: 

• SQL Trace and SQL Server Profiler are deprecated. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
• Use Extended Events instead

Source: https://docs.microsoft.com/en-us/

TypePerf - Command-Line Tool

We have Windows Operating system Command-Line Tool called "TypePerf" Is used to collect performance metrics periodically and store it in a File or SQL Table for further analysis on what time and what kind of overhead taken place in Server/SQL Instance like Physical/Logical Memory/Disk/Processor/IO,.. Etc,...

Here is the syntax

1. List all the Performance Objects and Counters (Without Instance)

TypePerf -q

2. List all the Performance Objects and Counters (With Instance)

TypePerf -qx

3. List counters of a specific Object (i.e: PhysicalDisk)

TypePerf -q \PhysicalDisk

4. List counters of a specific Object with Instance (i.e: PhysicalDisk)

TypePerf -qx \PhysicalDisk

5. Show metrics for a specific Object with all Instances (i.e: PhysicalDisk)

TypePerf "\PhysicalDisk(*)\Current Disk Queue Length"

* - Represents all Instance of the Object "PhysicalDisk"

6. Show metrics for a specific Object with all Instances (i.e: PhysicalDisk) only 4 samples

TypePerf "\PhysicalDisk(*)\Current Disk Queue Length" -sc 4

By Default - Shows metrics until press CTRL + C

7. Show metrics for a specific Object with all Instances (i.e: PhysicalDisk) only 4 samples for every 2 Seconds

TypePerf "\PhysicalDisk(*)\Current Disk Queue Length" -sc 4 -si 2

Ok! How to show metrics for multiple Counters\Instance ?

Lets create a Counter file contains Counters\Instances whatever you want to collect the metrics

Counter file created : C:\Personal\MyCounter.txt

Refer the Counter file

To collect the metrics in SQL Server Tables!

Create a System DSN

here is the file you have to start with - C:\windows\system32\odbcad32.exe

My System DSN Name : MySQLServer

Change the default database to: TestingDB (The database already created in my SQL Instance)

And, here is the script!

The following THREE Tables created in TestingDB Database

dbo.CounterDetails

dbo.CounterData

dbo.DisplayToID

here is the performance metrics collected in SQL Server Table in TestingDB Database