A report runs for 5 minutes before terminating with a SQL timeout error

Products

• IT Management Suite

Issue/Introduction

When attempting to run a report for a specific solution, in this case an Application Metering report "Denial of Events by Application," the report would run for about 5 minutes and then terminate with the something similar to the following SQL timeout error:

Sql error in query. Error: System.Data.SqlClient.SqlError: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.Sql CommandText: -- MAX ROWCOUNT SET ROWCOUNT 50000 -- DECLARE @Days INT DECLARE @Computer NVARCHAR(64) SET @Days = 30 SET @Computer = '%' SELECT * FROM (SELECT DISTINCT s.[ProductName] AS 'Application', 'Number of Denials' = ( SELECT COUNT(DISTINCT i.[PID] + CONVERT(NVARCHAR(40), i.[Start Date], 21) + CAST(i.[_ResourceGuid] AS NVARCHAR(50))) FROM dbo.Evt_AeX_Application_Start i JOIN dbo.vComputer c ON i.[_ResourceGuid] = c.[Guid] WHERE i.[ProductName] = s.[ProductName] AND UPPER(c.[Name]) LIKE UPPER(@Computer) AND i.[Denied] = 1 AND DATEDIFF(dd, i.[Start Date], GETDATE()) <= @Days) FROM dbo.Evt_AeX_Application_Start s GROUP BY s.[ProductName]) t WHERE t.[Number of Denials] > 0 ORDER BY t.[Application]

Cause

• The key table being called by the report "Evt_AeX_Application_Start " had an excess of 13 million rows and is taking up 67 percent of the 10+ GB database.
• The database server only had SQL Server 2000 Standard Edition, which is limited in the amout of memory it can use.

Resolution

1. Verify the number of rows and space the table(s) used by the report occupy in the database. This can be done by checking the SQL of the report against the output created by the SQL script in article 21310, which can be run in the Query Analyzer against the Altiris database.
2. If the table is seemingly too large, then it may be too much for the SQL server to handle. Therefore, it is recommended that the resource history retention level for the data class being used in the report be dropped down lower than the default of 6 months and then purged.

For example:

1. In this case the report being used Denial of Events by Application, was primarily using the table "Evt_AeX_Application_Start". After running the SQL script in article 21310 against the Altiris database it revealed that the table 'Evt_AeX_Application_Start' had over 13 million rows and took up approximately 67% of the database.
2. Therefore, it would become necessary to crop down the amount of rows and data space for this table, especially since it is an 'Event Table'. This is done by going into the Altiris Console to Configuration > Server Settings > Notification Server Settings > Purging Maintenance.
3. Select the tab Resource Event Data Purge Settings. The challenge here is to find the data class the corresponds with the table that that needs to be reduce in size. Usually the data classes in the various sections will have a similar or identical name to the table in question. In this example, under the node Software Management\Application Metering Events, you will find AeX Application Start. To the right will be the retention period which, by default, is 6 months. Change it to 3 months and apply the changes.
4. Having applied the changes select on the Purging Maintenance tab and then click Purge Now to kick off the purge process. After the process has completed run the query from article 21310 again and see how significant the number of rows and the size has changed for the table in question. If it is significant then try to run the report that was timing out again, and see if it is successful or not.

Adjusting the resource retention to the best value is also a challenge, but usually, after a little tinkering, you will be able to find the sweet spot for the respective data class.

Applies To
Notification Server 6.0.6074 (SP3 R5)
Application Metering Solution 6.1.32
Application Metering Report Pack 6.1.1080