I ran the query from both SQL Server Management Studio and Toad using bind variables rather than static and observed that the query took approximately 6-7 seconds to execute to return a total of 250 rows. This was using the composite key of itemId/plantId equating to 500 bind variables.

Which query? Sorry if I'm getting confused. Is this proof that the SQL query needs to be different or that - no matter what SQL you use - that amount of bind variables is problematic on SQL Server?

I think whatever the generated SQL query is, it would be reasonable to patch the Hibernate Search code to not load all entries in a single batch, but split them up in blocks of some parameter N. I guess it would be easy for you to patch this in Search and verify: propose a new method to set the option and send a patch proposal.