Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Last 50 events causing timeouts and DB blocking

So I had to open a case yesterday (Case #376623) because many of the views I have written are timing out. Through the process of elimination I was able to determine that it was the Last 50 Events component as the issue. As soon as I remove that from the views the pages load with very little delay.

I had my DBA take a look at the health of our DB with a tool they use and they found one query was causing considerable blocking on the database. The query in question is calling the events table (query included below for reference). He also was able to pull a report showing the wait time this particular query is causing on my system and the results were disturbing. The query shows wait times of close to 15 hours (per day). I'm no SQL expert so I'm not sure why a query that should simply be pulling a TOP 50 could be killing our performance.

I have submitted diagnostics to support and waiting for information. If they come back and tell me disk IO (which is a known problem on my system until we upgrade) I might lose it. Upgrading to NPM 10.3.1 took our average disk queue length down to 3 from over 7 which is better but still not ideal. We are hoping to move to our new SQL server in the next month.

(QUERY CAUSING THE BLOCKING)

(@maxRecords int,@fromDate datetime,@toDate datetime)

SELECT TOP (@maxRecords) NetObjectType,

NetObjectID,

NetObjectID2,

EventID,

Acknowledged,

EventTime,

Events.EventType as EventType,

Message,

ISNULL(EventTypes.BackColor, 0) as BackColor,

Nodes.NodeID as LinkNodeID

FROM Events

LEFT JOIN Nodes

ON Events.NetworkNode = Nodes.NodeID

LEFT JOIN EventTypes

ON Events.EventType = EventTypes.EventType

WHERE Acknowledged='false'

AND EventTime >= @fromDate

AND EventTime <= @toDate

ORDER BY EventID DESC

Find more posts tagged with

Accepted answers

All comments

mdriskell

So I've written what I believe to be a more efficient query to grab the last 50 events. I would just throw together a report but that doesn't give me the color coding so I'd have to figure out how to put this data into HTML.

SELECT NetObjectType,

NetObjectID,

NetObjectID2,

EventID,

Acknowledged,

EventTime,

Events.EventType as EventType,

Message,

ISNULL(EventTypes.BackColor, 0) as BackColor,

Nodes.NodeID as LinkNodeID

FROM Events

LEFT JOIN Nodes

ON Events.NetworkNode = Nodes.NodeID

LEFT JOIN EventTypes

ON Events.EventType = EventTypes.EventType

WHERE EventID > (SELECT MAX(EventId) - 50 FROM Events)

AND Acknowledged='false'

ORDER BY EventID DESC

mdriskell

Initial response from support.

Hello Mike;

Thank you for the information, could you please send me the Hardware configuration for you SQL and for all three SolarWinds servers. I will need the CPU configuration, Total RAM, HDD Configuration, and if the servers are Physical or Virtual.

Also let's plan a down time and remote session at the same,

I explained I will need more info as to what might be causing the issue before I can schedule down time. I can't down a prod system from monitoring our entire enterprise because one view is malfunctioning.

netlogix

This might not be fully solarwinds supported, but I was looking at the indexes on that table and the clustered index is, IMHO, on a silly column. I took clustered off "IX_NetworkNode" and added it to "IX_EventTime" which just makes way more sense. Then rebuilt the indexes on that table and it seam a little peppier to me.

mdriskell

Thanks Steve, I sent in my specs and I am awaiting their response. I'm well aware my SQL HD configuration is a bottleneck and am expecting that to be fully blamed however considering I can pull up the main events page which by default shows 2000 events in a couple of seconds but the last 50 events component completely times out I don't see how you can blame hardware. Something else is going on with that component.

netlogix

I didn't follow your response about the SQL HD stuff, was that directed towards me?

I had also noticed that the "last 50" events are slow for my enviroment and I think it is because the clustered index is based on seemingly random incoming data (node ID - who can predict the order of nodes generating events). This will cause the table to grow larger than it should and cause slow indexes because it is always needing to re-organize the table (a clustered index says what order you want the data to be stored in the database). By switching to IX_EventTime as the clustered index, it keeps the database from massively fragment itself by just inserting new rows.

I don't think it can be completely blamed on your hardware. (oh, there are other tables in the SW databases that have indexes that don't make sense to me)

mdriskell

No that was directed toward support . I know we have bottlenecks and I know that's part of the problem but to not be able to return 25 events when I can pull 2000 on another page is not a hardware issue.

mdriskell

I emailed support this morning because it has been two days since I have heard anything. I need some sort of answer back.