I used to run SQL Server 2008 R2 Express to do my .NET developments, but with the New Year I decided to upgrade my whole environment - that means that now I'm using Windows 8 and SQL Server 2012 Express SP1.
So, first thing I did was to attach the old databases (created in SQL Server 2005 and 2008) to the new server (SQL Server 2012). No errors on doing that.
However, every time my application performs a query on this database, it runs really slow. After a lot of tests within IIS, Windows, and SQL Server, I found out that it's almost certain that the problem relies on the database (or the sql server engine).
Everytime I rebuild the indexes for this database, the queries execute instantly (I mean, ~0.1 seconds). However, after a couple of minutes (or rebooting) the exact SAME statements start to run really slower (around 10 seconds).
I've upgraded Windows, SQL Server, and even applied the SQL Server 2012 SP1 Cumulative Update 1. Same problem.
The databases behave completely normal (which means really quick) in hundreds of production environments -- all of them created in SQL Server 2005 and all of them running either in SQL Server 2005 or 2008. All of the queries work fine when running in 2005 and 2008.
The same queries on the same data (the same DB) perform really really bad (1sec agaisnt 1min, for instance) in SQL Server 2012. Once I update the statistics in the database on the SQL 2012 server, it gets quite quick, as it does in SQL Server 2005 and 2008. However, after a couple of minutes and without *any update*, it starts to get slow again. I've got no explanation to that behavior.
Thanks in advance.
PS1: The exact version I'm running is: Microsoft SQL Server 2012 (SP1) - 11.0.3321.0 (X64) Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Windows 8 Pro)
PS2: I'm using this to rebuid the indexes: EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"