Skip to content
logo
platformaerp.com

Optimization Guide

Optimization Steps

1. Clean up search.searchhistory (safe on production)

SQL
1
2
3
4
5
6
-- create copy
select * into archive.searchhistory_20240213 from search.searchhistory
--check
select count(*) from search.searchhistory
--truncate table
truncate table search.searchhistory

2. Clean up deleted documentlines (safe on production)

SQL
1
2
3
4
-- warning headers should stay duo to consistency and information that somethng was deleted

select count(*) from document.documentline where status =-1 and adddate < getdate()-1
delete from document.documentline where status =-1 and adddate < getdate()-1

2a. Usunięcie cen dostawców jelsi starsze nei 5 lat bezpieczne, pomoze przy wyborze pozycji zamowien

SQL
1
2
delete from wms.[itemSupplier] where itemsuppliercode is null and itemsuppliername is null
and lastPurchasePriceDate < DATEADD(YEAR, -5, GETDATE())

3. Clean up hangfire log ( safe on production)

SQL
1
2
3
4
5
6
7
8
9
TRUNCATE TABLE [HangFire].[AggregatedCounter]
TRUNCATE TABLE [HangFire].[Counter]
TRUNCATE TABLE [HangFire].[JobParameter]
TRUNCATE TABLE [HangFire].[JobQueue]
TRUNCATE TABLE [HangFire].[List]
TRUNCATE TABLE [HangFire].[State]
DELETE FROM [HangFire].[Job]
DBCC CHECKIDENT ('[HangFire].[Job]', reseed, 0)
UPDATE [HangFire].[Hash] SET Value = 1 WHERE Field = 'LastJobId'

4. Reset search index_id (impact minor , safe on production)

5. Warning Clean up log.wall

Leave only one year

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
--check
select count(*) from log.wall where adddate < (getdate()-360) and
(newstatus is  null and previousstatus is null)

--first copy
select *  into archive.log_wall_20240222  --tudaj data
from log.wall where newstatus is  null and previousstatus is null
and adddate < (getdate()-360)

--delete
delete from log.wall where wallid in (select wallid from  log.[wall_20200522] )

8. (optional) CLenup LOG sessionlog (historia sesji)

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
truncate table sessionLog

--log narzedzie importujacego pliki
truncate table simpleLog

-- log dzialan
select count(*) from  system_logging
delete  from    system_logging where log_date < getdate()- 180
select count(*) from  logs
delete from Logs where TimeStamp < getdate()- 180

check last long operations

SQL
1
select top 100 * from [CRM0000_CONFIG].system_logging where log_duration >5000  order by log_date desc

check log for speed test

SQL
1
2
3
4
5
6
7
8
9
SELECT
      log_duration/1000 [CZAS WYKONANIA (S)],
      log_date DATA,
      log_message
      FROM [CRM0000_CONFIG].[dbo].[system_logging]
  where log_duration >2000
  and log_date > DATEADD(HOUR,-4,getdate())
  and log_logger ='controller'
  order by log_date desc

6. check for missing indexes

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
select * from  sys.dm_db_missing_index_details  where database_id = DB_ID()

SELECT TOP 100
 a.avg_user_impact,a.avg_total_user_cost,a.user_seeks,
    'CREATE NONCLUSTERED INDEX IX1_' + object_name(c.object_id) + left(cast(newid() as varchar(500)),5) + char(10)
    + ' on [' +  object_schema_name(c.object_id) +'].[' +  object_name(c.object_id)
    + ']('
    + case when c.equality_columns is not null and c.inequality_columns is not null then c.equality_columns + ',' + c.inequality_columns
    when c.equality_columns is not null and c.inequality_columns is null then c.equality_columns
    when c.inequality_columns is not null then c.inequality_columns
    end
    + ')' + char(10)
    + case when c.included_columns is not null then 'Include (' + c.included_columns + ')'
    else ''
    end as includes
FROM
    sys.dm_db_missing_index_group_stats a
    inner join sys.dm_db_missing_index_groups b
    on a.group_handle = b.index_group_handle
    inner join sys.dm_db_missing_index_details c
    on c.index_handle = b.index_handle
where
        db_name(database_id) = db_name()
     and equality_columns is not null
    --and object_name(c.object_id) like '%wall%'
ORDER BY
    a.avg_total_user_cost * a.avg_user_impact * (a.user_seeks + a.user_scans) DESC

cleanup location task

SQL
1
2
3
4
5
6
7
8
9
--check
select count(*) from wms.locationtask where adddate < getdate() -180

-- backup
select  * into custom.wms_locationtask_20221122 --tudaj data
from wms.locationtask  where  adddate < getdate() -180  -- order by adddate

--delete
delete from wms.locationtask  where  adddate < getdate() -180

Optional

1. Cleaunp stockbalancedate

SQL
1
2
3
4
5
6
7
8
9
select count(*)
  FROM [wms].[stockBalanceDate]
  where isnull(quantityin,0)=0 and isnull(quantityout,0)=0
  and isnull(valuein,0)=0 and isnull(valueout,0)=0

delete
  FROM [wms].[stockBalanceDate]
  where isnull(quantityin,0)=0 and isnull(quantityout,0)=0
  and isnull(valuein,0)=0 and isnull(valueout,0)=0

2. Cleanup stockbalance (uwaga tego nei robimy uzywamy tych stanow do s)

Usuniecie zapamietania lokacji jesli nie bylo ruch na lokacji przez ostatnie 180 dni

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
  /*If nothing happened during last 180 days on location and there was no stock*/

  select count(*)
   FROM  [wms].[stockBalance] s
   where isnull(s.quantity,0)=0 and isnull(s.quantity,0)=0
  and isnull(s.value,0)=0 and isnull(s.value,0)=0
    and s.moddate < getdate()- 180

-- backup
  select * into [wms].[stockBalance_archive] --archive
  FROM  [wms].[stockBalance] s
   where isnull(s.quantity,0)=0 and isnull(s.quantity,0)=0
  and isnull(s.value,0)=0 and isnull(s.value,0)=0
    and s.moddate < getdate()- 180

-- delete
  delete
  FROM  [wms].[stockBalance]
   where isnull(quantity,0)=0 and isnull(quantity,0)=0
  and isnull(value,0)=0 and isnull(value,0)=0
    and moddate < getdate()- 180

3. Check anomally

Appendix

Speed compare

http://geekswithblogs.net/DevJef/archive/2011/09/28/quick-performance-test-in-sql-server.aspx

Check table sizes for anomaly

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    4 desc

Checks documentlines daily

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
select convert(varchar(7),adddate,120) 'commision'  , count(*) from wms.commision
where adddate > getdate()-100 group by  convert(varchar(7),adddate,120) order by 1 desc

select  convert(varchar(7),adddate,120) 'commisionline', count(*) from wms.commisionline
where adddate > getdate()-100  group by convert(varchar(7),adddate,120) order by 1 desc

select  convert(varchar(7),adddate,120) 'document' , count(*) from document.document
where adddate > getdate()-100   group by  convert(varchar(7),adddate,120) order by 1 desc

select  convert(varchar(7),adddate,120) 'documentline', count(*) from document.documentline
where adddate > getdate()-100   group by  convert(varchar(7),adddate,120) order by 1 desc

select  convert(varchar(7),adddate,120) 'invoice', count(*) from document.invoice
where adddate > getdate()-100   group by  convert(varchar(7),adddate,120) order by 1 desc

select  convert(varchar(7),adddate,120) 'invoiceline', count(*) from document.invoiceline
where adddate > getdate()-100   group by  convert(varchar(7),adddate,120) order by 1 desc

select  convert(varchar(7),adddate,120) 'order', count(*) from document.[order]
where adddate > getdate()-100   group by  convert(varchar(7),adddate,120) order by 1 desc

select  convert(varchar(7),adddate,120) 'orderline', count(*) from document.orderline
where adddate > getdate()-100   group by  convert(varchar(7),adddate,120) order by 1 desc