I'm having trouble in finding where did this SQL query come from. It's executed only in production on Azure and in huge amounts (about 30-60k per hour) regardless of traffic. I managed to get it down a bit (from 1.4kk per day to 0.4-0.8kk) by rebuilding indexes, but dont' know why it helped.
Query:
(@0 nvarchar(40),@1 int)SELECT COUNT(*)
FROM [cmsDocument]
INNER JOIN [cmsContentVersion]
ON [cmsDocument].[versionId] = [cmsContentVersion].[VersionId]
INNER JOIN [cmsContent]
ON [cmsContentVersion].[ContentId] = [cmsContent].[nodeId]
INNER JOIN [umbracoNode]
ON [cmsContent].[nodeId] = [umbracoNode].[id]
INNER JOIN [cmsContentType]
ON [cmsContentType].[nodeId] = [cmsContent].[contentType]
WHERE (([umbracoNode].[nodeObjectType] = @0))
AND (([umbracoNode].[parentID] = @1))
I want to remove this completely as I'm having I/O issues on SQL and this can be part of the problem.
This does look very unusual but initially some questions come to mind:
How much visitor traffic is there on the site per day ?
How much admin content management traffic is there on the site per day ?
Do you have any third party packages installed / connected to your site ?
If 1 = yes and 2 = no and 3 = no, then potentially your "front end" code is the issue - if you are not querying the Umbraco cache to load content and are loading content from the database, then this might be the cause of your problem.
This is purely uneducated feedback so might not be applicable but hope it helps track down the issue.
Umbraco v7.14 with use of Umraco REST Api. We are not displaying any content, just exposing endpoints for FE app.
Azure SQL is in S2 tier.
Traffic (which is basically just requests for some data and few pages) is something about 15-20k per hour.
Not much admin management traffic. Most of the days no management at all and if it's there, then it's just creating one page (with 2 RTE fields) + uploading few images.
Yes, there are 2 packages installed by admin, but they are not used - Analytics and Cultiv Search Engine Sitemap. I tested locally and doesn't seem to affect anything or create any queries.
I double checked azure configuration and looks good (based on the link). Maybe there is something wrong with this, but configuration didn't changed at all when those queries started to appear in SQL.
I checked code for all of the requests but couldn't reproduce this query locally (with production DB backup) in any way.
Bad news is that number of this query started to grow up again and rebuilding indexes don't work anymore, so maybe it was just coincidence that it helped before.
And additional information - restarting App Service, changing tier of SQL is not helping at all.
where did this SQL query come from
Hi,
I'm having trouble in finding where did this SQL query come from. It's executed only in production on Azure and in huge amounts (about 30-60k per hour) regardless of traffic. I managed to get it down a bit (from 1.4kk per day to 0.4-0.8kk) by rebuilding indexes, but dont' know why it helped.
Query:
I want to remove this completely as I'm having I/O issues on SQL and this can be part of the problem.
Hi Thomas
This does look very unusual but initially some questions come to mind:
If 1 = yes and 2 = no and 3 = no, then potentially your "front end" code is the issue - if you are not querying the Umbraco cache to load content and are loading content from the database, then this might be the cause of your problem.
This is purely uneducated feedback so might not be applicable but hope it helps track down the issue.
Cheers
Nigel
Hi Thomas,
Are you running V7 or V8 ?
You say you get it down when you rebuild the examine indexes. So it is always a good idea if you configured your application correctly for running on azure : https://our.umbraco.com/documentation/Getting-Started/Setup/Server-Setup/azure-web-apps
Also I would check your code for usages of ContentService, MediaService during rendering.
Dave
Hi,
Thanks for answers!
Umbraco v7.14 with use of Umraco REST Api. We are not displaying any content, just exposing endpoints for FE app. Azure SQL is in S2 tier.
I double checked azure configuration and looks good (based on the link). Maybe there is something wrong with this, but configuration didn't changed at all when those queries started to appear in SQL.
I checked code for all of the requests but couldn't reproduce this query locally (with production DB backup) in any way.
Bad news is that number of this query started to grow up again and rebuilding indexes don't work anymore, so maybe it was just coincidence that it helped before.
And additional information - restarting App Service, changing tier of SQL is not helping at all.
I am facing the same issue. Anyone got this resolved?
is working on a reply...