Excessive tempdb and RAM use by Metabase in MSSQL? [SOLVED]


#1

Morning all,

I’m sorry to bother, but I recently started using Metabase with my MSSQL 2016 server. My database is some 12 GB, without Metabase running it doesn’t use more than 13 GB of RAM, and Tempdb stays under 1GB. When I turn on my metabase machine, RAM usage goes to 95% of the 19 GB I have for the sql machine, and Tempdb starts to climb - Over 36 hours its gone from under 1gb to occupying the whole 100gb partition its on.

Would anyone have any idea what is causing this? I’ve tried to set all the options to the least expensive possible, but it still seems to happen.

Thanks in advance for any ideas!!


#2

Sounds like a bad query. The database will use TempDB if there’s insufficient RAM to process a query in memory.
Good place to start is to use SQL Server Management Studio to monitor expensive queries. Then start using Metabase to see what’s causing the problem.


#3

Thanks! I’ll have a look aand see. I noticed before that there were a lot of long running queries coming from Metabase, this morning when I turned it on I turned off caching, and I’m not seeing those queries anymore, nor is tempdb going crazy- could that have been the problem?


#4

Another thought - when you first add a database to Metabase (and thereafter on a schedule), it will scan the tables for filter data. Scanning table won’t affect the temp.db, but if there are any views, they will be scanned too.
In the database settings, you can turn this off (set to Large Databae), then just do manual scans as needed,


#5

Thanks Andrew! Just an update to this in case it helps someone else:
I never did figure out what was causing the problem, but I made it stop by creating a new user on the SQL server for Metabase that only had access to the 10 tables I needed for my reports, not the several thousand others that aren’t of my interest. Now whether Metabase is running or not, it has no noticeable effect on the servers resources. :slight_smile: