The ArcGIS resources mention no such limitation related to the size of the Tables or the Database size itself, so there should not be any limitations as such.
Supported databases—Insights web help Use tab | ArcGIS Enterprise
It doesn't actually depend on the version of SQL Server, rather the edition of SQL Server matters, as SQL Server Express itself has a size limitation of 10 GB (Esri or Insights has nothing to do with this size limitation).
I have connected to Databases larger than 10GB and it worked fine.
Do remember, that Insights cannot connect to an Enterprise SQL Server geodatabase. It supports simple databases only.
I would suggest that you test on a Testing Environment first and then get back here if you face any roadblocks or performance issues.