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
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,
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,522 Super User 2024 Season 2
Martin Dráb 228,441 Most Valuable Professional
nmaenpaa 101,148