2

I have two databases. One with 10.000 rows and one with 10 million rows.

I have a load test and I am running it towards a rest api endpoint. This endpoint does a query to database and average response time is 9 seconds. Same query on small DB runs on average 200ms. I am trying to understand why are the amount of rows causing this problem.

I used Azure Query Performance Insight to find the slowest query. This is all I have access at the moment.

The main thing is that database DTU is on 100% when this is running, but option is not to scale up the DB, but to tune what I have now and see what causes the issue.

I never did performance before, but I read on google that I should check execution plan. Since I am using load tests, does this execution plan means the app that does queries with DB(code) and then see the query itself?

I have a query, but I am clueless on how to continue to resolve this issue? segregate query per table (there are a lot of joins for this query - Entity Framework is used in code).

What can I actually do as only a developer, and no DBA experience from before with only Azure Insights?

Any good tutorials to learn this, or is this just DBA experience?

Table example:

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TABLE dbo.ContentItems
( 
    Id int IDENTITY(10000,1) NOT NULL
    , SiteId int NOT NULL
    , ContentType int NOT NULL
    , Title nvarchar(500) NOT NULL
    , Name nvarchar(100) NULL
    , UrlTitle nvarchar(100) NOT NULL
    , Promo nvarchar(max) NOT NULL
    , PromoImageUrl nvarchar(500) NULL
    , Body nvarchar(max) NOT NULL
    , BodyImageUrl nvarchar(500) NULL
    , More nvarchar(max) NULL
    , AdSentence nvarchar(max) NULL
    , CreatedDate datetime NOT NULL
    , UpdateDate datetime NULL
    , PublishDate datetime NOT NULL
    , AuthorId int NULL
    , AuthorDisplayName nvarchar(50) NULL
    , ContentCategoryId int NULL
    , IsActive bit NOT NULL
    , IsLive bit NOT NULL
    , IsFeatured bit NOT NULL
    , PostCode nvarchar(9) NULL
    , VideoEmbedCode nvarchar(max) NULL
    , MainImageUrl nvarchar(500) NULL
    , UserGui nvarchar(100) NULL
    , UserName nvarchar(100) NULL
    , RowVersion timestamp NOT NULL
    , TopicTagId int NULL
    , TurnOffAdverts bit NULL
    , IsLegacy bit NOT NULL
    , ReferenceId int NULL
    , ShowDashboardPromo bit NULL
    , CanonicalUrl nvarchar(max) NULL
    , IsIncludeProductPromo bit NOT NULL
    , IsVisible bit NOT NULL
    ,  CONSTRAINT PK_ContentItems 
        PRIMARY KEY CLUSTERED 
        ( Id ASC )
        WITH (PAD_INDEX = OFF
            , STATISTICS_NORECOMPUTE = OFF
            , IGNORE_DUP_KEY = OFF
            , ALLOW_ROW_LOCKS = ON
            , ALLOW_PAGE_LOCKS = ON
        )
)
GO

ALTER TABLE dbo.ContentItems 
ADD CONSTRAINT DF_ContentItems_CreatedDate 
DEFAULT (getdate()) FOR CreatedDate
GO

ALTER TABLE dbo.ContentItems 
ADD CONSTRAINT DF_ContentItems_PublishDate  
DEFAULT (getdate()) FOR PublishDate
GO

ALTER TABLE dbo.ContentItems 
ADD CONSTRAINT DF_ContentItems_IsActive  
DEFAULT ((1)) FOR IsActive 
GO

ALTER TABLE dbo.ContentItems 
ADD CONSTRAINT DF_ContentItems_IsLive 
DEFAULT ((0)) FOR IsLive 
GO

ALTER TABLE dbo.ContentItems 
ADD CONSTRAINT DF_ContentItems_IsFeatured  
DEFAULT ((0)) FOR IsFeatured 
GO

ALTER TABLE dbo.ContentItems 
ADD DEFAULT ((0)) FOR IsLegacy 
GO

ALTER TABLE dbo.ContentItems 
ADD CONSTRAINT DF_ContentItems_IsIncludeProductPromo  
DEFAULT ((0)) FOR IsIncludeProductPromo 
GO

ALTER TABLE dbo.ContentItems 
ADD CONSTRAINT DF_ContentItems_IsVisible_Default  
DEFAULT ((1)) FOR IsVisible 
GO

ALTER TABLE dbo.ContentItems  
WITH CHECK 
ADD CONSTRAINT FK_ContentItems_TopicTags 
FOREIGN KEY (TopicTagId) 
REFERENCES dbo.TopicTags (Id) 
GO

ALTER TABLE dbo.ContentItems 
CHECK CONSTRAINT FK_ContentItems_TopicTags 
GO
  • Yes this is SQL Server database. I added DDL – Amel Salibasic Jul 26 '17 at 16:36
  • Format your code by selecting it then pressing CTRL-K – Max Vernon Jul 26 '17 at 16:41
  • Thanks for including the DDL - are there really no indexes on this table? You may want to look at Brent Ozar's excellent topics on performance tuning at brentozar.com/sql/sql-server-performance-tuning – Max Vernon Jul 26 '17 at 16:57
  • 2
    Can you add an example of a problematic query? – Tom V Jul 26 '17 at 18:04
  • Query plans are xml/graphical representation of how the db carried out your query, after it's been processed by the optimizer. Removes a lot of guesswork. Basics plans in SSMS: youtube.com/watch?v=J69zLvoh2oI. Do you have perms to execute the query and connect via SSMS? – Xingzhou Liu Jul 27 '17 at 5:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.