VMware View event database slowness


VMware View event database slowness

Today is my debugging day once again so after looking at VMware ESX and talking about indexing of Outlook E-mail, wondering why log files are not purged after ┬ábackup I’m back with VMware Horizon w. View.
The customer noticed that when he went to the VMware View administrator console and checked out the events, it would take at least 7 minutes to get a result…. 7 minutes that’s like, two coffee.
If you wonder what that looks like, here’s the picture.. a blank screen that shows nothing.
So first thing you do is look at the logging details of the connection servers, at least that was my first step. I made sure all of them are on informational only and we checked again. There was no instant improvement so let’s go to the SQL server.
This SQL server is setup for the View environment alone, so there is no other application bugging the server. the databases running there are the composer database, the event database and the update manager database. In normal circumstances this server is sleeping like a baby…
When however we request the events to be listed, the processor time goes up to 50%, effectively claiming one processor for the job.
The processor is this high during the time it needs to get the results, the query is also listed as most expensive query. It says it takes an average of 461894 milliseconds to complete but that’s crap for it takes at least 7 minutes to show results…
After the results are in the processor time drops instantly.
If you look at the query it certainly looks like a hell of a job to get results, wonder if that could be made easier. Just for the fun of it, here’s the query that is used to fill the event database. many outer joins and selects combined that are doing more harm then good in my opinion.
SELECT TOP 2000 EventID,Time,EventType,Severity,Module,Source,Acknowledged,Node,ModuleAndEventText,GroupId,UserSID,FolderPath,DesktopId,MachineId,UserDiskPathId,EndpointId,ThinAppId,LUNId,ApplicationId,FarmId,RDSServerId FROM (SELECT TOP 2000 VE_event.EventID,Time,EventType,Severity,Module,Source,Acknowledged,Node,ModuleAndEventText,GroupId,UserSID.StrValue AS UserSID,FolderPath.StrValue AS FolderPath,DesktopId.StrValue AS DesktopId,MachineId.StrValue AS MachineId,UserDiskPathId.StrValue AS UserDiskPathId,EndpointId.StrValue AS EndpointId,ThinAppId.StrValue AS ThinAppId,LUNId.StrValue AS LUNId,ApplicationId.StrValue AS ApplicationId,FarmId.StrValue AS FarmId,RDSServerId.StrValue AS RDSServerId FROM VE_event LEFT OUTER JOIN (SELECT EventID, StrValue FROM VE_event_data WHERE(Name = ‘UserSID’)) UserSID ON VE_event.EventID = UserSID.EventID LEFT OUTER JOIN (SELECT EventID, StrValue FROM VE_event_data WHERE(Name = ‘FolderPath’)) FolderPath ON VE_event.EventID = FolderPath.EventID LEFT OUTER JOIN (SELECT EventID, StrValue FROM VE_event_data WHERE(Name = ‘DesktopId’)) DesktopId ON VE_event.EventID = DesktopId.EventID LEFT OUTER JOIN (SELECT EventID, StrValue FROM VE_event_data WHERE(Name = ‘MachineId’)) MachineId ON VE_event.EventID = MachineId.EventID LEFT OUTER JOIN (SELECT EventID, StrValue FROM VE_event_data WHERE(Name = ‘UserDiskPathId’)) UserDiskPathId ON VE_event.EventID = UserDiskPathId.EventID LEFT OUTER JOIN (SELECT EventID, StrValue FROM VE_event_data WHERE(Name = ‘EndpointId’)) EndpointId ON VE_event.EventID = EndpointId.EventID LEFT OUTER JOIN (SELECT EventID, StrValue FROM VE_event_data WHERE(Name = ‘ThinAppId’)) ThinAppId ON VE_event.EventID = ThinAppId.EventID LEFT OUTER JOIN (SELECT EventID, StrValue FROM VE_event_data WHERE(Name = ‘LUNId’)) LUNId ON VE_event.EventID = LUNId.EventID LEFT OUTER JOIN (SELECT EventID, StrValue FROM VE_event_data WHERE(Name = ‘ApplicationId’)) ApplicationId ON VE_event.EventID = ApplicationId.EventID LEFT OUTER JOIN (SELECT EventID, StrValue FROM VE_event_data WHERE(Name = ‘FarmId’)) FarmId ON VE_event.EventID = FarmId.EventID LEFT OUTER JOIN (SELECT EventID, StrValue FROM VE_event_data WHERE(Name = ‘RDSServerId’)) RDSServerId ON VE_event.EventID = RDSServerId.EventID WHERE Time>=@P0 ORDER BY Time DESC, EventID DESC) AS v ORDER BY Time, EventID
What I do mostly when debugging a View environment is look in the event database directly for I need more than the default shown events and the web console isn’t that handy.
So doing a Select * from dbo.VE_Event on the SQL server itself shows instant result, I edited it with show Top 2000 and also that was faster than fast.
I worked for an Oracle consultancy company once in a long lost past and there I learned that indexing is key. So I was searching for a way to index the data table when I stumbled on a VMware article that had the solution.The Article states that if the event database is growing and growing the time to get the results is getting longer and CPU might be hammered. I created the index, which is finished in a second and closed my browser.

After opening the admin console again and asking for events I noted that the SQL server CPU didn’t spike at all. I returned to the console and the events are there.

So if you have issues with the event database or whatever database create an index.


Leave a Reply

https://tracking.cirrusinsight.com/869c29e2-3a9b-48c5-9232-0b95e7993ae8/controlup-com-pixel-php