Hi all, been pulling my hair out today. We have a set of HR databases that currently live on 3 different SQL servers. One is the test Server, one is the current live server, and one is soon to take the place of the live server.
We have a number of queries that the application runs, and discovered awful performance on the soon to be live server.
On the test server (sql server 2008r2 sql express SP2) the query returns in a few seconds, on the current live server (sql server 2008r2 standard edition SP1) the query returns in a few seconds, on the soon to be live server (sql server 2008r2 standard edition SP2) the same query takes 10 minutes!
The soon to be live server hosts multiple user databases (20 plus), has 12gb of RAM, 8 cores and is a virtual machine.
The live server has 2gb of RAM, 2 cores and is virtual, it only hosts the HR databases
the test server has 2gb of RAM 2 cores and is virtual (I will need to check this to verify) only hosts the HR dbs
Though the soon to be live server has multiple databases it is not under any significant load, and there is no memory pressure as far as I can see, we recently upped it from 8gb to 12gb thinking this was the cause but with no success. One thing I noticed that when the query is running, it does push the CPU up, with each CPU maxing out at 100% for a short period of time, then the cpu drops off on that core and moves onto the next.
The funny thing is, there are no waits associated with the query when I monitor it in activity monitor, normally I would expect to see pageiolatch, locks or something to give me a clue, but in this case nothing.
looking at sp_lock when the query runs there are a lot of sch-s locks, but they shouldn't be causing any problem should they?
Indexes have been rebuilt, and are tiny anyway, the tables are relatively small, none over 300mb I don't think (don't have the database in front of me at the moment).
The one query I have been focussing my efforts on uses a join with a couple of matching predicates, with 3 or 4 filter clauses, it is querying views that reference themselves. It is not the best db design, but the queries still return in no time on the test and live servers.
If you need any more info let me know, hopefully someone will have some ideas about this!
Thanks in advance