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 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
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.
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.
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.