We are currently testing SQL 2014 in our QA environment to ensure there are no performance or functional issues once we upgrade our Production environment.
Our SQL 2014 instance runs on a VM server with OS being Server 2012 R2 Standard. The Specs of the New VM Server is the same as our existing QA SQL VM running Server 2008 R2 and SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64). LF Server and WF Server is currently 9.2.0 SP1.
From all the tests we have done this far, functionality and performance seem either the same or slightly better. We do however have one concern. When performing a DATE range search, like Entries created in a specific time frame combined with either a Field or Template search, the search results take very long to return and in certain instance, the Search actually times-out. If the number of entries being search for resulted in a large result set I could understand the behaviour but our result sets are between 45 and 500 documents. On the one specific search where we looked for entries on a specific Template and created in "This Week" only after the 3rd attempt could we get a result. the first 2 attempts failed and errors where reported that the search had timed-out. Afther that, we could more consistently get results, they still take more than 5 minutes to return results, but they do not time out.
We did a similar test from the Search activity in Workflow and even this activity would take close to a minute to return the 45 results.
Now comparing this to the same repository working of a SQL 2008 R2 SQL instance, the Client search took approximately 2 min's to return results and the WF search would be in a couple of seconds.
This then raised the question if there is any specific configs that needs to be done against SQL 2014 to avoid this type of performance problem? We do have re-building of indexes being done on the SQL server. And as mentioned, when doing a plain Field search or even a Date range search on their own and not combined, the results are much better. it is only when one combines search criteria that there is a very noticeable performance drop.
Any ideas or advice?