You’re offline. This is a read only version of the page.
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
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:
Share
Report
All responses (
Answers (
-- 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,
