Tuning GeoServer MSSQL data stores

At work we recently installed some new layers on our GeoServer 2.4.4 which queried for spatial data from a MSSQL 2008r2 database. Unfortunately we soon realized we had some serious performance issues. I tried the usual tricks - updating statistics, checking indexes etc. Microsofts Database tuning engine advisor eventually showed that things were decent - yet we still expericnced poor performance.

I then tried modifying settings for the data store. For MSSQL the default settings are:

max connections:	10
min connections:	1
fetch size:	1000
connection timeout:	20
validate connections:	enabled
Use native geometry serialization:	disabled

I ended up changing the settings to

max connections:	20
min connections:	5
fetch size:	5000
connection timeout:	20
validate connections:	disabled
Use native geometry serialization:	enabled

Performance got a real nice boost. I estmate that things are at least twice as quick now, and This is especially noticeable when the MSSQL server is also quite busy with other tasks (especially heavy disk/network activity during backup).

Especially the "validate connections" parameter seems to have a noticeable impact. According to the documentation this parameter increases the risk of client errors. I suppose I will have to monitor the services for a while to see if they remain stable.