Help..SQL Server 2012 Query when using 'Order By' Query runs very slow

sbornstein2
sbornstein2 used Ask the Experts™
on
I am using SQL Server 2012 on my local home machine and the query I have runs within 1 second everytime.   On a new production server VM machine with a lot of memory etc. the same query takes over a minute to run only when using the order by.   It runs slower and slower each time as well.   Here is the query:   The first runs in about 1 second without the order by.   The order by it takes over a minute 20K records.   The order table OrderNumberId is the Primary Key and I also updated statistics on the table as well.   Each time I run it the query takes longer and longer with the order by.

This is what profiler trace is running:

--this query I have the order by commented out - 1 second
SELECT
[Extent1].[OrderNumberId] AS [OrderNumberId],
[Extent1].[OrderNumber] AS [OrderNumber],
[Extent1].[OrderTime] AS [OrderTime],
[Extent1].[PickupTime] AS [PickupTime],
[Extent1].[SortNumber] AS [SortNumber],
[Extent1].[PickupTimeSort] AS [PickupTimeSort],
[Extent1].[PickupCity] AS [PickupCity],
[Extent1].[PickupDate] AS [PickupDate],
[Extent1].[PickupState] AS [PickupState],
[Extent1].[Dcity] AS [Dcity],
[Extent1].[Dstate] AS [Dstate],
[Extent1].[DeadLineTime] AS [DeadLineTime],
[Extent1].[Po1] AS [Po1],
[Extent1].[PiecesWeight1] AS [PiecesWeight1],
[Extent1].[PiecesWeight2] AS [PiecesWeight2],
[Extent1].[ProNumber] AS [ProNumber],
[Extent1].[OrderTakenBy] AS [OrderTakenBy],
[Extent1].[CustCode] AS [CustCode],
[Extent1].[VehicleCodeName] AS [VehicleCodeName],
[Extent1].[OrderStatusCodeName] AS [OrderStatusCodeName],
[Extent1].[OrderStatusOrder] AS [OrderStatusOrder],
[Extent1].[DriverCode] AS [DriverCode]
FROM (SELECT
      [VwOrderDispatch].[OrderNumberId] AS [OrderNumberId],
      [VwOrderDispatch].[OrderNumber] AS [OrderNumber],
      [VwOrderDispatch].[OrderTime] AS [OrderTime],
      [VwOrderDispatch].[PickupTime] AS [PickupTime],
      [VwOrderDispatch].[SortNumber] AS [SortNumber],
      [VwOrderDispatch].[PickupTimeSort] AS [PickupTimeSort],
      [VwOrderDispatch].[PickupCity] AS [PickupCity],
      [VwOrderDispatch].[PickupDate] AS [PickupDate],
      [VwOrderDispatch].[PickupState] AS [PickupState],
      [VwOrderDispatch].[Dcity] AS [Dcity],
      [VwOrderDispatch].[Dstate] AS [Dstate],
      [VwOrderDispatch].[DeadLineTime] AS [DeadLineTime],
      [VwOrderDispatch].[Po1] AS [Po1],
      [VwOrderDispatch].[PiecesWeight1] AS [PiecesWeight1],
      [VwOrderDispatch].[PiecesWeight2] AS [PiecesWeight2],
      [VwOrderDispatch].[ProNumber] AS [ProNumber],
      [VwOrderDispatch].[OrderTakenBy] AS [OrderTakenBy],
      [VwOrderDispatch].[CustCode] AS [CustCode],
      [VwOrderDispatch].[VehicleCodeName] AS [VehicleCodeName],
      [VwOrderDispatch].[OrderStatusCodeName] AS [OrderStatusCodeName],
      [VwOrderDispatch].[OrderStatusOrder] AS [OrderStatusOrder],
      [VwOrderDispatch].[DriverCode] AS [DriverCode]
      FROM [dbo].[VwOrderDispatch] AS [VwOrderDispatch]) AS [Extent1]
--ORDER BY [Extent1].[OrderNumberId] DESC

With the order by uncommented it takes over a minute and gets worst every time I run it.

Any ideas what I can do or look for?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I wish it was that but no dice.   The order by is still running really slow.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
any chance of an execution plan? (.sqlplan file)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
VwOrderDispatch:  Hope that view doesn't include an order by. Can we see that sql?

Author

Commented:
ALTER VIEW [dbo].[VwOrderDispatch]
AS
SELECT        dbo.[Order].OrderNumberId,
                    dbo.[Order].OrderNumber, dbo.[Order].OrderTime, dbo.[Order].PickupTime, dbo.[VehicleSort].SortNumber,
                    Convert(char(5), dbo.[Order].PickupTime,8) As PickupTimeSort,
                  dbo.[Order].PickupCity, dbo.[Order].PickupDate, dbo.[Order].PickupState,  
                    dbo.[Order].Dcity, dbo.[Order].Dstate, dbo.[Order].DeadLineTime, dbo.[Order].Po1,
                    dbo.[Order].PiecesWeight1, dbo.[Order].PiecesWeight2, dbo.[Order].ProNumber, dbo.[Order].OrderTakenBy,
                    dbo.Customer.CustCode, dbo.VehicleCode.VehicleCodeName, dbo.OrderStatusCode.OrderStatusCodeName,dbo.OrderStatusCode.OrderStatusOrder,
                    dbo.Driver.DriverCode
FROM          dbo.[Order] LEFT JOIN
                         dbo.Driver ON dbo.[Order].DriverId = dbo.Driver.DriverId LEFT JOIN
                         dbo.OrderStatusCode ON dbo.[Order].OrderStatusCodeId = dbo.OrderStatusCode.OrderStatusCodeId LEFT JOIN
                         dbo.Customer ON dbo.[Order].CustId = dbo.Customer.CustId LEFT JOIN
                         dbo.VehicleCode ON dbo.[Order].VehicleCodeId = dbo.VehicleCode.VehicleCodeId LEFT JOIN
                                     dbo.VehicleSort ON dbo.[Order].VehicleCodeId = dbo.VehicleSort.VehicleCodeId

GO

Author

Commented:
attached sql plan
sqlplanorder.zip

Author

Commented:
One thing I see is the Hyper V new server shows in the execution plan "had to wait 58 seconds for memory grant" however on my machine I don't have this warning at all.   So there is one issue any idea what that means?

Author

Commented:
That memory grant was most of the time the query took to run any idea why?
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
>>"... On a new production server VM machine with a lot of memory ..."
>>"That memory grant was most of the time the query took to run any idea why?"

The execution plan does reveal exactly that
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" MemoryGrant="20936" CachedPlanSize="240" CompileTime="109" CompileCPU="82" CompileMemory="1040">
            <Warnings>
              <Wait WaitType="Memory Grant" WaitTime="58" />
            </Warnings>
            <MemoryGrantInfo SerialRequiredMemory="4608" SerialDesiredMemory="20936" RequiredMemory="4608" DesiredMemory="20936" RequestedMemory="20936" GrantWaitTime="57773" GrantedMemory="20936" MaxUsedMemory="6496" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104819" EstimatedPagesCached="52409" EstimatedAvailableDegreeOfParallelism="4" />
            <RelOp AvgRowSize="441" EstimateCPU="1.63865" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="24485" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="2.92964">

Open in new window

but I don't know why as I'm not personally very experienced with VM setup.
Brian CroweDatabase Administrator
Top Expert 2005

Commented:
Is the VM Host over-committed on RAM?  If you have assigned more ram to the VM's on the host than the host physically contains then you could have a conflict.
I'm sure there might be better (more fundamental) solutions to improve the performance of that query, but just a quick-fix suggestion, I would try inserting the inner select into a temporary table (i.e. #my_view) and then use that temporary table in the main select query. You can also verify the execution plan to see if there's any room for improvement by creating an index on that temporary table - you can't get any recommendation for missing indexes from SQL Query Optimization engine in the case of nested selects, but replacing it with a temp table allows you to get that info.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
given the simplicity of the view, just use that query and ignore the view (for the execution plan)

probably some of those left joins should be inner joins

but as the overall query isn't complex at all, I think the solution is to be found in he VM and it isn't a SQL issue
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
ORDER BY / sort requires memory to do the sorting.  Since memory allocations are such a performance issue, the sort itself is a performance issue.

You really shouldn't be sharing SQL's primary memory with other things.

If you can afford all dedicated memory for SQL, set the min and max memory to the same amount, thus using fixed memory.  This is often best for overall performance anyway.

If you can't, set the min memory setting to what you can afford to dedicate to SQL.

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial