Overall performance is very erratic

[TimH]

I know that description is a bit nebulous and vague but I am not sure how else to describe it. At times, everything seems to be working just fine but then a little while later when you attempt to search for an asset in the search bar, it just hangs. It seems very erratic/sporadic. The CPU and memory on the server are not being highly utilized at the times when Lansweeper is running slow or not responding, so it does not appear to be a hardware problem. I can run queries directly on the SQL database via SQL Server management studio with no performance issues. I see no significant entries in the server log or in Windows event viewer (although there are a fair amount of DCOM errors, but those are normal).

Any guidance/assistance is appreciated.

Thanks!

[Susan_A]

I'll create a ticket for our development team to take a look at the performance of the specified query. From what I understand, your specific issue was actually resolved though, by modifying the query? Should the issue persist, you can use SQL Profiler to identify slow-running queries.

[TimH]

Assets = 11904
SQL Server 2012 (11.0.5343.0)

[Michael_V]

How many assets do you have in your database?
Which database server are you using (sql server/sql compact)?

[TimH · Accepted solution]

I believe we found the problem. It appears that the web40repworkstationwithoutantivirus view has a 'LIKE' inside a JOIN which was taking forever to finish.

We changed this:

CREATE view [dbo].[web40repworkstationwithoutantivirus]
AS
SELECT TOP 1000000 tblassets.AssetID,
tblassets.AssetUnique,
tblassets.Domain,
tblassets.Description,
tblassets.Lastseen,
tsysos.Image As icon
FROM tblassets
INNER JOIN tblcomputersystem ON tblassets.AssetID =
tblcomputersystem.AssetID
INNER JOIN tblassetcustom ON tblassets.AssetID =
tblassetcustom.AssetID
INNER JOIN tsysos ON tblassets.OScode = tsysos.OScode
WHERE tblassets.AssetID NOT IN
(SELECT tblsoftware.AssetID
FROM tblsoftware INNER JOIN tblsoftwareuni On tblsoftwareuni.SoftID =
tblsoftware.softID INNER JOIN tsysantivirus
ON tblsoftwareuni.softwareName LIKE tsysantivirus.Software
) AND
tblassets.AssetID NOT IN (SELECT AssetID FROM tblAntivirus) AND
tblcomputersystem.Domainrole <=1 AND tblassetcustom.State = 1
GO

to this:

CREATE view [dbo].[web40repworkstationwithoutantivirus]
AS
SELECT TOP 1000000 tblassets.AssetID,
tblassets.AssetUnique,
tblassets.Domain,
tblassets.Description,
tblassets.Lastseen,
tsysos.Image As icon
FROM tblassets
INNER JOIN tblcomputersystem ON tblassets.AssetID =
tblcomputersystem.AssetID
INNER JOIN tblassetcustom ON tblassets.AssetID =
tblassetcustom.AssetID
INNER JOIN tsysos ON tblassets.OScode = tsysos.OScode
WHERE tblassets.AssetID NOT IN
(SELECT tblsoftware.AssetID
FROM tblsoftware INNER JOIN tblsoftwareuni On tblsoftwareuni.SoftID =
tblsoftware.softID INNER JOIN tsysantivirus
ON tblsoftwareuni.softwareName = tsysantivirus.Software
) AND
tblassets.AssetID NOT IN (SELECT AssetID FROM tblAntivirus) AND
tblcomputersystem.Domainrole <=1 AND tblassetcustom.State = 1
GO