When we moved a database from SQL Server 2005 running on Windows Server 2003 R2 x86 with 4GB RAM and 1vCPU (vmware) to SQL Server 2012 Windows Server 2012 R2 SP2 (11.0.5623) with 8GB RAM and 2 vCPU (vmware and same host and storage) we noticed that Select querys where very slow. A simple "select * from tablename" took 60 sec in new server and 10 sec in old server.
We moved the database like:
1. backup and restore
2. Upgrade database compatibility level
3. dbcc updateusage (dbname)
4. Rebuild index and update stats (full scan).
Drop and recreate index and stats, still very slow, 60 sec.
After Days of troubleshooting we tried:
1. Create new empty database in SQL Server 2012.
2. Import data from old server to new server.
3. select * from tablename, result 8 sec, 2 sec faster than old server!!
Is this a known issue?
I can't find anything like this when I'm googling.