I've found since upgrading to SEC5 the console is very slow when clicking on a computer in the status view in order to view its details. The screen will come up blank and then just over 2mins later the details finally appear.
Anyone else have this? I'm not sure if this is a common problem as I had a quick search through the forum but couldn't find any posts on the issue.
Hello CDC,
this is not how it should be. How many clients, and does it happen with any computer any time? An excessive number of alerts and events could cause this, but this affects usually only viewing the particular client(s). Is this the only function showing delays - using Events to view the different categories is there also a delay? Might be worth to check the load on the database instance and the database size.
Christian
HI Christian,
Yes this happens on any computer that's clicked on. We currently have about 650 clients with a database size of 414mb. The rest of the console seems to run ok, looking at our database server it isn't under any stress under normal conditions or when opening up the PC details window. Maybe our database has become a bit bloated through the various upgrades?
Hi,
Do you have SQL Profiler installed on the database machine?
As the data displayed in the computer details window is the result of a number of queries, I would try and identify which component is taking the time. Monitoring the "Duration" of the various component queries in SQL Profiler will tell you which tables perhaps should be purged?
For example, when opening the details of a computer I see the following query of many taking place:
exec dbo.ErrorHistoryListGet '2012-01-20 21:28:31',N'1,',N'SAV,'
which is providing the computer details window with the SAV error history for machine with ID = 1. I.e. the machine's computer details I opened. What is the duration of this query for you? note, they are in milliseconds.
Other queries that are part of the query are:
exec dbo.ErrorAlertOutstandingIDListGet N'1,',N'SCF,',100
exec dbo.ErrorAlertOutstandingIDListGet N'1,',N'ALC,',100
exec dbo.ErrorAlertOutstandingIDListGet N'1,',N'SEA,',100
There are quite a few more but you will see them all in SQL Profiler.
The "Duration" column is the key here to pinning down where the delay is coming from.
Regards,
Jak
P.S. If you don't have SQL Profiler (You need full SQL Server rather than just SSMS), there is another way to get the information out of SQL and that is to turn on C2 Level Auditing in SQL comination with running "fn_trace_gettable" queries against the trace file: http://msdn.microsoft.com/en-us/library/ms188425.aspx . A bit clunky and will slow down the SQL server to some degree but it will work also, you can also use clauses against the duration column to narrow down results. Even if you have SQL Profiler you can save the Profiler trace and perform the same queries against the trace if that's easier than using the GUI of Profiler.
The other option and possibly simpler is free SQL Profiler like tools; you can find them by searchin on Google.
Hi Jak,
We're only running SQL express on our Sophos server but we have the full version on another server so used the profiler on that to monitor. The queries you listed seemed to run fairly quickly between 23-35ms. Scrolling through the logs looking for long duration times i found one query which took 62556ms which was:
exec dbo.ComputerDescriptionAndOSUpdate 1580,34,N' '
Is this query related to opening the computer description?
Not being an expert on SQL, what options would i have to compact or clear up the database if it is bloated?
Christian, it definatly has become a lot slower since the upgrade. It wasn't quick before, but you'd see the populated window between 5-6 secs after clicking.
Cheers
HI,
I think that long duration is a bogus reading by SQL Profiler; that stored procedure is called in response to a status message coming in from a client causing the 'computersanddeletedcomputers' table to be updated for a given machine with regards to upating the OS or description field. It wouldn't be related to what you're experiencing.
If you want to purge alerts you could use PurgeDB.exe (http://www.sophos.com/support/knowledgebase/article/109884.html ) but it would be nice to correlate the problem with "too much data" of one type or another to even know what to specifically purge.
The other option to help us to understand if it's related to the amount of date might be to do as follows:
1. Open SQL Server Management Studio and connect to the instance in use.
2. Right click on SOPHOS50 and choose "Reports" - "Standard Reports" - "Disk Usage by table"
Does that report help identify any tables with more records than you would expect? Maybe you could make that available here?
Otherwise, it might be worth creating a support ticket so they can maybe do a remote access to witness the behavior? If you have SQL Profiler and SQL Management Studio available they should be able to identify the problem quite quickly. If a remote access is not possible, you could backup your SOPHOS50 database with BackupDB.bat and send that in to them.
One more thing, http://www.sophos.com/support/knowledgebase/article/111353.html is also worth a quick read.
Regards,
Jak
This is the output of the report. There's a few with over 100,000 records which seems quite high but not having something to compare it against makes it hard to see whether that might be normal. Another question regarding databases is that after upgrades the old databases are left on the server (so there now 5 sophos databases ranging from version 3 - 5) Are these still needed?
| Table Name | # Records | Reserved (KB) | Data (KB) | Indexes (KB) | Unused (KB) |
| dbo.AggregatedCompliancePolicyTypes | 10 | 16 | 8 | 8 | 0 |
| dbo.CCLRuleCCLMapping | 10 | 32 | 8 | 24 | 0 |
| dbo.ComputerEventsSummary | 327 | 80 | 40 | 40 | 0 |
| dbo.ComputerGroupMapping | 1,173 | 240 | 88 | 120 | 32 |
| dbo.ComputerListViewSearchColumns | 62 | 32 | 8 | 24 | 0 |
| dbo.ComputerPolicyMapping | 10,990 | 1,104 | 664 | 400 | 40 |
| dbo.ComputerPolicyStates | 6,479 | 1,808 | 832 | 872 | 104 |
| dbo.ComputersAndDeletedComputers | 1,173 | 2,776 | 1,088 | 1,288 | 400 |
| dbo.ContentControlLists | 185 | 2,696 | 2,600 | 72 | 24 |
| dbo.ContentControlListTagMapping | 0 | 0 | 0 | 0 | 0 |
| dbo.DashboardConfiguration | 7 | 48 | 8 | 40 | 0 |
| dbo.DashboardConfigurationEvents | 4 | 32 | 8 | 24 | 0 |
| dbo.DashboardDefaultEventThresholds | 4 | 32 | 8 | 24 | 0 |
| dbo.DataPoints | 26,224 | 2,704 | 1,584 | 904 | 216 |
| dbo.DataPolicyNonCompliant | 176,813 | 12,176 | 6,192 | 5,840 | 144 |
| dbo.DataProtectedCount | 311,374 | 21,264 | 10,832 | 10,256 | 176 |
| dbo.Defaults | 20 | 32 | 8 | 24 | 0 |
| dbo.DeployedPackages | 0 | 0 | 0 | 0 | 0 |
| dbo.EmailAlertRecipients | 1 | 48 | 8 | 40 | 0 |
| dbo.EmailAlertsSMTPParameters | 1 | 32 | 8 | 24 | 0 |
| dbo.EMLibraryServers | 0 | 0 | 0 | 0 | 0 |
| dbo.Enumerations | 266 | 168 | 64 | 48 | 56 |
| dbo.EnumNames | 3 | 48 | 8 | 40 | 0 |
| dbo.EnumValueNames | 11 | 32 | 8 | 24 | 0 |
| dbo.ErrorAlertFilters | 8 | 32 | 8 | 24 | 0 |
| dbo.Errors | 166,210 | 33,816 | 18,952 | 14,728 | 136 |
| dbo.Events | 67,677 | 33,632 | 23,664 | 9,832 | 136 |
| dbo.Events_DataControlData | 0 | 0 | 0 | 0 | 0 |
| dbo.Events_DeviceControlData | 725 | 216 | 128 | 16 | 72 |
| dbo.Events_FirewallData | 66,952 | 30,536 | 30,400 | 120 | 16 |
| dbo.Events_TamperProtectionData | 0 | 0 | 0 | 0 | 0 |
| dbo.Events_Web | 0 | 0 | 0 | 0 | 0 |
| dbo.ExemptedDeviceDetailsMapping | 1 | 32 | 8 | 24 | 0 |
| dbo.ExemptedDevices | 2 | 32 | 8 | 24 | 0 |
| dbo.ExemptionDetails | 1 | 32 | 8 | 24 | 0 |
| dbo.GroupPolicyMapping | 2,250 | 336 | 128 | 104 | 104 |
| dbo.Groups | 151 | 112 | 24 | 88 | 0 |
| dbo.GroupTypeMapping | 0 | 0 | 0 | 0 | 0 |
| dbo.GroupTypes | 0 | 0 | 0 | 0 | 0 |
| dbo.IDELists | 331 | 3,128 | 2,568 | 56 | 504 |
| dbo.LatestData | 24 | 4,072 | 4,024 | 24 | 24 |
| dbo.NotificationReportingMapping | 10 | 32 | 8 | 24 | 0 |
| dbo.Packages | 1,953 | 984 | 392 | 480 | 112 |
| dbo.PatchStatus | 0 | 0 | 0 | 0 | 0 |
| dbo.Permissions | 19 | 32 | 8 | 24 | 0 |
| dbo.Policies | 214 | 2,688 | 2,144 | 208 | 336 |
| dbo.PolicyExemptionDetailMapping | 1 | 32 | 8 | 24 | 0 |
| dbo.PolicyRuleMapping | 0 | 0 | 0 | 0 | 0 |
| dbo.ReporterParameters | 1 | 16 | 8 | 8 | 0 |
| dbo.Reports | 10 | 72 | 24 | 48 | 0 |
| dbo.RolePermissions | 41 | 32 | 8 | 24 | 0 |
| dbo.Roles | 5 | 32 | 8 | 24 | 0 |
| dbo.RuleContentControlLists | 10 | 16 | 8 | 8 | 0 |
| dbo.RuleRuleCCLMapping | 10 | 32 | 8 | 24 | 0 |
| dbo.Rules | 14 | 80 | 32 | 48 | 0 |
| dbo.SDDMLocations | 12 | 16 | 8 | 8 | 0 |
| dbo.SDDMPackages | 3,013 | 400 | 128 | 128 | 144 |
| dbo.SDDMServers | 5 | 88 | 56 | 32 | 0 |
| dbo.SubEstateGroupMapping | 1 | 32 | 8 | 24 | 0 |
| dbo.SubEstates | 1 | 48 | 8 | 40 | 0 |
| dbo.SyncPointData | 30 | 48 | 8 | 40 | 0 |
| dbo.TagCategoryLocalisations | 0 | 0 | 0 | 0 | 0 |
| dbo.Tags | 0 | 0 | 0 | 0 | 0 |
| dbo.TagValueLocalisations | 0 | 0 | 0 | 0 | 0 |
| dbo.ThreatEvents | 2,681 | 800 | 280 | 384 | 136 |
| dbo.ThreatMasterList | 1,706 | 664 | 320 | 272 | 72 |
| dbo.Threats | 7 | 184 | 32 | 152 | 0 |
| dbo.ThreatsArchive | 324 | 432 | 176 | 240 | 16 |
| dbo.Upgrade | 1 | 16 | 8 | 8 | 0 |
| dbo.UserRoles | 6 | 32 | 8 | 24 | 0 |
| dbo.Users | 17 | 32 | 8 | 24 | 0 |
| dbo.UserSubEstates | 6 | 32 | 8 | 24 | 0 |
| dbo.WebControlStatus | 0 | 0 | 0 | 0 | 0 |
HI,
None of those row count numbers standout as being ridiculously high, certainly not high enough to cause the problem you report with sensible hardware.
You can drop the old databases, i.e.. SOPHOS3, SOPHOS4, SOPHOS45, SOPHOS47. All the data has been copied from the previous to the latest database as part of the upgrade. I always tend to back them up and then drop them once I know the latest has all the data and seems to be OK. http://www.sophos.com/support/knowledgebase/article/17508.html .
I'm afraid that without seeing the slow computer details issue, I'm not sure what else to suggest. You could check the average disk queue lengths performance counter in performance monitor where the mdf and ldf reside, do these spike when you bring up the computer details? Is it a disk resource issue?
Using SQL Server Management Studio, if you connect to the SQL instance, right click on the instance from the tree view you can start Activity Monitor. Are there any "Recent Expensive Queries" you can tie to this operation? Data I/O OK?
I think you're best bet is to raise a call with Support and reference this forum thread, I suspect they will want to do a remote access as I think this one needs to be seen first hand.
Jak
