Query slows down by a factor of 250 to 450 when increasing maximum server memory setting Why ? (Network Steve Forum)

Query slows down by a factor of 250 to 450 when increasing maximum server memory setting Why ?

We have an SQL server 2012 SP1 CU6 running on a virtual machine that was P2V'ed from a physical blade server.

configured from the physical server was maintained hence it is still configured with 4 CPU's and 6 Cores hence having access to 24 cores in total.
The physical server had 128 gb.

The host (DL580G7, 4 cpu's with 10 cores, 1 TB memory) is running ESXI 5.1

All job are running slightly better on the VM due the better hardware.

Now the funny part starts. After doing the initial testing with the machine configuration which was all good we wanted to increase the VM memory to 512 gb. as we have a total of 1 TB of memory in our hosts and no memory pressure.

We configured the maximum server memory to 80% of the 512 gb. memory and then some of our queries starting taking ages.
We tweaked and adjusted the maximum server memory and found out if we configured it to 330 gb. then server runs just fine but if we go above say 335 gb. then the following
job.:

USE Datamart
exec sp_executesql N'set dateformat dmy exec ssis.tblSumOfStock @P1',N'@P1 datetime2(0)','2013-01-31 00:00:00'
GO

Normally the job takes between 76 and 77 sec. but with the increased memory setting it has taken between 19960 and 35254 seconds (roughly 5h 30m - 9h 45m). and if max memory is lowered execution time return to 76 sec.

We have tried the following.

1.: Reserving all memory on the host
2.: Running sql server with flag 0815 and 8048 to see if numa was the cause but with 330 gb. is still goes across several numa nodes.
3.: Tried isolation the VM on two different hosts - Same issue.
4.: Tried SQL flag to aquirie all memory on startup.
5.: MAXDOP -1 job still very slow
6.: Increasing parallel cost to different values - again slow run
7.: Comparing execution plans with the different memory settings does not give any clues to why.

I simply cant figure when this is happening. Have any of you noticed anything like this or know what could cause this ?

When running the job with max memory setting below and above the 330 gb mark the server acquires a total of 60 gb memory hence there is no memory pressure.

Edited by Martin Emanuelsen Friday, January 10, 2014 10:58 AM

January 10th, 2014 1:27pm

So do you get the same plan or different plans with the different memory settings? Any chance that you can supply at least the estimaated plans somewhere? (The actual plans would of course be better, but obviously it will take some time to get the slow plan.)

Free Windows Admin Tool Kit Click here and download it now

January 10th, 2014 5:11pm

You say no memory pressure and perhaps there SHOULD be no memory pressure, but when you use the higher memory setting are you checking for paging activity and other disk/IO activity? There are all kinds of obscure things that can go wrong with VM configurations. Are you sure that physical RAM is really available and not used by other VMs on the server?

Josh

January 10th, 2014 7:38pm

There were fixes to performance problems when using >256GBs of RAM. Please post the results of SELECT @@VERSION to verify the version you are running.

It is possible a similar problem is happening. I would suggest opening a ticket with MS support.

Free Windows Admin Tool Kit Click here and download it now

January 10th, 2014 7:42pm

There were fixes to performance problems when using >256GBs of RAM. Please post the results of SELECT @@VERSION to verify the version you are running.

It is possible a similar problem is happening. I would suggest opening a ticket with MS support.

I was going to suggest the same thing, but what I vaguely recall had to do with CPU utilization and this looks different ... though that doesn't mean it can't be some OTHER problem with big iron! If a review of the VM operation doesn't show a problem, I agree the next step would be an MS ticket.

Josh

January 11th, 2014 2:37am

Please post execution plans for both queries, as Erland suggested.

You mentioned that you compared them, but no information if they are identical. If they are not identical, I would focus on them first.

I had similar problem in the past, SQL Server was changing execution plan from nested loop to hash table when it had enough memory for the hash table. Similar difference in times due to invalid estimation of number of rows and cost of building hash table.

Free Windows Admin Tool Kit Click here and download it now

January 11th, 2014 6:22am

Functional plan.:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.3393.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtUseDb StatementCompId="1" StatementId="1" StatementText="USE [DataMart]
" StatementType="USE DATABASE" RetrievedFromCache="false" Database="[DataMart]" />
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementId="1" StatementText="
/****** Object: StoredProcedure [ssis].[tblSumOfStock] Script Date: 07-01-2014 13:19:59 ******/
SET ANSI_NULLS ON
" StatementType="SET ON/OFF" RetrievedFromCache="false" />
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="0" StatementId="1" StatementText="







CREATE procedure [ssis].[tblSumOfStock] @StockDate as datetime

as


INSERT INTO [Stock].[tbllog]
 ([Tid],[Stockdate])
select GETDATE() as Tid, @StockDate as [Stockdate]

SELECT top 100 percent 
trans.DataAreaId, 
trans.itemid,
trans.inventdimid,
inventlocationid,
inventbatchid,
sum(trans.[QTY]) as financialQty,
sum(trans.[COSTAMOUNTPOSTED]) + sum(isnull(s.[COSTAMOUNTADJUSTMENT],0)) as financialValue,
sum(isnull(s.[COSTAMOUNTADJUSTMENT],0)) as Financialadjust
into #tmpStockFinancial
FROM (
 select trans.itemid,
 inventlocationid,
 inventbatchid,
 trans.InventDimId,
 trans.DataAreaId,
 trans.[QTY],
 CostAmountPhysical,
 costamountposted ,
 trans.recid 
 FROM ERP.[tblInventTrans] trans 
 inner join ERP.tblinventdim dim on 
 trans.DataAreaId = dim.DataAreaId 
 and trans.inventdimid = dim.inventdimid
 where trans.DATEFINANCIAL <= @StockDate
 and (statusissue = 1 or STATUSRECEIPT = 1 )
 and trans.PackingSlipReturned = 0
 and trans.InventDimID not in ('00000003','00000220_077','00000221_077','00000222_077')
) trans
left join (
 select transrecid,SI.itemid,
 --SI.inventtransid,
 sum(sI.COSTAMOUNTADJUSTMENT) as COSTAMOUNTADJUSTMENT, sI.DataAreaId
 from ERP.tblInventSettlement SI 
 where SI.transdate <= @StockDate and cancelled = 0 and SETTLEMODEL < 7
 group by sI.DataAreaId,transrecid,SI.itemid --,SI.inventtransid
) s on 
 trans.recid = s.transrecid 
 and trans.DataAreaId = s.DataAreaId 
 and trans.itemid = s.itemid
group by trans.DataAreaId,
trans.itemid,
trans.InventDimId,
inventlocationid,
inventbatchid
having (sum(trans.[QTY]) <> 0 or sum(isnull(trans.[COSTAMOUNTPHYSICAL],0)) + sum(isnull(s.[COSTAMOUNTADJUSTMENT],0)) <> 0)
order by trans.DataAreaId, trans.itemid, trans.InventDimId -- hjlper p performance
option (force order)

/*
CREATE CLUSTERED INDEX [IX_InventSumTemp] ON [dbo].[#tmpStockFinancial]
(DataAreaId ASC,
itemid ASC,
inventdimid ASC
) 
--INCLUDE ([inventlocationid],[inventbatchid],[financialQty],[financialValue],[Financialadjust])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
*/

insert into Stock.tblSumOfStock
select isnull(physical.StockDate,financial.StockDate) as stockdate, 
isnull(physical.itemid, financial.itemid ) as itemid, 
isnull(physical.inventdimid, financial.inventdimid ) as inventdimid, 
isnull(physical.inventlocationid, financial.inventlocationid ) as inventlocationid, 
isnull(physical.inventbatchid, financial.inventbatchid ) as inventbatchid, 
isnull(physical.DataAreaId, financial.DataAreaId ) as DataAreaId, 
isnull(PhysicalQty,0) as PhysicalQty,
isnull(FinancialQty,0) as FinancialQty,
isnull(pv,0) as Physicalvalue,
isnull(FinancialValue,0) as FinancialValue,
isnull(Financialadjust,0) as FinancialAdjust

from ( select @StockDate as StockDate,
 trans.itemid,
 inventlocationid,
 inventbatchid,
 trans.InventDimId,
 trans.DataAreaId,
 sum(trans.[QTY]) PhysicalQty , 
 sum(CostAmountPhysical) as pv
 FROM (-- #TransDim1 
 select trans.itemid,
 inventlocationid,
 inventbatchid,
 trans.InventDimId,
 trans.DataAreaId,
 trans.[QTY],
 --costamountposted, s.COSTAMOUNTADJUSTMENT,
 case when trans.datefinancial <= @stockdate 
 then costamountposted + isnull(s.COSTAMOUNTADJUSTMENT ,0)
 else CostAmountPhysical
 end
 as CostAmountPhysical,
 --CostAmountSettled,
 --costamountposted,
 datefinancial
 --,
 --valueOpen
 --case valueopen when 0 then CostAmountSettled else costamountposted end as openvalue
 FROM ERP.[tblInventTrans] trans 
 inner join ERP.tblinventdim dim on 
 trans.DataAreaId = dim.DataAreaId and
 trans.inventdimid = dim.inventdimid
 left join ( 
 select transrecid,SI.itemid, --SI.inventtransid,
 sum(sI.COSTAMOUNTADJUSTMENT) as COSTAMOUNTADJUSTMENT, sI.DataAreaId
 from ERP.tblInventS" StatementType="CREATE/ALTER PROCEDURE" RetrievedFromCache="false" />
</Statements>
<Statements>
<StmtSimple StatementCompId="0" StatementId="2" StatementType="CREATE/ALTER PROCEDURE" RetrievedFromCache="false" />
</Statements>
<Statements>
<StmtSimple StatementCompId="0" StatementId="3" StatementType="CREATE/ALTER PROCEDURE" RetrievedFromCache="false" />
</Statements>
<Statements>
<StmtSimple StatementCompId="0" StatementId="4" StatementType="CREATE/ALTER PROCEDURE" RetrievedFromCache="false" />
</Statements>
<Statements>
<StmtSimple StatementCompId="0" StatementId="5" StatementType="CREATE/ALTER PROCEDURE" RetrievedFromCache="false" />
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

Non functional plan.:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.3393.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtUseDb StatementCompId="1" StatementId="1" StatementText="USE [DataMart]
" StatementType="USE DATABASE" RetrievedFromCache="false" Database="[DataMart]" />
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementId="1" StatementText="
/****** Object: StoredProcedure [ssis].[tblSumOfStock] Script Date: 07-01-2014 13:23:46 ******/
SET ANSI_NULLS ON
" StatementType="SET ON/OFF" RetrievedFromCache="false" />
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="0" StatementId="1" StatementText="







CREATE procedure [ssis].[tblSumOfStock] @StockDate as datetime

as


INSERT INTO [Stock].[tbllog]
 ([Tid],[Stockdate])
select GETDATE() as Tid, @StockDate as [Stockdate]

SELECT top 100 percent 
trans.DataAreaId, 
trans.itemid,
trans.inventdimid,
inventlocationid,
inventbatchid,
sum(trans.[QTY]) as financialQty,
sum(trans.[COSTAMOUNTPOSTED]) + sum(isnull(s.[COSTAMOUNTADJUSTMENT],0)) as financialValue,
sum(isnull(s.[COSTAMOUNTADJUSTMENT],0)) as Financialadjust
into #tmpStockFinancial
FROM (
 select trans.itemid,
 inventlocationid,
 inventbatchid,
 trans.InventDimId,
 trans.DataAreaId,
 trans.[QTY],
 CostAmountPhysical,
 costamountposted ,
 trans.recid 
 FROM ERP.[tblInventTrans] trans 
 inner join ERP.tblinventdim dim on 
 trans.DataAreaId = dim.DataAreaId 
 and trans.inventdimid = dim.inventdimid
 where trans.DATEFINANCIAL <= @StockDate
 and (statusissue = 1 or STATUSRECEIPT = 1 )
 and trans.PackingSlipReturned = 0
 and trans.InventDimID not in ('00000003','00000220_077','00000221_077','00000222_077')
) trans
left join (
 select transrecid,SI.itemid,
 --SI.inventtransid,
 sum(sI.COSTAMOUNTADJUSTMENT) as COSTAMOUNTADJUSTMENT, sI.DataAreaId
 from ERP.tblInventSettlement SI 
 where SI.transdate <= @StockDate and cancelled = 0 and SETTLEMODEL < 7
 group by sI.DataAreaId,transrecid,SI.itemid --,SI.inventtransid
) s on 
 trans.recid = s.transrecid 
 and trans.DataAreaId = s.DataAreaId 
 and trans.itemid = s.itemid
group by trans.DataAreaId,
trans.itemid,
trans.InventDimId,
inventlocationid,
inventbatchid
having (sum(trans.[QTY]) <> 0 or sum(isnull(trans.[COSTAMOUNTPHYSICAL],0)) + sum(isnull(s.[COSTAMOUNTADJUSTMENT],0)) <> 0)
order by trans.DataAreaId, trans.itemid, trans.InventDimId -- hjlper p performance
option (force order)

/*
CREATE CLUSTERED INDEX [IX_InventSumTemp] ON [dbo].[#tmpStockFinancial]
(DataAreaId ASC,
itemid ASC,
inventdimid ASC
) 
--INCLUDE ([inventlocationid],[inventbatchid],[financialQty],[financialValue],[Financialadjust])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
*/

insert into Stock.tblSumOfStock
select isnull(physical.StockDate,financial.StockDate) as stockdate, 
isnull(physical.itemid, financial.itemid ) as itemid, 
isnull(physical.inventdimid, financial.inventdimid ) as inventdimid, 
isnull(physical.inventlocationid, financial.inventlocationid ) as inventlocationid, 
isnull(physical.inventbatchid, financial.inventbatchid ) as inventbatchid, 
isnull(physical.DataAreaId, financial.DataAreaId ) as DataAreaId, 
isnull(PhysicalQty,0) as PhysicalQty,
isnull(FinancialQty,0) as FinancialQty,
isnull(pv,0) as Physicalvalue,
isnull(FinancialValue,0) as FinancialValue,
isnull(Financialadjust,0) as FinancialAdjust

from ( select @StockDate as StockDate,
 trans.itemid,
 inventlocationid,
 inventbatchid,
 trans.InventDimId,
 trans.DataAreaId,
 sum(trans.[QTY]) PhysicalQty , 
 sum(CostAmountPhysical) as pv
 FROM (-- #TransDim1 
 select trans.itemid,
 inventlocationid,
 inventbatchid,
 trans.InventDimId,
 trans.DataAreaId,
 trans.[QTY],
 --costamountposted, s.COSTAMOUNTADJUSTMENT,
 case when trans.datefinancial <= @stockdate 
 then costamountposted + isnull(s.COSTAMOUNTADJUSTMENT ,0)
 else CostAmountPhysical
 end
 as CostAmountPhysical,
 --CostAmountSettled,
 --costamountposted,
 datefinancial
 --,
 --valueOpen
 --case valueopen when 0 then CostAmountSettled else costamountposted end as openvalue
 FROM ERP.[tblInventTrans] trans 
 inner join ERP.tblinventdim dim on 
 trans.DataAreaId = dim.DataAreaId and
 trans.inventdimid = dim.inventdimid
 left join ( 
 select transrecid,SI.itemid, --SI.inventtransid,
 sum(sI.COSTAMOUNTADJUSTMENT) as COSTAMOUNTADJUSTMENT, sI.DataAreaId
 from ERP.tblInventS" StatementType="CREATE/ALTER PROCEDURE" RetrievedFromCache="false" />
</Statements>
<Statements>
<StmtSimple StatementCompId="0" StatementId="2" StatementType="CREATE/ALTER PROCEDURE" RetrievedFromCache="false" />
</Statements>
<Statements>
<StmtSimple StatementCompId="0" StatementId="3" StatementType="CREATE/ALTER PROCEDURE" RetrievedFromCache="false" />
</Statements>
<Statements>
<StmtSimple StatementCompId="0" StatementId="4" StatementType="CREATE/ALTER PROCEDURE" RetrievedFromCache="false" />
</Statements>
<Statements>
<StmtSimple StatementCompId="0" StatementId="5" StatementType="CREATE/ALTER PROCEDURE" RetrievedFromCache="false" />
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

January 13th, 2014 5:12am

Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64)
Oct 25 2013 19:04:40
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Free Windows Admin Tool Kit Click here and download it now

January 13th, 2014 5:19am

Wait stats

SQL Server restarted
WaitType	Wait_S	Resource_S	Signal_S	WaitCount	Percentage	AvgWait_S	AvgRes_S	AvgSig_S
PAGEIOLATCH_EX	178.26	178.06	0.21	19199	30.1	0.0093	0.0093	0
LCK_M_S	113.76	113.67	0.09	31	19.21	3.6696	3.6668	0.0029
IO_COMPLETION	63.21	63.11	0.1	7937	10.67	0.008	0.008	0
PREEMPTIVE_COM_QUERYINTERFACE	58.41	58.41	0	3	9.86	19.4707	19.4707	0
MSQL_DQ	58.41	58.41	0	3	9.86	19.4707	19.4707	0
OLEDB	58.41	58.41	0	3	9.86	19.47	19.47	0
PAGEIOLATCH_SH	15.75	15.74	0.02	1839	2.66	0.0086	0.0086	0
PREEMPTIVE_OS_LOOKUPACCOUNTSID	14.26	14.26	0	21	2.41	0.6792	0.6792	0
LCK_M_X	8.16	8.16	0	40	1.38	0.204	0.2039	0.0001
Working memory setting just after completion
WaitType	Wait_S	Resource_S	Signal_S	WaitCount	Percentage	AvgWait_S	AvgRes_S	AvgSig_S
CXPACKET	6381.06	5052.87	1328.19	4250058	67.44	0.0015	0.0012	0.0003
SOS_SCHEDULER_YIELD	585.19	0.45	584.74	286165	6.18	0.002	0	0.002
IO_COMPLETION	500.2	499.06	1.15	39419	5.29	0.0127	0.0127	0
PREEMPTIVE_OS_AUTHZINITIALIZECONTEXTFROMSID	382.71	382.71	0	20	4.04	19.1356	19.1356	0
PAGEIOLATCH_SH	352.82	352.42	0.4	5683	3.73	0.0621	0.062	0.0001
PAGEIOLATCH_EX	269.39	268.95	0.45	24969	2.85	0.0108	0.0108	0
MSQL_DQ	175.43	175.43	0	7	1.85	25.0616	25.0616	0
PREEMPTIVE_COM_QUERYINTERFACE	175.43	175.43	0	7	1.85	25.0616	25.0616	0
Non working memory setting after 5 min run
WaitType	Wait_S	Resource_S	Signal_S	WaitCount	Percentage	AvgWait_S	AvgRes_S	AvgSig_S
CXPACKET	16553.36	14638.46	1914.89	6264172	73.48	0.0026	0.0023	0.0003
IO_COMPLETION	1159.3	1157.3	2	95235	5.15	0.0122	0.0122	0
PREEMPTIVE_OS_AUTHZINITIALIZECONTEXTFROMSID	909.95	909.95	0	50	4.04	18.199	18.199	0
SOS_SCHEDULER_YIELD	886.93	1.17	885.76	755718	3.94	0.0012	0	0.0012
MSQL_DQ	613.61	613.61	0	22	2.72	27.8914	27.8914	0
PREEMPTIVE_COM_QUERYINTERFACE	613.61	613.61	0	22	2.72	27.8914	27.8914	0
OLEDB	613.59	613.59	0	22	2.72	27.8905	27.8905	0
PAGEIOLATCH_SH	353.05	352.65	0.4	5705	1.57	0.0619	0.0618	0.0001

January 13th, 2014 5:26am

Functional DBCC memory status

Process/System Counts Value
---------------------------------------- --------------------
Available Physical Memory 504172261376
Available Virtual Memory 8218657193984
Available Paging File 544441696256
Working Set 1259106304
Percent of Committed Memory in WS 100
Page Faults 329709
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0

(10 row(s) affected)

Memory Manager KB
---------------------------------------- -----------
VM Reserved 563095792
VM Committed 3370016
Locked Pages Allocated 16767928
Large Pages Allocated 2254848
Emergency Memory 1024
Emergency Memory In Use 16
Target Committed 419840024
Current Committed 20137944
Pages Allocated 11957128
Pages Reserved 14967592
Pages Free 4570624
Pages In Use 24600456
Page Alloc Potential 374247544
NUMA Growth Phase 0
Last OOM Factor 0
Last OS Error 0

Non functional

Process/System Counts Value
---------------------------------------- --------------------
Available Physical Memory 504261206016
Available Virtual Memory 8218673971200
Available Paging File 544530706432
Working Set 1257209856
Percent of Committed Memory in WS 100
Page Faults 328466
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0

(10 row(s) affected)

Memory Manager KB
---------------------------------------- -----------
VM Reserved 563095792
VM Committed 3370016
Locked Pages Allocated 16706496
Large Pages Allocated 2254848
Emergency Memory 1024
Emergency Memory In Use 16
Target Committed 419840024
Current Committed 20076512
Pages Allocated 16459632
Pages Reserved 8350760
Pages Free 9792
Pages In Use 22511896
Page Alloc Potential 376336104
NUMA Growth Phase 0
Last OOM Factor 0
Last OS Error 0

Remaining values for both are scaled accordingly or identical.

Free Windows Admin Tool Kit Click here and download it now

January 13th, 2014 5:40am

This topic is archived. No further replies will be accepted.