Skip to main content

Notifications

Announcements

No record found.

Customer experience | Sales, Customer Insights,...
Suggested answer

how to optimize the performance for sending email

(0) ShareShare
ReportReport
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

  • 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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,419 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,492 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans