Azure SQL Database DTU percentage Reach 100% on particular function

Question

My scenario is I have a button name(publish) at admin side use for publish agreement and after publish it visible to user-side. Recently I saw when I publish agreement DTU percent reach almost 100% and I tested rest of all functionality DTU not goes upto 5%.

My database size is 250mb I upgraded the DTU to a higher tier(from B1 to B2,S1,S2,S3) but when publish agreement it reach 100%

process took (3 minute maximum) when publish a large agreement

I checked the Query that taking long time also not complicated

(@EntityKeyValue1 bigint)SELECT 
[Extent1].[Revision] AS [Revision], 
[Extent1].[ClauseVersionId] AS [ClauseVersionId], 
[Extent1].[SourceClauseVersionId] AS [SourceClauseVersionId], 
[Extent1].[DisplayHeader] AS [DisplayHeader], 
[Extent1].[Body] AS [Body], 
[Extent1].[EndUserGuidanceNote] AS [EndUserGuidanceNote], 
[Extent1].[DocumentParty_DocumentPartyId] AS [DocumentParty_DocumentPartyId], 
[Extent1].[PublishedClause_ClauseId] AS [PublishedClause_ClauseId]
FROM [dbo].[PublishedClauseVersion] AS [Extent1]
WHERE ([Extent1].[PublishedClause_ClauseId] IS NOT NULL) AND ([Extent1].[PublishedClause_ClauseId] = @EntityKeyValue1)

I saw many answer and article but I didn't get any proper solution for this issue.

Is PublishedClauseVersion a view or a table? Are there any calculated fields in there? Even when this table would make up the entire 250mb and there would not be any indexing, it still shouldn't take 3 minutes to run through all of it. Are you sure it is the query that takes this long? (Looks like entity framework code, could it be your fetcher is doing other (complicated) stuff?) — deroby
Also, the usual tips: try an UPDATE STATISTICS on the table and have an index on PublishedClause_ClauseId. From some of the linked articles you might be 'I/O/Transaction Bound'... — deroby
@deroby PublishedClauseVersion is a table and 3 minutes is maximum time taken by code(when publish a big size agreement) I check this in network tab of browser. In azure I have applied Index and also run UPDATE STATISTICS on all tables.but still getting same Issue — Umang Patwa
if you have enabled peformance insight,can you paste output of that — TheGameiswar

Mamdouh Mamdouh · Accepted Answer · 2018-12-30T10:42:02

Check if the 2 sides of the comparison matches , Meaning the PublishedClause_ClauseId is also BigInt Data type as the parameter you are using "@EntityKeyValue1" , Mismatching them cause query optimizer to either scan or not use indexes, Match them then redeploy

Azure SQL Database DTU percentage Reach 100% on particular function

1 Answers