1

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?

Oleksandr
  • 111
  • 2
  • 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. –  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 https://sqlperformance.com/2014/12/sql-plan/different-plans-identical-servers and http://dba.stackexchange.com/questions/78503/identical-servers-but-different-query-execution-plans and http://dba.stackexchange.com/questions/53726/difference-in-execution-plans-on-uat-and-prod-server – 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 02: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 06:38
  • Yes, i mentioned identical content in the name. Compatibility level is the same, data files structure as well. Seems like something "outside" SQL server is slowing down the whole thing. I did not mention that both instances are hosted in VM's, and most likely PROD VM has some problems with IO. I just need to find the way to confirm it. – Oleksandr Nov 27 '16 at 07:55
  • Are the hosts over subscribed? We had an issue with odd sized machines on same hosts. We found that if the vms had the same cpu settings the hosts would allocate resources more evenly. – Sir Swears-a-lot Nov 28 '16 at 10:18
  • Can you rule out SAN I/O? Move datastore? Are other servers with same storage also slow? Can you move vm to different host? – Sir Swears-a-lot Nov 28 '16 at 10:23
  • Well, good old server restart helped. I have no idea what caused the issue and why, but it's gone. Thanks everyone! – Oleksandr Dec 02 '16 at 19:21

0 Answers0