Select with CROSS APPLY runs slow
You need better indexing, and a bit of a rewrite.
Because you’re only using one table, you can use ROW_NUMBER() in a sub-query and then grab the top row from each partition.
SELECT grp_fk_obj_id, grp_name, grp_id
FROM (
    SELECT grp_fk_obj_id, grp_name, grp_id, ROW_NUMBER() OVER (PARTITION BY grp_fk_obj_id ORDER BY date_from DESC, ISNULL(date_to, '4000-01-01') DESC) AS rownum
    FROM tbl_groups
) g
WHERE g.rownum = 1;

But also, fix your ORDER BY clause, because it forces it to do the Sort. Because you’re ordering on an expression, you can’t index it effectively. If you can force date_to to have a value, order by date_from desc, date_to desc, then an index on (grp_fk_obj_id, date_from, date_to) include (grp_name) will really help. It would even make your original query run way quicker.