-1

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

In Prod--

(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.

In Dev--

(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 ?

1

Two things that you need to look at when comparing the difference in performance on your dev server and your prod server are the differences in the amount of data on dev and prod and the amount of use/load that both servers have.

If the prod server is a lot larger then the dev server (As would be expected) your queries will take longer. Also if your prod server has more use/load on it then dev it will increase the amount of time your queries need to run.

In order to provide better advice more information about the differences of the two environments will be needed.

  • They have the same number of CPU and Same RAM and are on the Same Host and SAN. The Disk is Larger on Prod. There are actually less transactions running on Prod since Dev is used for other testing as well. – user146892 Mar 19 '18 at 15:31
1

The first test query you show uses no resources other than CPU and a tiny amount of RAM.

Check the VM's settings. I would guess the prod VM doesn't have a CPU reservation whereas the dev VM does have CPU reserved.

The stats you provided are for another query; please provide the create-table script, and the actual execution plans for the queries in prod and dev. Interestingly, the elapsed time for both prod and dev are almost the same (i.e. less than 10% difference).

Since you state there is less activity on prod than on dev, its possible dev has all it's memory in physical ram on the host server, whereas the prod server is having it's memory paged in-and-out as needed. You should ensure memory for both servers is "reserved" on the VMware host.

  • Thank You I will check those settings. Sorry the Statistics are for the same table. I just masked the table name for security reasons. – user146892 Mar 19 '18 at 15:51
  • I checked with VM/SAN Admin - We aren’t using dynamic resource provisioning so there aren’t really any reservations. But Dev is showing a lot more active memory than Prod. They are not sure why. – user146892 Mar 19 '18 at 17:36
  • In VMware, for SQL Server, RAM, and CPU should always be reserved. See this pdf for more details – Max Vernon Mar 19 '18 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.