The sqlmonitor from imajaydwivedi

Dashboard - Deadlock Analysis

Below query can be used for this infra -

declare @_endDate datetime;
set @_startDate = dateadd(hour,-24,getdate());
set @_endDate = GETDATE()

exec sp_BlitzLock @StartDate = @_startDate, @EndDate = @_endDate
				,@OutputDatabaseName = 'tempdb'
go

select *
from tempdb..BlitzLock

Create Alert for Severity 19 to 25 along with any specific Error Number

Create infra to create SQLAlerts based on error number & category entries on a specific table.

As a response to alert, a SQLAgent job should execute, and record the error details.

Add Preventive Triggers on `dbo.instance_details` to avoid Mistakes

Add Preventive Triggers on dbo.instance_details to avoid Mistakes

We are getting error on two step 3XEventSession and 9CopyDbaToolsModule2Host

**2024Feb16_1240 INFO: ***Working on step '3__XEventSession'..
2024Feb16_1240 INFO: $XEventSessionFilePath = 'D:\GitHub\SQLMonitor\DDLs\SCH-Create-XEvents.sql'
2024Feb16_1240 INFO: Computed XEvent files directory -> 'D:\MSSQL15.MSSQLSERVER\XEvents'..
2024Feb16_1240 INFO: Create XEvent session named [xevent_metrics]..

The target, "5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file", encountered a configuration error during initialization.
Object cannot be added to the event session. The operating system returned error 5: 'Access is denied.
' while creating the file 'C:\Program Files\Microsoft SQL
Server\MSSQL15.SQL2019\MSSQL\xevents\xevent_metrics_0_133525896392450000.xel'.
At line:97864 char:9

```
    throw $records[0]
```
```
    ~~~~~~~~~~~~~~~~~
```
- CategoryInfo : NotSpecified: (SQLMASTER\SQL2019:String) [], Exception
- FullyQualifiedErrorId : dbatools_Invoke-DbaQuery

2024Feb16_1243 INFO: *****Working on step '9__CopyDbaToolsModule2Host'..
2024Feb16_1243 INFO: $DbaToolsFolderPath = 'D:\Github\dbatools'
2024Feb16_1243 INFO: $dbaToolsLibraryFolderPath = 'D:\Github\dbatools.library'
Import-LocalizedData : Cannot find the Windows PowerShell data file 'dbatools.psd1' in directory
'D:\GitHub\SQLMonitor\SQLMonitor\en-US', or in any parent culture directories.
At D:\GitHub\SQLMonitor\SQLMonitor\Install-SQLMonitor.ps1:1937 char:5

Import-LocalizedData -BaseDirectory $dbatoolsConfigFileDirectory  ...

```
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
```
- CategoryInfo : ObjectNotFound: (D:\GitHub\SQLMo...S\dbatools.psd1:String) [Import-LocalizedData], PSInvalidOperationE
  xception
- FullyQualifiedErrorId : ImportLocalizedData,Microsoft.PowerShell.Commands.ImportLocalizedData

Infra to Track Server and Database Configuration Changes

Need to capture any server-level changes like max memory, ad hoc distributed queries, clr enabled, database mail, remote access, xp_cmdshell.

Some reference blogs -

Add Infra to Track `AG State Change`

Add Infra to Track AG State Change

SQL CPU Not Showing in SQLCluster Case

Describe the bug
SQLCluster CPU is not showing on dashboard when the SQLCluster is NOT present on preferred node.

To Reproduce
Assume 2 active-active SQLClusters, say SqlClus01 & SqlClus02 configured on 2 hosts SqlHost01 & SqlHost02. If both the SqlClus01 & SqlClus02 are both moved to SqlHost01, then SqlHost02 does not show SQL CPU on dashboards.

Track Status of SQLAgent Service

After reboot of OS/Box, sometimes, SQLAgent service does not start automatically.

Add feature to track SQLAgent services & display on All Server Dashboard.

Dashboard for AlwaysOn Latency

Create a dashboard for AlwaysOn Latency.

This dashboard should be able to expose latency across time.

Show Counts of Issues on All Server Dashboard

Show counts of issues in each panel on All Server Dashboard similar to below -

Basic Slack Alerts for Core Metrics

Create following alerts using Python & Slack -

We are getting the below error how to fixed this

2024Feb16_1042 ERROR: Seems either [DBA] does not exists, or the data/log files are present in C:\ drive.
Kindly rectify this issue.
F:\GitHub\SQLMonitor\SQLMonitor\Install-SQLMonitor.ps1 : Stop here. Fix above issue.
At F:\Github\SQLMonitor\Private\Wrapper-InstallSQLMonitor.ps1:75 char:1

F:\GitHub\SQLMonitor\SQLMonitor\Install-SQLMonitor.ps1 @params

Setup Alert Engine Using Python+Postgres

Design Email & Slack Alerts for following -

Audit To Trace DDL Changes

Setup Alert Engine Using Python+SQLServer

Design Email & Slack Alerts for following -

Self-Sign PowerShell Scripts

In highly secured environment, only remote-signed powershell scripts are allowed.

Self-signing SQLMonitor powershell scripts will help in smooth deployments.

Add Metric `Avg ms per disk wait` on dashboard

Add Metric Avg ms per disk wait on dashboard

This wait type helps to identify if there is disk latency on server.

Data Distribution of Table Partition

Is your feature request related to a problem? Please describe.
There are times when I need to check data distribution of a partitioned table in order to figure of if the table partitioning is working fine or not.

Query to use - https://github.com/imajaydwivedi/SQLMonitor/blob/dev/Sql-Queries/QRY-Get-Partitions-Record-Count-Metrics.sql

Add Parameters to Skip Particular Wait Type in `usp_waits_per_core_per_minute`

Is your feature request related to a problem? Please describe.
On some of my servers, there are some WaitTypes that always occurring, and overshadowing other relevant wait types.

Describe the solution you'd like
Below 2 options -

A parameter to ignore comma separate list of wait types
A table that keeps a list of wait types that can be ignored for easy handling. This table should be present on each server.

Dashboard exposing Statistics Information

Delta update stats query can be used for this infra - https://ajaydwivedi.com/query/indexoptimize

Also, expose result of DBCC SHOW_STATISTICS

Add Collection Time on All Server Dashboard

Is your feature request related to a problem? Please describe.
It's very important to know when the data collection happened while analyzing the data.

So need to add collection_time on All Server Dashboard panels.

Change Retention of `dbo.all_server_volatile_info_history` to 15 Days

There are scenarios where we need to check historical metrics to figure out max/min/average/pencentile etc for a metrics within x days.

For example, get max memory utilization for last 7 days.

Need to change Retention of dbo.all_server_volatile_info_history to 15 Days in table dbo.purge_table.

Infra to Support Password Rotation for `grafana` Login

Due to restrict oranizational security policies, login password should change very 90 days.

Due to this, password for grafana login should be rotated every 90 days.

Need to build the infra to support password rotation for grafana login

Add Flag for choice of `MemoryOptimized Tables`

Add Flag for choice of MemoryOptimized Tables

Dashboard Exposing ErrorMessages from ErrorLogs

Create a dashboard Exposing Error Messages from ErrorLogs of all the servers.

Idea is to proactively identify issues by reading error logs, and generate alerts if possible.

Daily Mailer for `All Server Health` Dashboard

Folks are requesting for a daily mail representing the same data as on the dashboard Monitoring - Live - All Servers.

Add Latency Trend Over Time on `t__Database File IO Stats`

Add Latency Trend Over Time on t__Database File IO Stats

Migrate `grafana` Login from Inventory on All Servers

Is your feature request related to a problem? Please describe.
Deployment of SQLMonitor on AG replicas causes grafana login with different SIDs.
Thus monitoring stops working post failover.

Describe the solution you'd like
Create grafana login by scripting out from Inventory Server. This would ensure consistency of login SID.

Enable `-TrustServerCertificate -EncryptConnection` flag in Invoke-DbaQuery

Is your feature request related to a problem? Please describe.
With latest SMO, connections fails with weird messages.

So need to enable -TrustServerCertificate -EncryptConnection flag in Invoke-DbaQuery

Exception Tables for All Panels on All Server Dashboard

Is your feature request related to a problem? Please describe.

There are times when we can not do anything to one issue appearing on dashboard.
For example, if a database has been set to offline or readonly, then backups won't happen.
So having an exception table would help to skip these records showing on dashboard.

Control Immediate Removal of Perfmon File in Job `(dba) CollectPerfmonData`

Is your feature request related to a problem? Please describe.
I was to analyze the generated perfmon blg files on my server. So don't want to remove them immediately.

Describe the solution you'd like
A parameter in script SQLMonitor\perfmon-collector-push-to-sqlserver.ps1 to decide if immediately remove a file or not.

Dashboard Exposing Latest Backup History

Is your feature request related to a problem? Please describe.
Sometimes backups are not happening, and we find out very late.

Having a dashboard will help in timely detection of miss backup issues.

Added exception for Offline/ReadOnly/Standby databases
Ignore database snapshots
Added exception table on Inventory server for handling Restore State or any other maintenance activity
Implement logic based on Job Category for backup jobs on AG backups. Goal is to figure out which replica is source of backup jobs

2023Nov22_1832 INFO:      Fetch disk space on $HostName = [21L-LTPABL-1187]..
2023Nov22_1832 INFO:      Push disk info to SqlServer [21L-LTPABL-1187].[DBA].[dbo].[disk_space]..
The certificate chain was issued by an authority that is not trusted
At line:97904 char:9
+         throw $records[0]
+         ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ConnectionError: (21L-LTPABL-1187:String) [], Exception
    + FullyQualifiedErrorId : dbatools_Write-DbaDbTableData

imajaydwivedi / sqlmonitor Goto Github PK

sqlmonitor's Introduction

sqlmonitor's People

Contributors

Stargazers

Watchers

Forkers

sqlmonitor's Issues

Recommend Projects

Recommend Topics

Recommend Org