Link to home
Start Free TrialLog in
Avatar of sbornstein2

asked on

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

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
[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]
      [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?
Avatar of Tom
Flag of Norway image

Avatar of sbornstein2


I wish it was that but no dice.   The order by is still running really slow.
Avatar of PortletPaul
any chance of an execution plan? (.sqlplan file)
VwOrderDispatch:  Hope that view doesn't include an order by. Can we see that sql?
ALTER VIEW [dbo].[VwOrderDispatch]
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,
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

attached sql plan
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?
That memory grant was most of the time the query took to run any idea why?
Avatar of PortletPaul
Flag of Australia image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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
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.