What is this?

This is basically where I write down stuff that I work with at my job as a GIS Technical Analyst (previously system administrator). I do it because it's practical for documentation purposes (although, I remove stuff that might be a security breach) and I hope it can be of use to someone out there. I frequently search the net for help myself, and this is my way of contributing.

Thursday, March 20, 2014

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.