Avatar of sbornstein2
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
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?
Microsoft SQL Server

Avatar of undefined
Last Comment
sbornstein2

8/22/2022 - Mon
Tom

sbornstein2

ASKER
I wish it was that but no dice.   The order by is still running really slow.
PortletPaul

any chance of an execution plan? (.sqlplan file)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
PortletPaul

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

ASKER
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
sbornstein2

ASKER
attached sql plan
sqlplanorder.zip
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sbornstein2

ASKER
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?
sbornstein2

ASKER
That memory grant was most of the time the query took to run any idea why?
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Brian Crowe

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ProjectChampion

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.
PortletPaul

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 Pletcher

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sbornstein2

ASKER
thanks