V�kon je pomal�, pokud tabulka AsyncOperationBase stane p��li� velk� v aplikaci Microsoft Dynamics CRM 4.0

• Kolekce dat SQM. -Program doch�zet m���tka kvality softwaru kter� shroma��uje data pro z�kazn�ka.
• �loha aktualizace smlouvy st�ty SQL. Tato �loha spou�t� jednou za den na p�lnoci. Tato �loha nastav� stav neplatn� platnost� smluv.
• Napln� organizace �pln� text katalogu Index - index �pln� text v db hled�n� �l�nky kb v CRM.

• Skript SQL v Tento �l�nek je pouze jednor�zov� �sil�. M��ete p�idat toto jako SQL �lohu spustit na no�n�ho, t�denn� nebo m�s��n� z�klad periodick�ho z�klad. Jako va�e CRM spust� , nutn� bu� pou��t tento �l�nek na z�klad� t�denn� v z�vislosti na v� podnik pot�ebuje nebo pou��t �e�en� podle writting vlastn� �lohy HROMADN� DELETE (naleznete na�e CRM SDK dokumentaci na vlastnost BulkDeleteRequest.QuerySet, BulkDeleteRequest t��dy a odstran�n� objedn�vky).
• Ujist�te se, �e odstran�n� z�znamy AsyncOperation pro pracovn� postupy a odpov�daj�c� z�znamy z objektu WorkflowLogBase.
• Ujist�te se, �e budou odstran�ny v�echny odpov�daj�c� z�znamy bulkdeletefailure.
• P�esv�d�te se, zda pouze n�sleduj�c� typy operace Async jsou odstran�na, pokud je k�d stavu typy 3 a k�d stavu typy je 30 nebo 32:
  • Roz�i�uj�c� �kol pracovn�ho postupu (1)
  • Shroma��ov�n� dat SQM (9)
  • PersistMatchCode (12)
  • FullTextCatalogIndex (25)
  • UpdateContractStates (27)
  • Pracovn� postup (10)

IF EXISTS (SELECT name from sys.indexes
                  WHERE name = N'CRM_AsyncOperation_CleanupCompleted')
      DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
GO
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
GO

declare @DeleteRowCount int
Select @DeleteRowCount = 2000
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
declare @continue int, @rowCount int
select @continue = 1
while (@continue = 1)
begin
      begin tran
      insert into @DeletedAsyncRowsTable(AsyncOperationId)
      Select top (@DeleteRowCount) AsyncOperationId
      from AsyncOperationBase
      where OperationType in (1, 9, 12, 25, 27, 10) AND StateCode = 3 AND StatusCode in (30, 32)     
 
      Select @rowCount = 0
      Select @rowCount = count(*) from @DeletedAsyncRowsTable
      select @continue = case when @rowCount <= 0 then 0 else 1 end     
 
        if (@continue = 1)
        begin
            delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
            where W.AsyncOperationId = d.AsyncOperationId
            
delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
            where B.AsyncOperationId = d.AsyncOperationId
 
            delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
            where A.AsyncOperationId = d.AsyncOperationId            
 
            delete @DeletedAsyncRowsTable
      end
 
      commit
end

--Drop the Index on AsyncOperationBase

DROP INDEX AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

Zlep�en� v�konu odstran�n� skriptu

• Zlep�it celkov� v�kon Microsoft CRM napl�novat odstran�n� CRM slu�by spustit �pi�ku pro aplikaci Microsoft CRM. Ve v�choz�m nastaven� je slu�ba spu�t�na v okam�iku nainstalov�na aplikace Microsoft CRM. M��ete v�ak spustit slu�by 10: 00 HODIN nam�sto na v�choz� �as spu�t�n�. To provedete pomoc� editoru �lohy CRM ScaleGroup. Dal�� informace naleznete na n�sleduj�c�m webu MSDN:
  http://code.msdn.microsoft.com/ScaleGroupJobEditor/Release/ProjectReleases.aspx?ReleaseId=676

  (http://code.msdn.microsoft.com/ScaleGroupJobEditor/Release/ProjectReleases.aspx?ReleaseId=676)
  Pozn�mka: Tato akce neovlivn� v�kon skriptu p��mo.
• Zlep�en� v�konu odstran�n� skripty v tomto �l�nku a zlep�it Microsoft CRM DeletionService k�d spu�t�n� podobn� odstran� p�idat n�sleduj�c� indexy threeOrganizationName_MSCRM datab�ze p�ed spu�t�n�m skriptu odstran�n� v tomto �l�nku:

  CREATE NONCLUSTERED INDEX CRM_WorkflowLog_AsyncOperationID ON [dbo].[WorkflowLogBase] ([AsyncOperationID])
  GO 
  
  CREATE NONCLUSTERED INDEX CRM_DuplicateRecord_AsyncOperationID ON [dbo].[DuplicateRecordBase] ([AsyncOperationID])
  GO
  
  CREATE NONCLUSTERED INDEX CRM_BulkDeleteOperation_AsyncOperationID ON [dbo].[BulkDeleteOperationBase]
  (AsyncOperationID)
  GO
  

  Pozn�mka Pokud p�id�te tyto indexy skriptu odstran�n� m��e trvat hodiny spustit.
• Zastavit CRM asynchronn� zpracov�n� slu�by p�i spu�t�n� tohoto skriptu.
• OPTIONAL: n�sleduj�c� indexy znovu vytvo�it a aktualizovat statistiky:

  -- Rebuild Indexes & Update Statistics on AsyncOperationBase Table 
  ALTER INDEX ALL ON AsyncOperationBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)
  GO 
  -- Rebuild Indexes & Update Statistics on WorkflowLogBase Table 
  ALTER INDEX ALL ON WorkflowLogBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)
  
  GO

• OPTIONAL: Statistika aktualizovat pomoc� �pln� skenov�n� na v�echny tabulky zapojen�ch pomoc� dotazu n�e p��kazy (nejl�pe na vypnuto �pi�ky):

  UPDATE STATISTICS [dbo].[AsyncOperationBase] WITH FULLSCAN
  UPDATE STATISTICS [dbo].[DuplicateRecordBase] WITH FULLSCAN
  UPDATE STATISTICS [dbo].[BulkDeleteOperationBase] WITH FULLSCAN
  UPDATE STATISTICS [dbo].[WorkflowCompletedScopeBase] WITH FULLSCAN
  UPDATE STATISTICS [dbo].[WorkflowLogBase] WITH FULLSCAN
  UPDATE STATISTICS [dbo].[WorkflowWaitSubscriptionBase] WITH FULLSCAN

• OPTIONAL : zm�nit model zotaven� datab�ze MSCRM Simple vyhnout nadbyte�n�ch generov�n� protokol� serveru SQL. SQL server 2005 p�ihl�en� Management Studio Microsoft SQL Server jako spr�vce, klepn�te prav�m tla��tkem my�i na datab�zi _MSCRM <org_name>, klepn�te na tla��tkoVlastnosti, klepn�te na p��kaz Mo�nosti a potom klepn�te na Model obnoven�. Ozna�it Simple a potom klepn�te na tla��tko OK. Po spu�t�n� tohoto skriptu po��te�n� �as, model zotaven� datab�ze _MSCRM <org_name> P�epnut� zp�t do FULL nejlep�� datov� model obnovitelnost.

Select Count(AsyncOperationId)
from AsyncOperationBase WITH (NOLOCK)
where OperationType in (1, 9, 12, 25, 27, 10) 
AND StateCode = 3 AND StatusCode IN (30,32) 

Zp�t nahoru | Dejte n�m zp�tnou vazbu