The problem is following. I have 2 SQL servers 2012 instances in two different virtual machines, let's call it DEV and PROD. When running a very simple parameterized query returning one row from one table with identical data structure, content, and query execution plans, the profiler in PROD shows much higher reads (like 15 times) and duration (5 times) than in DEV. Note, that PROD machine is 2 times more powerful than DEV.

Some days ago, it was the same, but then something happened, and it all went like this. No changes to data structure has been done. Indexes and statistics are up to date in both. Execution plans are identical in DEV and PROD.

So, i exclude SQL server as a reason. Apart from it, what can cause such behavior?

  • 1
    When you mention reads, do you mean logical reads or physical reads? – Dan Guzman Nov 26 '16 at 15:05
  • 1
    Using Profiler? Just use SET STATISTICS IO ON and see if they truly compare. Would also be nice to see the comparison because just telling us prod is a more powerful machine does not say anything about the disk between the two. – Shawn Melton Nov 26 '16 at 16:05
  • 2
    Any differences that aren't simply due to hardware / network / perception / lack of cached data are going to be seen in the plans (I question what you may deem as "identical"). Have you looked closely at the plans? See sqlperformance.com/2014/12/sql-plan/… and dba.stackexchange.com/questions/78503/… and dba.stackexchange.com/questions/53726/… – Aaron Bertrand Nov 26 '16 at 18:12
  • If you are comparing Dev and Production, are you comparing them equally? Production environments tend to have lots of competing requests while Dev environments tend not to. Additionally, Production environments tend to have data spread broadly across the data-files due to significantly more activity than a Dev environment. Even rebuilding a table cannot ensure the pages for table are near each other. There are more areas you need to look like like cache turn-over. – Robert Miller Nov 27 '16 at 2:20
  • Is the compatibility level the same in both? How recently was dev refreshed/còpied from live. And how was this done? You mention identical structure but not data. Do they both have the same volumes and distribution of data? – Sir Swears-a-lot Nov 27 '16 at 6:38

Your Answer

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

Browse other questions tagged or ask your own question.