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?

Tom

Reading this funny article might help:
http://www.masterofmalt.com/software-development/blog/why-microsoft-windows-server-2012-produces-slow-and-inconsistent-sql-query-speeds/

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)

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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

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.

sbornstein2

ASKER

thanks