Here’s how to perform this comprehensive monitoring:

TL;DR

1. Enable SQL Server Agent: Activate the SQL Server Agent for interaction with external collectors such as Prometheus.
2. Install Prometheus Windows Exporter: Download and install the Prometheus Windows Exporter from GitHub.
3. Set Up Prometheus Scraper/Database: Install Prometheus on your monitoring server/computer and configure the scraper.
4. Access Prometheus Server and Add Targets: Access the Prometheus server through a browser and add a new target.
5. Query SQL Server Processes: Execute the necessary query to query SQL Server processes.
6. Install Grafana and Connect to Prometheus: Install Grafana, connect it to the Prometheus server, and visualize the data.

• 

• Change the startup type of the SQL Server Agent service to “Automatic” so that it starts automatically upon system startup.

• Download  the Prometheus Windows Exporter from GitHub.
• Use the installer found in the “Assets” section to install the exporter.
• You can use the default Windows node exporter provided by Prometheus, which includes basic SQL Server metrics. No additional exporter installation is needed.
• Install the exporter using the MSI installer.
• After installation, the binary will be located in theC:\Program Files\windows_exporterfolder.
• 
• If a process/task named “windows_exporter” is running, terminate it, as we will need to manually start the exporter with some additional flags.
• Manually run the exporter with the following command to fetch SQL Server statistics:

C:\Program Files\windows_exporter\windows_exporter.exe --collectors. Enabled="cpu,cs,logical_disk,net,os,service,system,textfile,mssql"

• Access http://localhost:9182/metrics in your browser to view available metrics.
• To make the changes persistent, add the following settings to the registry:

"C:\Program Files\windows_exporter\windows_exporter.exe" --collectors.enabled="cpu,cs,logical_disk,net,os,service,system,textfile,mssql" --log.format logger:eventlog?name=windows_exporter

Search for “regedit” in the Start menu.

Open regedit.

HKEY_LOCAL_MACHONE/SYSTEM/Current ControlSet/Services/windows_exporter

in the opened window and make the registry changes we provided above to the ‘ImagePath’.

3-  Install Prometheus and Configure the Scraper:

• Download the latest version of Prometheus from prometheus.io. ,
• Extract the downloaded .tgz file to an appropriate folder.
• Run the binary file named prometheus.exelocated in the extracted folder.
• There will be a file named prometheus.yamlin the directory where Prometheus is installed.
• Open this file with a text editor. In thestatic_configssection, add the IP address of the Windows server you want to add as a target.

Example:

 - job_name: "MSSQL_SERVER"

    # metrics_path defaults to '/metrics'
    # scheme defaults to 'http'.

    static_configs:
      - targets: ["MSSQLSERVERIP:9182"]

• Replace “MSSQLSERVERIP” with the actual IP address of your Windows server.
• Save and close the file.

4.Access the Prometheus Server and Add Targets:

• Open a browser and go to localhost:9090

• Tarayıcınızı açın ve Prometheus sunucusuna gidin. Varsayılan olarak localhost:9090 .Open your browser and go to the Prometheus server. By default, it’s localhost:9090
• Click on the “Console” tab in the upper left corner. This is where you can run PromQL queries.
• Query SQL Server processes by entering the following query:

rate(windows_mssql_sqlstats_batch_requests[30s]) * 60

6.Install Grafana and Connect to Prometheus:

• Install Grafana and access the interface at localhost:3000.
• Use the “Import” option to add the MSSQL Windows Exporter Dashboard theme. Enter ‘15024’ as the ID in the “Import via grafana.com” section and proceed with “Load.”

• In the “Data Sources” section, add the “Prometheus” data source and “Import” it.
• 

• Now, you can visually examine SQL Server statistics in the added dashboard.