All Queries are slow on Prod than Dev Server(SQL 2012). We have put the servers on the same host and changed the storage to the same SAN. All resources including core and memory are the same. All configuration on the SQL Server are the same and Database was restored from Prod to Dev. It is not just one Query all read Queries seem to be slower on Prod. Inserts take almost the same time. Any help on where we should look is appreciated. The number of transactions is not much higher in Prod at this point.
Even Simple query Like Select * from tablename takes longer in Prod Server. Execution Plans are same except EstimateIO and Cost are different in two servers.
Queries not reading from table are also taking longer in Prod
Simple test Query
Declare @i as int = 0 While @i < 10000 Begin Set @i = @i+1 End
Simple Read Query and Statistics
set statistics time on SET STATISTICS IO ON select * from TABLE_NAME; SET STATISTICS IO OFF set statistics time off
(20455 row(s) affected) Table '******'. Scan count 1, logical reads 1353, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 218 ms, elapsed time = 1194 ms.
(20455 row(s) affected) Table '*******'. Scan count 1, logical reads 1353, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 94 ms, elapsed time = 1102 ms.
Same number of rows in both servers, they are VM's on the same host and same volume on Disk. The bigger difference seems to be in CPU time Why ?