SQL Azure performance issues with the latest version. Maybe related to "Update fields_map to read from DB schema rather than file"?

[mikoskinen]

After updating our Project Nami site to the latest version, our SQL Azure CPU usage spiked heavily:

We checked the analytics provided by SQL Azure and the culprit seems to be this query:

select tab.name as table_name, col.name as column_name,
                        CASE WHEN exists (select * from sys.indexes pk inner join sys.index_columns ic on ic.object_id = pk.object_id and ic.index_id = pk.index_id where pk.object_id = tab.object_id and ic.column_id = col.column_id and pk.is_primary_key = 1) then 'primary_id' else t.name end as data_type 
                        from sys.tables as tab 
                        inner join sys.columns as col on tab.object_id = col.object_id 
                        left join sys.types as t on col.user_type_id = t.user_type_id 
                        order by table_name, column_name

Here's a list which shows the execution time and execution count for the query:

The query seems to be heavy and it seems to be executed quite often. Is this normal or did we fail our update? :)

[patrickebates]

This query was added a couple of versions back to replace the fields_map file that was causing issues with some updates. I've not seen this reaction on any other site before.

What is the tier of this SQL Azure instance?

[mikoskinen]

This was on basic tier, so the cheapest one can get. We upgraded the instance to standard and gave it more calculation units and it is now running OK.

Still though the query I pasted above is the one "hammering" the database the most. This is from the past 6 hours, during which the site only had few page views:

Where the first row is the query mentioned above. Here's more detailed view:

During that same 6h period the second most CPU intensive query was:

SELECT option_name, option_value FROM wp_2_options WHERE autoload = 'yes'

As shown in the image, that was executed 1245 times and it took in total 1.89s. In comparison, the query to sys.tables was executed 171 times and took 16.8s

[patrickebates]

This is interesting. I'm checking other sites, and while this query is in the Top 3 or Top 5 (as it would execute with almost every page load), it's behind the options autoload in execution time and CPU load in the majority of cases.

No idea why it would be such a hit on resources, especially on the Basic tier.

[mikoskinen]

An update on this a month later. We've been running the S2 tier since I originally opened the issue. The query to sys.tables in still leading the pack, this is from the past 7 days:

Here's the last 6 hours:

Is the query result something that could be cached?

[softwarecell]

We have just updated and seeing this same issue.

[mikoskinen]

Any news related to this? :) Still hoping that we could use a lower tier of SQL Azure as the current S1 (with 20 DTUs) is not enough.

[sarn1]

I might be late to the game, but with Project Nami 2.7.0 it spiked out my Azure SQL DB DTUs. This came out of nowhere for me (or someone activated Yoast SEO that I have set as inactive). It would not stop consuming 100% until I deactivated the Yoast SEO plugin. Re-activated it and it spiked up again. So I was able to isolate it to this plugin.

So the big pointer I can give out is to deactivate all plugins and see if that clears the DTUs and then re-activate each plugin back.

[sergiocabral]

Any update for this issue? thx. About my company, we have these plugins below. We don`t have the Yost SEO plugin but the same problems occurs.

• Elementor
• GP Premium
• GTranslate
• Rename wp-login.php
• Search & Replace
• Simple Lightbox
• WordPress Importer
• WP File Manager
• WPForms Lite

after I will investigate this problem in my scenario with more attention.

[sergiocabral]

Hi again... I solve the problem with these lines to cache the query result. Then the execute occurs each 1 minute. (but I will use 2 minutes as interval) This file is /wp-includes/fields_map.php into the function read. After this the average use of eDTU down from 20% to 0.3

[sergiocabral]

Hi again and again. The code above is not the guilty. Im my case the plugin wpforms-lite is the root cause of many calls of the Fields_map->read(). I discovered using the query-monitor plugin (https://br.wordpress.org/plugins/query-monitor/). With the inserted code above:

---

EDIT1: An example of use of query-monitor. It can show the origin plugin owner of the fields_map call

[sergiocabral]

My final solution was install and enable the RedisCache. Awesome reduce eDTU.

Maybe bots around the world hit attack to my website with login, comments, etc. and this increase activity in my site and growing up the eDTU.

Or, as anyone wrote, a plugin make many calls to Fields_map->read(). In my case was the wpforms-lite... to other was the Yoast SEO. But the plugin query-monitor with use of do_action('qm/start', 'log message here') and do_action('qm/stop', 'log message here') into the Fields_maps->read() (as above comments) can reveal what the villain plugin.