I run the following query on a SQL 2008 R2 Server 2012 box with 4 processors and 20GB of ram the script max's out CPU usage to 100% and takes 15 minutes to run.
running the same script against the same data on a Virtual box with 8GB ram 1 processor takes 20 seconds to run and CPU usage never goes above 50%. This box is running SQL 2014 Express on a windows 7 machine.
This wasn't always a problem on the 2012 box either. Seems to has just started happening an no one know what changed.
Any ideas on more trouble shooting steps or changes we could try?
SELECT "OEHDRHST_SQL"."inv_dt", "OEHDRHST_SQL"."cus_no",
"OELINHST_SQL"."item_no", "OELINHST_SQL"."loc",
"OEHDRHST_SQL"."orig_ord_type", "OELINHST_SQL"."qty_to_ship",
"OELINHST_SQL"."uom_ratio", "OEHDRHST_SQL"."inv_no",
"OEHDRHST_SQL"."ord_no", "OEHDRHST_SQL"."ord_type",
"cicmpy"."debcode", "cicmpy"."cmp_name",
"OELINHST_SQL"."sls_amt", "OELINHST_SQL"."cost_amt",
"OELINHST_SQL"."discount_pct", "OELINHST_SQL"."qty_ordered"
FROM ("058"."dbo"."oehdrhst_sql" "OEHDRHST_SQL"
LEFT OUTER JOIN "058"."dbo"."oelinhst_sql" "OELINHST_SQL" ON
"OEHDRHST_SQL"."inv_no"="OELINHST_SQL"."inv_no")
LEFT OUTER JOIN "058"."dbo"."cicmpy" "cicmpy" ON
"OEHDRHST_SQL"."cus_no"="cicmpy"."debcode"
WHERE OEHDRHST_SQL."Inv_Dt"
BETWEEN {d '2016-04-01'} AND {d '2016-04-30'} AND
"OEHDRHST_SQL"."ord_type"='O'
ORDER BY OEHDRHST_SQL."inv_dt" ASC,
OEHDRHST_SQL."cus_no" ASC,
OEHDRHST_SQL."inv_no" ASC
Turn on execution plan in SSMS (Query->Include Actual Execution Plan from the top menu bar) and run the query on both servers. The execution plan will be displayed after the query completes. Compare the two execution plans to see where the slower running query is consuming most resources.
You also should check whether the indexes need reorganization or rebuilding. You will find native T-SQL scripts as well as third party scripts ( Olla Hallengren, Minion Reindex etc.) which can help you do this.
Also, check if there is anything unusual in the configuration of the servers. You can look through the settings by right clicking on the server name in the SSMS object explorer and selecting properties.
Another thing you might consider is to look through the wait stats. See Paul Randal's blog here. The wait stats are counters since the last restart of the server, so you may need to reset the stats and run the query to get useful info.