Query went from fast to slow

SQL Server Administration
1

Hi. We recently upgraded our ERP and went from using SQL Server 2008 to 2014. In addition, we upgraded our hardware (faster, better everything). Suddenly all of the queries we have embedded in various .NET programs are running much, much slower. The query below went from 2 seconds to 5 minutes and 27 seconds. On top of that, I am a total newb, know very little about this stuff, inherited all of these queries and am now responsible for speeding things up.

Does anybody know what happened and can offer any solutions that might make things as fast as they once were (or at least faster than they've become)? If not, can anybody give me pointers (at the most basic level) on how to rewrite queries like the one below to make it much faster? Many thanks in advance!

Here is the an example query:

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
V.VendorID
,V.Name
,PA.Name as Buyer
,(RD.OurQtyRD.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.FailedQtyRD.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
--,NC.DMRNum
,(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 NonConf NC on NC.PONum = RD.PONum and NC.POLine = RD.POLine and NC.PORelNum = RD.PORelNum and NC.DMRNum > 0 --and RD.LotNum = NC.LotNum
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
2

Before trying to rewrite the queries, make sure that your database is tuned properly. Check if the indexes are fragmented, and if statistics are updated. There are simple scripts available on the web to check. If you need to rebuild the indexes, it can lock up the tables for the duration of the rebuild (unless you are on enterprise edition and use online option).

Once you have checked those off, if the performance is still slow, pick one query that is easy to compare and look at the query plan. The easiest way to look at a query plan is to press control-m (Query -> Include Actual Execution plan from the menu) in SSMS, and run the query. The query plan displayed will give you some indication of what part of the query is taking up the resources. If you have the old server or a dev server, you can repeat the process on that server and compare the two.

1 Like
3

Sounds like a bad execution plan due to out-of-date statistics. I'd start by updating stats, which should be done after an upgrade anyway. Update stats on all of the tables involved in your query. You could do a sampling, say 30%, or a fullscan.

1 Like
4

Critical to update STATS after upgrade

Perhaps too much memory allocated? Important to set MAX Memory for SQL to lower than total server memory - leaving enough for O/S (and also for any other APPs if you have other things running on the same server? Here's hoping nothing else on that box ...)

I think it can help to reboot (or at least stop/start SQL) after:

RESTORE all databases
Rebuild all indexes / statistics

because buffers full of "wrong sort of data" after all the migration work. That said, the cache buffers should sort themselves out in a day or two

How do the Server Settings compare between Old &New server? For example, you might have Sync Stats Create on new server and ASync on old one - that could make a huge difference.

Also possible that SQL has stuffed a lousy Query Plan in the cache, by chance ... clearing the cache or recompiling the SProc which that query is in (if it IS an SProc) would be a more lightweight test!

Actually the MOST lightweight test would be to run that snippet of SQL in SSMS and see how it performs. Perhaps with some modest changes (swap two lines of WHERE clause over) so that it does NOT exactly match any query plan already in the cache)

Pedantic point:

Set @EndDate = '12/31/2014';

is ambiguous and dependant on Locale settings (of both the server and the logged on user), only safe unambiguous format is '20141231' (which works for both DATE and DATETIME datatypes) or '2014-12-31' which only works for DATE