Severe performance slowdown with SP4
-
Hello.
I'm having performance problems with several SPs a programmer made.
In his stressed laptop one of those SPs takes about 30 secs to run and in my server took about 1:15. This time added to the other SPs makes the program unusable.
Digging about it, we seen he had SP2 and I had SP4. When he installed SP4 on his laptop the same SP took about 8 minutes to run!!
I've been looking for SP4 performance issues, but this is the only thing I've found, and is unavailable to public http://support.microsoft.com/kb/826906/
Any clue about where should I look?
PS. His SP uses cursors. I'm working on getting rid of them, but at least I want the pre-SP4 performance (without uninstalling it).
-
Do you guys have extacly the same databses on both servers?
Did you try dropping the executions plans and rerunning.
Does it do that on every execution or is is bad only on the first run?
Can you show us the sp code... maybe we can convert the cursor for you right away.
-
Thanks for your input
Not exactly the same DB, but something close. He has the full data amout, but I have views and he uses them converted to tables. However, the data amoud is still tiny (a few hundreds of records for the larger table). However, the weird thing is that after SP4 his machine worked fine.
About the execution plan, I ran sp_updatestats (or, how I drop the exec plans?) with the same execution time. And the execution time is consistent every time I run it.
About posting the code, I'm afraid I can't. Not the exactly the holy-cow-super-code, but as the source code hasn't been made by my and the programmer is from an external company I prefer to keep it local.
-
DBCC FREEPROCCACHE
Will drop all execution plans.
If you can't post the code, can you show us the execution plans??? Maybe we'll be able to spot something obvious!
-
It's not good to try to compare databases ( especially not the same ) running on different operating systems or database versions. That sp4 gave a problem fine - that's interesting, I'd suggest you apply the rollup 2187 as sp4 introduced a few problems.
The first step always is to compare the execution plans, data volumes also need to be the same, as do indexes, stats, processors ( threads/cores ) otherwise any comparision is somewhat flawed.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Hi Colin
I didn't menctioned it, but already tried installing 2187 with no changes 🙁
I guess the focus is not comparing dbs (the time compared in his db against mine), but the huge time increase caused by SP4 installation in his machine and at this point is when I compare with the absurd times in my server.
btw, the server is a dual 3Ghz box with 4GB in RAM.
-
Hi Ninja!
Dropped the execution plans, no significant change (about 1 second)
The exec plans are these (wow this thing generated a big one, not sure if are going to be helpful):
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |--Snapshot Cursor(Categorias, Read Only) |--Population Query(Synch... |--Stream Aggregate(GROUP BY: ([tblDT_ProductType].[strProductType], [tblDT_FacilityProductsUsed].[numProductTypeId])) |--Nested Loops(Inner Join, WHERE: ([tblDT_ProductType].[numProductTypeId]=[tblDT_FacilityProductsUsed].[numProductTypeId])) |--Sort(ORDER BY: ([tblDT_ProductType].[strProductType] ASC, [tblDT_ProductType].[numProductTypeId] ASC)) | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_ProductType])) |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityProductsUsed]), WHERE: ([tblDT_FacilityProductsUsed].[numFacilityId]=Convert([@Param1007]))) ------------------------------------------------ |--Dynamic Cursor(Producs, Optimistic) |--Fetch Query(Fetc... | |--Table Scan(OBJECT: (@Prods)) |--Refresh Query(Refres... ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |--Table Insert(OBJECT: (@Prods), SET: (@Prods.[ClaveVta]=[Expr1007], @Prods.[ClaveTam]=[Expr1008], @Prods.[ClaveCliente]=[Expr1009], @Prods.[ClaveManuf]=[Expr1010])) |--Top(ROWCOUNT est 0) |--Compute Scalar(DEFINE: ([Expr1007]=Convert([tblDT_FacilityProductsUsed].[strPSalesCode]), [Expr1008]=Convert([tblDT_FacilityProductsUsed].[strPSalesSize]), [Expr1009]=Convert([tblDT_FacilityProductsUsed]. |--Nested Loops(Inner Join) |--Nested Loops(Left Outer Join, OUTER REFERENCES: ([tblDT_FacilityProductsUsed].[numFacilityProductId])) | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityProductsUsed]), WHERE: ([tblDT_FacilityProductsUsed].[numProductTypeId]=Convert([@TipoProd]) AND [tblDT_FacilityProductsUsed].[numFacilit | |--Hash Match(Cache, HASH: ([tblDT_FacilityProductsUsed].[numFacilityProductId]), RESIDUAL: ([tblDT_FacilityProductsUsed].[numFacilityProductId]=[tblDT_FacilityProductsUsed].[numFacilityProductId]) | |--Stream Aggregate(DEFINE: ([Expr1002]=MAX([tblDT_FacilityProductsData].[strPMfgCode]))) | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityProductsData]), WHERE: (([tblDT_FacilityProductsUsed].[numFacilityProductId]=[tblDT_FacilityProductsData].[numFacilityProductId |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_ProductType]), WHERE: ([tblDT_ProductType].[numProductTypeId]=Convert([@TipoProd]))) ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |--Table Insert(OBJECT: ([Cover].[dbo].[tbl_TmpLotes]), SET: ([tbl_TmpLotes].[FechaEnt]=[Union1074], [tbl_TmpLotes].[Lote]=[Union1072], [tbl_TmpLotes].[Tipo]=[Union1073], [tbl_TmpLotes].[ClaveVta]=[Union1075], [tbl_TmpLotes].[ClaveTam]= ASC, [Union1073] ASC, [Union1074] ASC, [Union1075] ASC, [Union1076] ASC, [Union1077] ASC, [Union1078] ASC, [Union1079] ASC)) |--Concatenation |--Compute Scalar(DEFINE: ([Expr1015]=Convert([tblInventarioDetalle].[lote]), [ConstExpr1087]=Convert([@CveVenta]), [ConstExpr1088]=Convert([@CveTamano]), [ConstExpr1089]=Convert([@CveCliente]), [ConstExpr1090]=Convert([@CveM | |--Compute Scalar(DEFINE: ([Expr1085]=getdate())) | |--Nested Loops(Inner Join) | |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES: ([tblInventarioDetalle].[lote])) | | |--Clustered Index Seek(OBJECT: ([W].[dbo].[tblInventarioDetalle].[PK_tblInventarioDetalle]), SEEK: ([tblInventarioDetalle].[codigo]=[@CveVenta] AND [tblInventarioDetalle].[tamano]=[@CveTamano]), WHERE: | | |--Row Count Spool | | |--Nested Loops(Inner Join, WHERE: ([tblDT_FacilityInvTrans].[numFacilityProductId]=Convert([tblDT_FacilityProductsUsed].[numFacilityProductId]))) | | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityInvTrans]), WHERE: ([tblInventarioDetalle].[lote]=[tblDT_FacilityInvTrans].[strLotNo])) | | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityProductsUsed])) | |--Row Count Spool | |--Filter(WHERE: ([Expr1007]=[@CveManufac])) | |--Stream Aggregate(GROUP BY: ([Rank1110]) DEFINE: ([Expr1007]=MAX([tblDT_FacilityProductsData].[strPMfgCode]))) | |--Nested Loops(Inner Join, WHERE: ([tblDT_FacilityProductsUsed].[numFacilityProductId]=[tblDT_FacilityProductsData].[numFacilityProductId])) | |--Rank | | |--Nested Loops(Inner Join) | | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_RptCurrentDailyForecast_ByProduct]), WHERE: (((([tblDT_RptCurrentDailyForecast_ByProduct].[numFacilityId]=Convert | | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityProductsUsed]), WHERE: ((([tblDT_FacilityProductsUsed].[numFacilityId]=Convert([@IDCliente]) AND [tblDema | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityProductsData]), WHERE: (Convert([tblDT_FacilityProductsData].[strPMfgLocation])=5569 AND Convert([tblDT_F |--Compute Scalar(DEFINE: ([Expr1094]=getdate(), [ConstExpr1096]=Convert([@CveVenta]), [ConstExpr1097]=Convert([@CveTamano]), [ConstExpr1098]=Convert([@CveCliente]), [ConstExpr1099]=Convert([@CveManufac]))) | |--Nested Loops(Inner Join) | |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES: ([tblDT_FacilityInvTrans].[strLotNo])) | | |--Nested Loops(Inner Join, WHERE: ([tblDT_FacilityInvTrans].[numFacilityProductId]=Convert([tblDT_FacilityProductsUsed].[numFacilityProductId]))) | | | |--Sort(ORDER BY: ([tblDT_FacilityInvTrans].[strLotNo] ASC)) | | | | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityInvTrans]), WHERE: (len([tblDT_FacilityInvTrans].[strLotNo])<8)) | | | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityProductsUsed]), WHERE: (([tblDT_FacilityProductsUsed].[numFacilityId]=Convert([@IDCliente]) AND [tblDT_FacilityProductsUse | | |--Row Count Spool | | |--Top(1) | | |--Clustered Index Scan(OBJECT: ([W].[dbo].[tblInventarioDetalle].[PK_tblInventarioDetalle]), WHERE: ([tblDT_FacilityInvTrans].[strLotNo]=[tblInventarioDetalle].[lote])) | |--Row Count Spool | |--Filter(WHERE: ([Expr1029]=[@CveManufac])) | |--Stream Aggregate(GROUP BY: ([Rank1114]) DEFINE: ([Expr1029]=MAX([tblDT_FacilityProductsData].[strPMfgCode]))) | |--Nested Loops(Inner Join, WHERE: ([tblDT_FacilityProductsUsed].[numFacilityProductId]=[tblDT_FacilityProductsData].[numFacilityProductId])) | |--Rank | | |--Nested Loops(Inner Join) | | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_RptCurrentDailyForecast_ByProduct]), WHERE: (((([tblDT_RptCurrentDailyForecast_ByProduct].[numFacilityId]=Convert([@ID | | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityProductsUsed]), WHERE: ((([tblDT_FacilityProductsUsed].[numFacilityId]=Convert([@IDCliente]) AND [tblDemandToo | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityProductsData]), WHERE: (Convert([tblDT_FacilityProductsData].[strPMfgLocation])=5569 AND Convert([tblDT_Facili |--Compute Scalar(DEFINE: ([Expr1065]=Convert([tblInventarioDetalle].[lote]), [Expr1103]=getdate(), [ConstExpr1105]=Convert([@CveVenta]), [ConstExpr1106]=Convert([@CveTamano]), [ConstExpr1107]=Convert([@CveCliente]), [ConstEx |--Nested Loops(Inner Join) |--Nested Loops(Inner Join) | |--Filter(WHERE: ([Expr1058]=[@CveManufac])) | | |--Stream Aggregate(GROUP BY: ([Rank1119]) DEFINE: ([Expr1058]=MAX([tblDT_FacilityProductsData].[strPMfgCode]))) | | |--Nested Loops(Inner Join, WHERE: ([tblDT_FacilityProductsUsed].[numFacilityProductId]=[tblDT_FacilityProductsData].[numFacilityProductId])) | | |--Rank | | | |--Nested Loops(Inner Join) | | | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_RptCurrentDailyForecast_ByProduct]), WHERE: (((([tblDT_RptCurrentDailyForecast_ByProduct].[numFacilityId]=Convert([@ID | | | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityProductsUsed]), WHERE: ((([tblDT_FacilityProductsUsed].[numFacilityId]=Convert([@IDCliente]) AND [tblDemandToo | | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityProductsData]), WHERE: (Convert([tblDT_FacilityProductsData].[strPMfgLocation])=5569 AND Convert([tblDT_Facili | |--Row Count Spool | |--Hash Match(Inner Join, HASH: ([Expr1122])=([tblDT_FacilityInvTrans].[numFacilityProductId]), RESIDUAL: ([tblDT_FacilityInvTrans].[numFacilityProductId]=[Expr1122])) | |--Compute Scalar(DEFINE: ([Expr1122]=Convert([tblDT_FacilityProductsUsed].[numFacilityProductId]))) | | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityProductsUsed]), WHERE: ([tblDT_FacilityProductsUsed].[strPSalesCode]=[@CveVenta] AND [tblDT_FacilityProductsUs | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityInvTrans])) |--Clustered Index Seek(OBJECT: ([W].[dbo].[tblInventarioDetalle].[PK_tblInventarioDetalle]), SEEK: ([tblInventarioDetalle].[codigo]=[@CveVenta] AND [tblInventarioDetalle].[tamano]=[@CveTamano]), WHERE: (len([tblI ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |--Table Insert(OBJECT: ([Cover].[dbo].[tbl_TmpLotes]), SET: ([tbl_TmpLotes].[FechaEnt]=[tblPSD].[dtmDueDate], [tbl_TmpLotes].[Lote]=[Expr1009], [tbl_TmpLotes].[Tipo]=[Expr1010], [tbl_TmpLotes].[ClaveVta]=Convert([@CveV |--Top(ROWCOUNT est 0) |--Compute Scalar(DEFINE: ([Expr1009]=Convert([tblPSD].[strTkt#OrPlan#]), [ConstExpr1020]=Convert([@CveVenta]), [ConstExpr1021]=Convert([@CveTamano]), [ConstExpr1022]=Convert([@CveCliente]), [ConstExpr1023]=Convert([ |--Sort(DISTINCT ORDER BY: ([tblPSD].[dtmDueDate] ASC, [tblPSD].[strTkt#OrPlan#] ASC)) |--Hash Match(Inner Join, HASH: ([Expr1031])=([Expr1032]), RESIDUAL: ([Expr1032]=[Expr1031])) |--Compute Scalar(DEFINE: ([Expr1031]=Convert([Expr1002]))) | |--Filter(WHERE: ([Expr1002]=[@CveManufac])) | |--Stream Aggregate(GROUP BY: ([Rank1029]) DEFINE: ([Expr1002]=MAX([tblDT_FacilityProductsData].[strPMfgCode]))) | |--Nested Loops(Inner Join, WHERE: ([tblDT_FacilityProductsUsed].[numFacilityProductId]=[tblDT_FacilityProductsData].[numFacilityProductId])) | |--Rank | | |--Nested Loops(Inner Join) | | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_RptCurrentDailyForecast_ByProduct]), WHERE: (((([tblDT_RptCurrentDailyForecast_ByProduct].[numFacilityId]=Convert([@IDClien | | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityProductsUsed]), WHERE: ((([tblDT_FacilityProductsUsed].[numFacilityId]=Convert([@IDCliente]) AND [tblDT_Fac | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityProductsData]), WHERE: (Convert([tblDT_FacilityProductsData].[strPMfgLocation])=5569 AND Convert([tblDT_FacilityPro |--Compute Scalar(DEFINE: ([Expr1032]=If (substring([tblPSD].[strMfgCode], 1, 1)='#' OR substring([tblPSD].[strMfgCode], 1, 1)='$') then [tblPSD].[strPrimeCode] el |--Table Scan(OBJECT: ([Inddw].[dbo].[tblPSD]), WHERE: ([tblPSD].[ysnWIP]=-1 AND [tblPSD].[strTkt#OrPlan#]NULL)) ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |--Table Insert(OBJECT: ([Cover].[dbo].[tbl_TmpLotes]), SET: ([tbl_TmpLotes].[FechaEnt]=[tblPSD].[dtmDueDate], [tbl_TmpLotes].[Lote]=[Expr1009], [tbl_TmpLotes].[Tipo]=[Expr1010], [tbl_TmpLotes].[ClaveVta]=Convert([@CveV |--Top(ROWCOUNT est 0) |--Compute Scalar(DEFINE: ([Expr1009]=Convert([tblPSD].[strTkt#OrPlan#]), [ConstExpr1019]=Convert([@CveVenta]), [ConstExpr1020]=Convert([@CveTamano]), [ConstExpr1021]=Convert([@CveCliente]), [ConstExpr1022]=Convert([ |--Sort(DISTINCT ORDER BY: ([tblPSD].[dtmDueDate] ASC, [tblPSD].[strTkt#OrPlan#] ASC)) |--Hash Match(Inner Join, HASH: ([Expr1032])=([Expr1031]), RESIDUAL: ([Expr1031]=[Expr1032])) |--Compute Scalar(DEFINE: ([Expr1032]=Convert([Expr1002]))) | |--Filter(WHERE: ([Expr1002]=[@CveManufac])) | |--Stream Aggregate(GROUP BY: ([Rank1028]) DEFINE: ([Expr1002]=MAX([tblDT_FacilityProductsData].[strPMfgCode]))) | |--Nested Loops(Inner Join, WHERE: ([tblDT_FacilityProductsUsed].[numFacilityProductId]=[tblDT_FacilityProductsData].[numFacilityProductId])) | |--Rank | | |--Nested Loops(Inner Join) | | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_RptCurrentDailyForecast_ByProduct]), WHERE: (((([tblDT_RptCurrentDailyForecast_ByProduct].[numFacilityId]=104 AND [tblDeman | | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityProductsUsed]), WHERE: ((([tblDT_FacilityProductsUsed].[numFacilityId]=104 AND [tblDT_FacilityProductsUsed] | |--Table Scan(OBJECT: ([Cover].[dbo].[tblDT_FacilityProductsData]), WHERE: (Convert([tblDT_FacilityProductsData].[strPMfgLocation])=5569 AND Convert([tblDT_FacilityPro |--Compute Scalar(DEFINE: ([Expr1031]=If (substring([tblPSD].[strMfgCode], 1, 1)='#' OR substring([tblPSD].[strMfgCode], 1, 1)='$') then [tblPSD].[strPrimeCode] el |--Table Scan(OBJECT: ([Inddw].[dbo].[tblPSD]), WHERE: ([tblPSD].[ysnWIP]=0 AND [tblPSD].[strTkt#OrPlan#]NULL)) -------------------------------------------------------------------- |--Table Scan(OBJECT: ([Cover].[dbo].[tbl_TmpLotes])) -
Well with a quick scan I can spot a LOT of table scans which is really not a good thing (most of the time).
I think I'll suggest you search this site for performance tuning articles and how to remove cursors. Since the execution plans without the code is just a tad useless.
-
agreed - tuning is a bit of a black art - I've just tuned a query on a dev box ( as one might expect ) and when deployed to prod ( new indexes ) the query choose a totally different index and plan. Even with a join hint it still choose a different index! The database's wre identical as were o/s and sql version , 2 procs vs 8 procs and no awe in dev. Found the problem but it's a bit of a black art.
Kalen Delaney said that ms do not consider they have to document or make reference to any optimiser changes they make in sp's, it's quite possible this is your problem - the optimiser got an entire rewrite for 2005 so queries may and do run totally different again , and I can personally vouch for this difference across sql versions, have seen it on editions too but was not able to find a fix at that time.
Cursors are bad !
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
My guess is too that cursors are really bad, and responsibles of the slowness in this case. I'll keep in removing them and let you know in case of something weird occurs.
Thanks
-
Your guess will be correct 99.9% of the time
. -
Also a great change from SP3 to SP4 is that the datatypes have to match in order to use an index.
*found the link
-
Hmmm .. I'm not too convinced on that as it's only certain data conversions that will work on the fly .. do you have a test example ?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Hi,
How about using Index tunning wizard which will suggest right indexes for the SP.
Minaz Amin
"More Green More Oxygen !! Plant a tree today"
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply