Solarwinds SDK script unmanage nodes

Solarwinds offers a Powershell SDK to manipulate nodes programmatically. The Solarwinds SDK can be downloaded here.

I have created a demonstration script will unmanage a node for 2 hours so that maintenance can be performed on the node.

Setup connection to Solarwinds application server.

$secpasswd = ConvertTo-SecureString "password" -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential("LAB\username01", $secpasswd)

Search for the node named ‘SQL01.lab.net’ and set the node as unmanaged for 2 hours. This is very useful if you have planned maintenance which happens once per week and want to programmatically unmanage a node each week.

$swis = Connect-Swis -Hostname swserver.lab.net -Credential $mycreds
$uris = Get-SwisData $swis "SELECT Uri FROM Orion.Nodes where Caption='SQL01.lab.net'"
$uris | ForEach-Object { Set-SwisObject $swis $_ @{Status=9;Unmanaged=$true;UnmanageFrom=[DateTime]::UtcNow;UnmanageUntil=[DateTime]::UtcNow.AddHours(2)} }

SolarWinds SAM Appinsight for SQL SERVER causing deadlocks

I recently found a bug in the way Solar winds monitor’s our databases. I am running Solarwinds Server application Monitor 11.1 and SQL SERVER Application Insight. Ever since I turned on the Application Insight feature I started seeing deadlock’s occurring in my SQL SERVER environment. Upon further investigation I noticed the Solarwinds Application Insight for SQL SERVER monitoring application was doing a read committed operation on system tables in all my database.

I sent the email below to Solar winds support and suggested the Solar winds developer change the query to a read uncommitted. Several days later I heard back from the Solar winds application engineer stating that they had a script for me to run. I ran the script which updated the monitoring query to be read uncommitted. I saw an immediate drop in the number of deadlocks alerts coming from my SQL SERVER. The moral of the story is to test monitoring thoroughly before using it in production. As in this case the monitoring server was at times causing transactions to be terminated unexpectedly.


—–Original Message—–
From: myname@mycompany.com
Sent: Monday , January 12, 2015 12:31 pm GMT (GMT+00:00)
Subject: appinsight for sql is causing deadlocks

The issue I am seeing is that solarwinds is locking system tables when its
app insight sql monitor runs. Here is one example where app insight
deadlocked on tempdb.sys.sysidxstats. Might I suggest that solarwinds
change the software so it does a “dirty read” or in other words read
uncommitted. This would prevent the deadlocks from happening.

Here is a excerpt from the deadlock XML file. The SAM App Insight monitor is running in isolation level read committed.

<process id="process7481048" taskpriority="0" logused="0" waitresource="KEY: 2:281474980249600 (45001d8e01fc)" waittime="1219" ownerI
d="825842724" transactionname="SELECT" lasttranstarted="2015-01-11T13:36:28.200" XDES="0x80039960" lockMode="S" schedulerid="6" kpid="2680" status="suspended" spid="143" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2015-01-11T13:36:28.133
" lastbatchcompleted="2015-01-11T13:36:28.090" clientapp=".Net SqlClient Data Provider" hostname="SW01" hostpid="16352" loginname="DOMAIN\sa-monitor" isolationlevel="read committed (2)" xactid="825842724" currentdb="2" lockTimeout="4294967295" cliento
ption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="26" sqlhandle="0x02000000b680b70233f6dc8a15fa42960c50200f89756a25">
SELECT TOP 10
	t.NAME AS TableName,
	t.object_id,
	mainIndex.data
_space_id,
	p.rows AS RowCounts,
	SUM(a.total_pages) * 8 AS TotalSpaceKB,
	SUM(CASE i.[type]
	WHEN 2 THEN 8 * a.used_pages
	ELSE 0
	END) AS IndexSpaceKB
FROM [sys].[tables] t
INNER JOIN sys.indexes mainIndex ON (t.object_id = mainIndex.object_id AND mainIn
dex.[type] IN (0,1))
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN [sys].[partitions] p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN [sys].[allocation_units] a ON p.partition_id = a.container_id
WHERE t.NAME NOT LI
KE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID &gt; 255 GROUP BY t.Name, t.object_id, mainIndex.data_space_id, p.Rows
ORDER BY TotalSpaceKB DESC     </frame>
     <frame procname="adhoc" line="21" stmtstart="1748" stmtend="1786" sqlhandle="0x0200000
0db583517807077e95c2490bb681fca4f9e78724f">
EXECUTE (@sqlQuery)     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>


<keylock hobtid="281474980249600" dbid="2" objectname="tempdb.sys.sysidxstats" indexname="clst" id="lock30e2f1d00" mode="X" associatedObjectId="
281474980249600">
    <owner-list>
     <owner id="process39d30c088" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process7481048" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>

Solarwinds Support provided the following SQL to change the ISOLATION LEVEL to READ UNCOMMITTED. Since included the new SQL in Solarwinds I have not seen any deadlocks.

-- FB395905 AppInsight for SQL is causing SQL Deadlocks
-- specifying isolation level for polling
UPDATE cts SET Value = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @TimeStamp datetime
SET @TimeStamp = GETUTCDATE()
SELECT
	name as dbName,
	d.database_id,
	[compatibility_level],
	collation_name,
	[state],
	recovery_model,
	DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), bk.last_backup) as last_backup,
	avg_read_latency,
	avg_write_latency,
	avg_bytes_per_read,
	avg_bytes_per_write,
	num_of_reads,
	num_of_writes,
	@TimeStamp as TimeStamp
FROM sys.databases d
LEFT JOIN
(
SELECT bs.database_name, MAX(bs.backup_finish_date) last_backup
FROM msdb.dbo.backupset bs
WHERE server_name = SERVERPROPERTY(''ServerName'') GROUP BY bs.database_name
) AS bk ON d.name = bk.database_name
LEFT JOIN
(
SELECT  database_id,
	SUM(io_stall_read_ms / NULLIF(num_of_reads, 0)) AS avg_read_latency,
	SUM(io_stall_write_ms / NULLIF(num_of_writes, 0)) AS avg_write_latency,
	SUM(num_of_bytes_read / NULLIF(num_of_reads, 0)) AS avg_bytes_per_read,
	SUM(num_of_bytes_written / NULLIF(num_of_writes, 0)) AS avg_bytes_per_write,
	SUM(num_of_reads) AS num_of_reads,
	SUM(num_of_writes) AS num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
GROUP BY database_id
)AS [io] ON d.database_id = [io].database_id
WHERE name NOT IN (${UnmanagedDatabases})'
FROM APM_ComponentTemplateSetting cts
INNER JOIN APM_ComponentTemplate ct ON cts.ComponentTemplateID = ct.ID
WHERE ct.UniqueId = '15868507-6248-4DE6-9A83-561535EBC058' AND cts.[Key] = 'SqlQuery'

-- Enforce job rescheduling for ABSA applications
UPDATE a SET LastModified = GETUTCDATE()
FROM APM_Application a INNER JOIN APM_ApplicationTemplate at ON a.TemplateID = at.ID
WHERE at.CustomApplicationType = 'ABSA'