One issue that comes back every so often with Edgesight is one of performance. Little by little, an administrator might see the following behaviour:
- Report Rendering takes forever,
- Payloads are queuing in the webload directory and are imported very slowly,
- Servers will not update the Database,
- Nightly maintenance takes a very long time – and possibly eventually fails,
- Grooming error message are showing up all over the place in the console,
- and other similarly annoying stuff…
Those symptoms are usually caused by too much data residing in the SQL database. There is so much to process that every single job takes ages. Tasks that should be performed by the SQL engine build up until the system comes to a halt – if there’s no disk space left – or becomes desesperately sluggish and downright unusable.
This is where the administrator realizes that maybe the SQL server was not up to its mission. There might be too many servers reporting and/or the SQL server is under heavy load. The administrator might have chosen to expand the retention period, etc. However, the administrator is in a situation where reports need to be run and sent to management illico. He needs to find a way out of the predicament today so that he can send those reports to his management tomorrow morning. He’ll think about correcting the deployment design later.
Finding a way out of this situation in a short amout of time is possible. My method is to find out where the bulk of the data is and then drill down to identify which type of data we can manually remove, either by running a delete statement or truncate some table if their content is not needed.
1 – Find out which filegroups are the largest.
The default installation of Edgesight will create 7 Filegroups but the administrator may decide to customize the installation. Regardless of the installation management, let’s first find out the size of the filegroups:
SELECT DB_NAME(database_id) AS DatabaseName, Name AS Logical_Name, Physical_Name, (size*8)/1024 SizeMB
WHERE DB_NAME(database_id) = ‘Edgesight_DB_Name’
Depending on the size of the deployment, the result will vary. But what we will want to tackle are the largest filegroups. I have seen filegroups exceeding 100 GB.
2 – Find out the table to filegroup mapping:
from sys.sysindexes i,
where objectproperty(i.id,’IsUserTable’) = 1
and f.data_space_id = i.groupid
and f.data_space_id = d.data_space_id
and f.data_space_id = s.data_space_id
order by f.name,object_name(i.id),groupid
The output of the query will help tell you which tables belong to which filegroup. So let’s say if you find out in step 1 that FG2 is oversized, step 2 will tell you that the ALERT and ALERT_PARAM tables needs to be looked at. Similarly, if FG6 is oversized, the CORE_NET_TRANS and CORE_NET_STAT tables needs investigation.
You can also directly find out the “count per table” and investigate the largest tables directly:
SELECT sysobjects.Name, sysindexes.Rows
INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE type = ‘U’ AND sysindexes.IndId < 2
order by rows desc
3 – Find out about the grooming schedule configured by the admin You can find out about this in the Edgesight console but I believe it is more convenient to run the following query:
select f.table_name,f.view_name,m.default_days,m.groom_daysfrom maint_table_config minner join table_def f on f.tableid=m.tableidwhere m.default_days is not NULL
The output will also provide the default configuration so that you can see straight away what was changed and whether or not the large tables are affected by these changes.
4 – Now that you know about the largest Filegroups and the largest tables, you can start drilling down and find out what you can remove. This part is a little more complex because it depends on the content of the affected tables. So Let’s say, the count per table (Step 3 above) returns the following (First 20 tables only):
The table above tells us which tables we should try to reduce. First thing we need to know we whether records older that the cut-off date are still present (which means that the grooming was not successful). To find out, compare the oldest record in the table with the grooming schedule in step3.
Let’s take the USAGE table as an example, since it comes on top. Checking the grooming schedule for the usage table (from step3), we see that the retention period is 90 days:
select MIN(dtperiod) from usage
select COUNT(*) from usage
where dtperiod < ’2012-05-22 18:00:00′
declare @cnt int;declare @date datetime;set @date = ’2012-05-22 10:03:24.000′ifexists(select instid from usage where dtperiod < @date)set @cnt = 1else set @cnt = 0while @cnt > 0Begindelete top(100000) from usage where dtperiod < @dateifexists(select instid from usage where dtperiod < @date)set @cnt = 1else set @cnt = 0end;