CRM 4.0 bulk delete performance degradation

After investigating the progressive performance degradation during a bulkdelete I think I found the problem.

It's the intence use of table variables and Ad Hoc queries (see dbo.fn_CollectForCascadeDelete CRM function) fills the cache with unuseful data.


To free the cache without restarting the server you can use the following command:
DBCC FREESYSTEMCACHE ('ALL').

Beware this command causes a temporary SQL Server general perfomance degradation, so you should use it to solve this specific problem avoiding it during user activities.

In other words you'd better run it during the bulk delete batch activity.

In a real case deletion starts with 3600 records/second and ends with 12 records/second.

During the bulk delete, after empting the cache, the speed go back to 3600 then degradation starts again.

A solution could be periodically empty the cache only if tempdb contains the bulk delete table variable.

Here is the script to search for the specific CRM variable and run the cache cleancing only when we found it and if it was created at least two minutes ago.

select

 t.[object_id]

from

 tempdb.sys.tables t with(nolock)

  inner join

 (

  select

   [object_id]

  from

   tempdb.sys.columns with(nolock)

  where

   [name] in ('o','t'/*,'r'*/,'p')

  group by

   [object_id]

  having

   count([object_id]) = 3

 ) c on

  c.[object_id] = t.[object_id]

where

 datediff(mi, t.create_date, getdate()) >= 2

if @@rowcount > 0

 DBCC FREESYSTEMCACHE ('ALL')

We could for example include this command in a SQL Job scheduled during the bulk delete every 3 minutes.

Another slowing down cause could be missing foreign key indexes (ex. AsyncOperationId column, WorkFlowLogBase table).

UPDATE: optimize for Ad Hoc Queries

1. SQL2005

USE [master]
GO
ALTER DATABASE [CRM_DB_NAME] SET PARAMETERIZATION FORCED WITH NO_WAIT
GO

2. SQL2008+

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO