I have encountered similar issues with the 40 rows being some sort of magic number with respect to joins against a SQL server enterprise geodatabase.
1. If I use SelectLayerByAttributes from a stand-alone Python script against a joined layer it only returns the first 40 matching rows
2. If I use the FeatureClassToFeatureClass tool in ArcGIS Pro with a where clause against a joined layer the resulting feature class will have the correct number of output rows, but all rows after row 40 are duplicates (of row 40)