spaghettidba

-- Sync lag

;WITH 
	AG_Stats AS 
			(
			SELECT AR.replica_server_name,
				   HARS.role_desc, 
				   Db_name(DRS.database_id) [DBName], 

spaghettidba

SET DEADLOCK_PRIORITY -10;
IF SERVERPROPERTY('EngineEdition') NOT IN (2,3,4) BEGIN /*NOT IN Standard,Enterpris,Express*/
	DECLARE @ErrorMessage AS nvarchar(500) = 'Telegraf - Connection string Server:'+ @@ServerName + ',Database:' + DB_NAME() +' is not a SQL Server Standard,Enterprise or Express. Check the database_type parameter in the telegraf configuration.';
	RAISERROR (@ErrorMessage,11,1)
	RETURN
END

DECLARE

spaghettidba

SET DEADLOCK_PRIORITY -10;
IF SERVERPROPERTY('EngineEdition') NOT IN (2,3,4) BEGIN /*NOT IN Standard,Enterpris,Express*/
	DECLARE @ErrorMessage AS nvarchar(500) = 'Telegraf - Connection string Server:'+ @@ServerName + ',Database:' + DB_NAME() +' is not a SQL Server Standard,Enterprise or Express. Check the database_type parameter in the telegraf configuration.';
	RAISERROR (@ErrorMessage,11,1)
	RETURN
END

DECLARE
	 @SqlStatement AS nvarchar(max)
	,@MajorMinorVersion AS int = CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar),4) AS int)*100 + CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar),3) AS int)

spaghettidba

DECLARE @ProductVersion varchar(15);
                            DECLARE @Edition varchar(15);
                            DECLARE @EngineEdition smallint;
                            DECLARE @HostPlatform varchar(15);

                            SELECT @ProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS varchar(15)),
                                @Edition = CAST(SERVERPROPERTY('Edition') AS varchar(15)),
                                @EngineEdition = CAST(SERVERPROPERTY('EngineEdition') AS smallint)

                            DECLARE @Version int

spaghettidba

I see a few issues with your code that could be slowing it down. Let's try to go through them together:

1. SELECT * - Unless you really need all the columns, SELECT * is not a good practice: it prevents the optimizer from using indexes because looking up all the columns usually exceeds the cost of scanning the whole table
2. nolock - It is not a "go faster" switch: it instructs the engine to ignore locks posed by other sessions and not lock rows while reading them. This could end up reading rows more than once or skipping them altogether, besides reading rows not yet committed by other sessions. Make sure you understand what are the effects on your query.
3. p.col5 in ( -- list of 20 strings ) or p.col5 in ( -- list of 3000 strings ) can be collapsed in a single p.col5 in ( -- list of 3020 strings )
4. You should probably save your strings in a temporary table or table parameter, because passing too many literals on a query can exhaust the resources of the query optimizer (it usually times out tryi

spaghettidba

wget "https://github.com/$([regex]::match((wget https://github.com/zyedidia/micro/releases/latest).Content,'\/zyedidia\/micro\/releases\/download\/.+win64\.zip').Groups[0].Value)" -OutFile "$($env:temp)\micro.zip"; Expand-Archive -Path "$($env:temp)\micro.zip" -DestinationPath (gl)

spaghettidba

[CmdletBinding()]
    param(
        [Parameter(Mandatory=$true, Position=1)]
        [string[]]$SourceServer,
        [Parameter(Mandatory=$true, Position=2)]
        [string]$DestinationServer,
        [Parameter(Mandatory=$false, Position=3)]
        [string]$DestinationDatabase = "diagnostic",
        [Parameter(Mandatory=$false, Position=4)]
        [string]$DestinationSchema = "dbo"

spaghettidba

#
# RUN THIS CODE IN Powershell x86 !!!!!
#

$dllPath = "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Microsoft.SqlServer.ConnectionInfoExtended.dll"
$typeName = "Microsoft.SqlServer.Management.Trace.Tracefile"

try {
    $InputFile = "c:\temp\test.trc"
    $baseAssembly = [Reflection.Assembly]::LoadFrom($dllPath)

spaghettidba

#
# RUN THIS CODE IN Powershell x86 !!!!!
#

$dllPath = "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\PFCLNT.DLL"
$typeName = "Microsoft.SqlServer.Management.Trace.CTraceObjectsFileController"

try {
    #
    # this is the code found in Microsoft.SqlServer.Management.Trace.TraceFile.InitializeAsReader (thanks DotPeek...)

spaghettidba

$servers = @()
$regservers = Invoke-sqlcmd -ServerInstance GSVSQL33\SQL21 -Query "SELECT server_name FROM msdb.dbo.sysmanagement_shared_registered_servers_internal" 
$regservers | 
ForEach-Object {
    $servnameparts = $_.server_name.Split(@("\"),[System.StringSplitOptions]::None)
    $serverName = $servnameparts[0]
    if(Test-Connection -ComputerName $serverName -Quiet -Count 1){
        try {
            Invoke-Sqlcmd -ServerInstance $_.server_name -Query "SELECT SERVERPROPERTY('computernamephysicalnetbios') AS servername" | 
            ForEach-Object {