September 1, 2015 at 12:34 pm
Hi. We recently upgraded our ERP system and went from SQL Server 2008 to 2014. We also upgraded our hardware (faster, better everything). Suddenly all of the queries we have embedded in various .NET programs are running much, much slower. For example, I have a query that went from 2 seconds to 5 minutes and 27 seconds. Does anybody have any idea what happened? Anybody have any possible solutions? Many thanks.
September 1, 2015 at 1:55 pm
September 1, 2015 at 2:18 pm
Thanks for replying. I'll take a look at AskBrent. Hopefully that will help.
September 1, 2015 at 2:24 pm
First, use a schema comparison tool between the old and new database to confirm all indexes were migrated over.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 1, 2015 at 8:22 pm
The biggest difference between 2008 & 2014 was the new cardinality estimator. First off, make sure the database compatibility mode is set to 120. If it is, you're using the new estimator. That can cause problems for some queries. You can turn off the estimator by changing the compatibility mode, but you're better off identifying if there are queries that need to be turned off and then applying traceflags. You can read more about it here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 1, 2015 at 10:21 pm
So was it an in-place upgrade or a new installation? It's important to know if the instance-level settings were retained or reset to defaults.
Either way it could be instance level settings like MAXDOP. Is it configured correctly for the new server?
Does the server have the same IP? Sometimes they get moved to another subnet a few hops away introducing additional latency.
The other thing that hits time and again is disk configuration. Yeah it's a brand new server but were the disks formatted to 64kb NTFS blocks? Are the data/log files on separate spindles? What about the underlying RAID configuration?
What are the wait stats for the server? Have you been running sp_WhoIsActive now and again to see what's holding things up? Or possibly even turn on the built-in Management Data Warehouse to start gathering query-level stats, so at least you have somewhere to start.
September 3, 2015 at 9:02 am
Grant, I think you called it. It looks like the new cardinality estimator appears to be causing our queries to run much slower. We changed the compatibility mode to 2012 and that fixed it.
September 3, 2015 at 9:07 am
bwhiteside (9/3/2015)
Grant, I think you called it. It looks like the new cardinality estimator appears to be causing our queries to run much slower. We changed the compatibility mode to 2012 and that fixed it.
Grant, wouldn't updating statistics fix cardinality estimations, or is there a general bug with SQL Server 2014's cardinality estimator?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 3, 2015 at 10:23 am
Eric M Russell (9/3/2015)
bwhiteside (9/3/2015)
Grant, I think you called it. It looks like the new cardinality estimator appears to be causing our queries to run much slower. We changed the compatibility mode to 2012 and that fixed it.Grant, wouldn't updating statistics fix cardinality estimations, or is there a general bug with SQL Server 2014's cardinality estimator?
There's not a bug in the cardinality estimator. It works differently than it used to. It hadn't been updated from SQL Server 7 to SQL Server 2012. The 2014 estimator is the first major revision to it in a long time. The differences, again, not a bug, are in how the cardinality is estimated, not in how the statistics are stored and retrieved. You can still have statistics problems, absolutely, but the estimator is also in there doing different work.
Most of the time, it's better. Just not all the time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 3, 2015 at 10:24 am
bwhiteside (9/3/2015)
Grant, I think you called it. It looks like the new cardinality estimator appears to be causing our queries to run much slower. We changed the compatibility mode to 2012 and that fixed it.
Very high probability that you have query tuning opportunities in those queries. I would strongly suggest prioritizing that and getting the compatibility back to 120. Otherwise you won't be able to take advantage of everything that 2014 has to offer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 3, 2015 at 3:33 pm
I absolutely agree that there are probably opportunities to better tune the queries. Part of the problem is that I am a decent .NET programmer but a total newb when it comes to SQL and so was the guy that wrote all the queries I just inherited, which means he may have written them poorly but I'm not skilled enough yet to know how to improve them. Any chance I can share a query with any of you guys and get some pointers on how to improve it? Here it is if anybody wants to take a crack at it. The subquery takes 5 minutes and 25 seconds while the entire query (oddly enough) takes 5 minutes and 21 seconds.
Declare @BuyerName as nvarchar(50);
Declare @StartDate as date;
Declare @EndDate as date;
Set @BuyerName = '' + '%';
Set @StartDate = '01/01/2014';
Set @EndDate = '12/31/2014';
select
T.VendorID
,T.Name
,COUNT(T.name) as NumofRel
,SUM(T.relvalue) as RelValue
,cast(cast((COUNT(T.name) -SUM(T.NumOfLateDue))as decimal(8,4))/COUNT(T.name) as decimal(4,2)) as OnTimeScoreD
,cast(cast((COUNT(T.name) -SUM(T.NumOfLateProm))as decimal(8,4))/COUNT(T.name) as decimal(4,2)) as OnTimeScoreP
,SUM(T.NumOfLateDue) as NumofLateDue
,SUM(T.NumOfLateProm) as NumofLateProm
,AVG(T.DaysLateDue) as AvgDaysLateDue
,AVG(T.DaysLateProm) as AvgDaysLateProm
,cast(cast((COUNT(T.name) -SUM(T.InspectionFailed))as decimal(8,4))/COUNT(T.name) as decimal(4,2)) as QualityScore
,sum(T.InspectionFailed) as NumOfRejects
,SUM(T.RejectValue) as RejectValue
from (
select
V.VendorID
,V.Name
,PA.Name as Buyer
,(RD.OurQty*RD.OurUnitCost) as RelValue
,(select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(CASE WHEN dbo.AGM_isdatenull(POR.PromiseDt) = 1 THEN POR.DueDate ELSE POR.PromiseDt END as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1) as DaysLateProm
,(select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(POR.DueDate as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1) as DaysLateDue
,(CASE WHEN (select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(CASE WHEN dbo.AGM_isdatenull(POR.PromiseDt) = 1 THEN POR.DueDate ELSE POR.PromiseDt END as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1)>2 THEN 1 ELSE 0 END )as NumOfLateProm
,(CASE WHEN (select COUNT (*) from agmdb.dbo.agm_calendar AC where AC.dt>=(cast(POR.DueDate as DateTime)) and AC.dt< RD.receiptdate and AC.isworkday = 1)>2 THEN 1 ELSE 0 END ) as NumOfLateDue
,(RD.FailedQty*RD.OurUnitCost*(CASE WHEN isnull(PT.DMRNum,99999)<>99999 and PT.dmrnum <> 0 and isnull(UD14.CheckBox19,0) <> 1 THEN 1 ELSE 0 END)) as RejectValue
,(CASE WHEN isnull(PT.DMRNum,99999)<>99999 and PT.dmrnum <> 0 and isnull(UD14.CheckBox19,0) <> 1 THEN 1 ELSE 0 END )AS InspectionFailed
from
RcvDtl RD
join PORel POR on RD.PONum = POR.PONum and RD.POLine = POR.POLine and RD.PORelNum = POR.PORelNum
join Vendor V on RD.VendorNum = V.VendorNum
join POHeader POH on POR.PONum = POH.PONum
join Erp.PurAgent PA on PA.BuyerID = POH.BuyerID
left outer join PartTran PT on PT.PONum = RD.PONum and PT.POLine = RD.POLine and PT.PORelNum = RD.PORelNum and PT.PackSlip = RD.PackSlip and PT.PackLine = RD.PackLine and PT.TranType = 'INS-DMR'
left outer join Ice.UD14 on UD14.Key1 = PT.DMRNum and UD14.Key5 = '1'
where dbo.AGM_isdatenull(RD.ReceiptDate) = 0 and dbo.AGM_isdatenull(POR.DueDate) = 0 and cast(RD.ReceiptDate as date) <> cast( GETDATE() as date)
and RD.InspectionPending = 0 and RD.ReceiptDate >= @StartDate and RD.ReceiptDate <= @EndDate
and 1 = CASE WHEN @BuyerName = '%' THEN 1 ELSE CASE WHEN PA.name like @buyername THEN 1 ELSE 0 END END
) as T
group by T.VendorID, T.Name
order by T.VendorID
September 4, 2015 at 3:55 am
Experts might tell you to look at the actual execution plan to see where the costly parts of the query are, but it might just confuse you as a beginner with a complex query. You could try starting with just the subquery, and simply commenting out different parts of it (such as the working day count subqueries in the SELECT, then parts of the WHERE clause, then some of the table joins) until it suddenly gets much faster, to find which part is causing the slowness. I only glanced through your query but it looks like dbo.AGM_isdatenull() is some kind of scalar function that has to be calculated for each row, which might be very slow if the function does much, so see what the definition of that function is (presumably it does more than ISNULL()), and whether that part of the WHERE clause could be done a different way. Also make sure that the fields being used for joining or filtering are indexed.
September 4, 2015 at 5:21 am
There's a lot in there that needs addressing. The biggest issue is all the places where there are functions on the columns like this:
AC.dt>=(cast(CASE WHEN dbo.AGM_isdatenull(POR.PromiseDt) = 1 THEN POR.DueDate ELSE POR.PromiseDt END as DateTime))
That's going to lead to scans against the tables. Indexes just won't get used properly. That's just one example. I saw several other functions. Woof. That's going to take some work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 4, 2015 at 5:44 am
Maybe if the function is only there to cope with null dates being stored as an origin point like 1st January 1900 or 1st January 1753, then he could replace stuff like this:
WHERE dbo.AGM_isdatenull(RD.ReceiptDate)= 0
with stuff like this:
WHERE (RD.ReceiptDate IS NOT NULL AND RD.ReceiptDate NOT IN ('17530101','19000101'))
or alternatively fix the existing data and whatever loads new data, so that nulls really get stored as nulls, and then just use WHERE RD.ReceiptDate IS NOT NULL
September 4, 2015 at 10:28 am
Hi guys,
Thanks for your insights. I wondered about AGM_IsDateNull as well, so I looked at it and as far as I can tell it just checks if the value is NULL. Here is the function:
ALTER function [dbo].[AGM_IsDateNull](@dat datetime)
returns tinyint
as
begin
declare @result tinyint
set @result = 0
if @dat is null set @result = 1
return (@result)
end
So instead of...
where dbo.AGM_isdatenull(RD.ReceiptDate) = 0
I changed it to...
RD.ReceiptDate is not null
On top of that, I read somewhere that you should try to put limiting conditionals in the joins rather than the where clause when possible, so I restructured that as well and I got the query to run in zero seconds. I took the results from both queries, put them each in their own Excel worksheet, then compared the worksheets and they were exactly the same. Its amazing that both queries (zero seconds vs. 5 minutes & 22 seconds) return the exact same dataset, but one takes sooooo much longer. Just goes to show how important it is to write your queries properly.
Thank you both for your help! It is greatly appreciated!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply