High CPU Usage on MS SQL

Hi,
Been using Metabase for a long time now and lately was seeing some issues on my MS SQL server which metabase connects to gather data.

THe metabase uses all the cpu all the time. After killing metabases serviice/ connections, my cpu usage drops to 1-2 %.

Any help regarding this issue ?

Hi @fitimosmani
Which version are you using?
Do you have x-ray enabled?
Are you using automatic sync/scans?

I’ve seen similar issues, the problem for me was some really badly written views in SQL Server, subqueries and outer applies all over the place etc. I think Metabase periodically does full scans of all the tables/views it can access, when it hit the bad views it was just grinding to a halt.

So in my case it was my own bad SQL views causing the issue. I’ve since moved to a data warehouse, improved my SQL and used use schemas to limit/control what Metabase can see.

I am using 0.29.3 and 0.30 (Same issue)

Yesterday i change the DATABASE SYNCING from hourly to daily. The last 24 hours, the issue has not occurred.

I have had a very simialr issue. I am running SQL Server on a EC2 instance. You can see the impact when I add a MS SQL single datasource and forget to turn on Daily Synching vs hourly, it destroy my server. Caught the oversight at 14:30 today. v32.10

@Slickrock22
How big is your database (GB)? And how many tables?
Have you checked the SQL server log for any help on what it’s struggling with?
You might be interested in this issue:
https://github.com/metabase/metabase/issues/10398
and https://github.com/metabase/metabase/issues/6209#issuecomment-355912936

DB is 600mb. Super small. Approx 30 tables. Most only have a few records. The issues were related bu not sure exactly what I am experiencing. Also I am on SQL Server 2016 SP2 as an FYI.

Also if you look at the mid point of the graph pre-turning DATABASE SYNCING to daily it is pegged consistently at 40% vs almost 0 when changed to daily.

@Slickrock22
Okay, that’s a very small database.
Do you see Metabase actively sync’ing constantly when setting it to hourly?
Check both Metabase and SQL Server.

I believe hourly is default correct? If so the 40% CPU graph is hourly. When set to daily it goes to near 0%

sorry to butt in.
Do you have any complex views? Metabase will also do a select * from all the views. You can either turn off syncing or set security to exclude some views and tables.

How would you set it to exclude some views?

Do you mean changing tables/views from queryable to hidden in settings?

no, I think it still scans those. I meant changing the database security so that the amount used can only see a few tables.

No complex views

I usually do
exec sp_msforeachtable "DENY SELECT ON ? TO metabase_user;"
And then
GRANT SELECT ON [dbo].[TABLE_OR_VIEW_NAME] to [metabase_user]
This way metabase user can’t access any table or view except what I grant access them.

2 Likes

Thank you. Good suggestion!