Skip to main content

Notifications

Microsoft Dynamics CRM forum
Suggested answer

how to optimize the performance for sending email

Posted on by 5

Hi guys,

I got a sql script from database, as shown below, it seems like resend the email,but this script takes 15s every time, it affects the performance of database, I want to know how to optimize this functionality,

appreciate for your help!

(@StateCode0 int,@StatusCode0 int,@DirectionCode0 bit,@DeliveryAttempts0 int,@ParticipationTypeMask0 int,@PartyId0 uniqueidentifier,@PartyId1 uniqueidentifier,@PartyId2 uniqueidentifier,@PartyId3 uniqueidentifier)select
top 5 "email0".Subject as "subject"
, "email0".Description as "description"
, "email0".PriorityCode as "prioritycode"
, "email0".ActivityId as "activityid"
, "email0".ModifiedOn as "modifiedon"
, "email0".StateCode as "statecode"
, "email0".StatusCode as "statuscode"
, "email0".DeliveryAttempts as "deliveryattempts"
, "email0".AttachmentCount as "attachmentcount"
, convert(bigint, "email0".VersionNumber) as "versionnumber"
, N'' as "safedescription"
from
 Email as "email0" WITH (NOLOCK)  join ActivityParty as "activityparty1" WITH (NOLOCK)  on ("email0".ActivityId  =  "activityparty1".ActivityId and (((("activityparty1".ParticipationTypeMask = @ParticipationTypeMask0 and ("activityparty1".PartyId in (@PartyId0
, @PartyId1
, @PartyId2
, @PartyId3)))))))
where
 (("email0".StateCode = @StateCode0 and ("email0".StatusCode != @StatusCode0 or "email0".StatusCode is null) and "email0".DirectionCode = @DirectionCode0 and (((((("email0".DeliveryAttempts = @DeliveryAttempts0)))))))) order by
 "email0".ActualEnd asc

Thanks,

Sean

Categories:
  • Suggested answer
    RE: how to optimize the performance for sending email

    Hello Sean,

    Hope you are well.

    If you are using Onprem deployment, would say to check out the index fragmentation level for the database, using this script:

    -- Ensure a USE  statement has been executed first.
    USE AdventureWorks
    GO
     
    -- Look at the Index Information
    SELECT
                    DB_NAME(idxst.database_id) AS [database_name],
                    OBJECT_NAME(idxst.object_id, idxst.database_id) AS [object_name],
                    QUOTENAME(idxif.name) [index_name],
                    CASE 
                                    WHEN avg_fragmentation_in_percent < 10 THEN 'LOW'
                                    WHEN avg_fragmentation_in_percent < 30 THEN 'MEDIUM'
                                    WHEN avg_fragmentation_in_percent < 50 THEN 'HIGH'
                                    ELSE 'EXTREME'
                    END as fragmentation_indicator,
                    idxst.*
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , 'LIMITED') AS idxst -- not using DETAILED or SAMPLED
    INNER JOIN sys.indexes idxif  ON idxst.object_id = idxif.object_id AND idxst.index_id = idxif.index_id
    ORDER BY [object_name], [index_name]
    GO
    

    If fragmentation is HIGH or EXTREME for multiple indexes, would say to apply the following scripts:

    Rebuild/reorganize indexes:

    SET NOCOUNT ON;
    DECLARE @objectid int;
    DECLARE @indexid int;
    DECLARE @partitioncount bigint;
    DECLARE @schemaname nvarchar(130);
    DECLARE @objectname nvarchar(130);
    DECLARE @indexname nvarchar(130);
    DECLARE @partitionnum bigint;
    DECLARE @partitions bigint;
    DECLARE @frag float;
    DECLARE @command nvarchar(4000);
    SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
    INTO #work_to_do
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
    DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
    OPEN partitions;
    WHILE (1=1)
    BEGIN;
    FETCH NEXT
    FROM partitions
    INTO @objectid, @indexid, @partitionnum, @frag;
    IF @@FETCH_STATUS < 0 BREAK;
    SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
    FROM sys.objects AS o
    JOIN sys.schemas as s ON s.schema_id = o.schema_id
    WHERE o.object_id = @objectid;
    SELECT @indexname = QUOTENAME(name)
    FROM sys.indexes
    WHERE object_id = @objectid AND index_id = @indexid;
    SELECT @partitioncount = count (*)
    FROM sys.partitions
    WHERE object_id = @objectid AND index_id = @indexid;
    IF @frag < 30.0
    SET @command = N'ALTER INDEX '   @indexname   N' ON '   @schemaname   N'.'   @objectname
      N' REORGANIZE';
    IF @frag >= 30.0
    SET @command = N'ALTER INDEX '   @indexname   N' ON '   @schemaname   N'.'   @objectname
      N' REBUILD';
    IF @partitioncount > 1
    SET @command = @command   N' PARTITION='   CAST(@partitionnum AS nvarchar(10));
    EXEC (@command);
    PRINT N'Executed: '   @command;
    END;
    CLOSE partitions;
    DEALLOCATE partitions;
    DROP TABLE #work_to_do;
    GO
    

    Update statistics:

    exec sp_MSForEachTable "UPDATE STATISTICS ? with fullscan"

    Consider apply those scripts on recurrent jobs.

    Pay attention to run it outside the business hours.

    Regards,

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Dynamics 365 Community Update – Sep 16th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,522 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,441 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans