We have "SET NOCOUNT OFF" by default on our database server. We use "SET NOCOUNT ON" for store procedures. As reported by dba's that all nhibernate generated select statements are using "SET NOCOUNT OFF". Which is taking long for queries to execute. We are trying to increase the performance. I can not figure out the way to set "SET NOCOUNT ON" for specific nhibernate session or query. Can someone have some opinion about that.
You probably misunderstand what SET NOCOUNT ON does and why.
SET NOCOUNT does not have such a significant effect that it becomes a concern. Setting it to ON on statements that DON'T return data is simply an optimization.
On the other hand, setting it to ON on queries, where you very much want to know how many results were returned, makes no sense. Instead of quickly detecting how many results there are, your client would have to enumerate all the data to see how many rows are returned.
Your server will return the data in any case, so telling it to NOT return the number of rows it returns makes no sense.
You probably have other performance issues. You should check what queries are executed, whether your tables have proper the indexes and whether you force NHibernate to execute more queries than you expect (the dreaded N+1 problem)