Hello Thwackers,
We are having some performance issues on our NPM installation at the moment, and it seems to be coming down to NTA related queries. this SQL query seems to be really causing some pain, but the next few down the list of "top CPU hoggers" are also NTA related.
I have posted in the NPM forum due to the overall affect being that NPM runs slow, or inability to drill into node details when it really plays naughty
I believe there is fundamentally something bad going on in NTA that is causing the rest of the platform to act up, does the below query mean anything to you?
cheers
Spongey toilet-pants
.
--EXEC [dbo].[swsp_NodesGetAllNodes] 1
CREATE PROCEDURE [dbo].[swsp_NodesGetAllNodes] ( @ntaManagedOnly bit)
AS
BEGIN
SET NOCOUNT ON;
IF (@ntaManagedOnly = 1)
BEGIN
SELECT DISTINCT n.NodeID AS NodeID,
n.UnManaged AS UnManaged,
(CASE WHEN Len(Isnull(n.DNS,''))=0 THEN n.Caption ELSE n.DNS END) as DNS,
n.IP_Address AS IP_Address,
convert(int, COALESCE(np.FlowVersion,0)) as FlowVersion,
convert(int, COALESCE(np.FlowType,0)) AS FlowType,
CAST(case when n.ObjectSubType='ICMP' then 1 else 0 end AS BIT) as IsICMP,
CAST(1 AS BIT) AS IsPrimaryIpAddress,
n.[Description] AS [Description],
n.IOSImage AS IOSImage,
n.IOSVersion AS IOSVersion,
np.IsAARCapable AS IsAARCapable,
np.IsAARMainDevice AS IsAARMainDevice
FROM Nodes n WITH(NOLOCK)
JOIN Interfaces i WITH(NOLOCK) ON i.NodeID = n.NodeID
JOIN NetFlowSources ns WITH(NOLOCK) ON ns.InterfaceID = i.InterfaceID AND Enabled=1
LEFT JOIN NetFlowNodeProperties np WITH(NOLOCK) ON np.NodeID=n.NodeID
WHERE n.UnManaged = 0
END
ELSE
BEGIN
SELECT n.NodeID AS NodeID,
n.UnManaged AS UnManaged,
(CASE WHEN Len(Isnull(n.DNS,''))=0 THEN Caption ELSE n.DNS END) as DNS,
n.IP_Address AS IP_Address,
convert(int, COALESCE(np.FlowVersion,0)) as FlowVersion,
convert(int, COALESCE(np.FlowType,0)) AS FlowType,
CAST(case when n.ObjectSubType='ICMP' then 1 else 0 end AS BIT) as IsICMP,
CAST(1 AS BIT) AS IsPrimaryIpAddress,
n.[Description] AS [Description],
n.IOSImage AS IOSImage,
n.IOSVersion AS IOSVersion,
np.IsAARCapable AS IsAARCapable,
np.IsAARMainDevice AS IsAARMainDevice
FROM Nodes n WITH(NOLOCK)
LEFT JOIN NetFlowNodeProperties np WITH(NOLOCK) ON np.NodeID=n.NodeID
END
END
!