January 12, 2007 at 1:22 pm
This is a continuation of a problem I posted last July. Unfortunately we still cannot recreate performance problems we are experiencing.
Summary:
- Vendor application
- Slow performance corresponds to a large number of logical reads, 2 million - 100+ million
- Some fragmentation found, but running DBCC INDEXDEFRAG during the day doesn't help
- Can't reproduce problem when restoring DB to a test server
- Vendor uses tempdb to store "permanent" temp tables. The tables are recreated when SQL restarts. Not sure why they do this.
- CPUs looks good. Memory looks good.
- I see a decent amount of CXPACKET waittype, but not sure what amount is considered bad. Perhaps CXPACKET indicates a parallel query issue and I need to adjust "minimum query plan threshold for considering queries for parallel execution". I'm no expert with parallel queries and CXPACKET so this is only a guess based upon Google searches.
I already run sp_updatestats each night, should this also be run during the day. Also, what do you recommend regarding running the following commands when the system is in use?
dbcc freeproccache
sp_refreshview
EXEC sp_msForEachTable 'EXEC sp_recompile ''?'''
Thanks, Dave
January 12, 2007 at 1:30 pm
If you are suspecious about parallalism you can control it using MAXDOP option in your query but you can't change the third party application code...So you can change configuration on servers side to use single processor instead of all processors and I don't think it requires SQL to restart to take effect...
Simply you can change and test it on production..if you don't see any improvements you can change it back what it was...
MohammedU
Microsoft SQL Server MVP
January 15, 2007 at 3:46 am
index defrag is not the same as rebuilding indexes. cxpacket waits are always bad news - generally what happens is that the cost of the query evokes a parallel plan - this is good except when the high cost is die to a poor query ( missing indexes for example or lots of scans ) in certain circumstances the parallel threads casue blocking ( the cxpacket waits ) to each other.
Most advice you'll get is to turn off parallel queries ( generally a really bad idea ) whereas tuning the queries is the best move. You need to identify the queries causing the problem and ideally identify if there are missing indexes.
NTFS and internal database fragmentation can contribute to this but generally it's poor queries doing table scans.
I figure it's unlikely to be stats, ideally if you can, update stats every night and maybe try an index rebuild rather than a defrag - don't forget to do a dbcc updateusage - but I doubt it'll make much difference.
clearing the cache is unlikely to make much difference, update stats and index rebuilds will prompt a plan recompile anyway if there has been enough change. How many procs do you have on your box ? I did some testing on a 4 way dual core ( amd ) on poor queries and a maxdop setting of 4 was about optimal with 1 and 8 giving the worst performance. In my case I had to disable parallelism for the whole server as 75% of the queries produce table scans through using leading wildcards in searches.
That level of io is pretty high btw.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 15, 2007 at 10:41 am
I understand IndexDefrag is not the same as DBReindex, but at least it reorganizes the leaf pages. Better then nothing I guess.
On one test I restored the production DB to our test server and then dropped all indexes in hopes this was an index issue. I saw a slight increase in the number of logical reads, but nothing approaching what we see in production. The response time was also not as poor as in production. It's generally the same stored procedures that cause the performance problems and in some cases I can see an open cursor as the last command executed. Yes, the dreaded cursor. I've passed the information along to the vendor, but since they can't reproduce the problem they won't consider a code re-write. In regards to parallel processing if I receive a call indicating someone's process has been running for a long time and I then turn off parallel processing, would this impact the process that is currently running or would it continue to process in parallel? I'm guessing it would continue.
Thanks, Dave
January 15, 2007 at 11:01 am
It sounds to me like maybe the query/sp is using the wrong execution plan. Is it a single sp that is always as the basis of the problem. I generally will see a large number of logical reads in queries where loop joins are used. If several tables in the queries have a large number of rows and table scans with loop joins are being used than the performance is usually bad. I will try to hint out hash and merge joins on those queries to see if performance improves. I also will build covering indexes to make sure I'm not doing table scans and bookmark lookups. Could you post the tables schema, #of rows, ,indexes and the offending sp, so we can take a look at it.
Thanks
Tom
January 15, 2007 at 3:52 pm
yup wrong proc plans can be a pain - try adding a with recompile to the proc and see if the problem goes away ( as a test - I don't normally recommend using with recompile ) Of course it might be recompiling anyway - run a trace and see.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 16, 2007 at 8:54 am
The big delays are typically within one stored procedure, but that procedure invokes several other procs. In regards to joins I thought that was a possibility, but I don't understand why I cannot reproduce the problem on our test server, especially after I dropped all indexes forcing SQL to perform table scans.
Dave
January 16, 2007 at 11:44 am
The other server could be using a different plan. We'd really have to see the execution plans to get anymore information. You can still do table scans and have it use any type of join (loop, hash, merge) which will, by far, have the impact that you are seeing with the logical reads (reads in memory). Have you looked at them to see if they are the same? Also, can you run the stored procs individually to see if one is slower than others. The execution plan will help you by ranking the statements that were executed, and give you a rough idea of where to look. I also will turn statistics io on, and statistic time on to see what tables I am spending the most time and resources on. Once again some schema/statements would help us help you.
Tom
January 16, 2007 at 1:16 pm
I looked at an old trace and found a couple of procedures causing problems on that specific day. One procedure executed the following update statement.
This one contains a lot of joins.
-- pMxGetGainInfo
update @portGainTable
set LongTargetYTDRGain = p.LongTargetYTDRGain + tl.LongTargetYTDRGainAdjust
from @portGainTable p
join (
select a.portId,
sum(dbo.fMxGetLongGain(
dbo.fMxGetSysTotalGain(d.UnallocCloseQuantity*tt.signOfQty, a.TranCode,
dbo.fMxGetShareValueNoAI(a.targetPrice, s.valuationFactor, o.paydownFactor),
l.isZeroMV,
l.UnitCost,a.TdLocalFxRate,
0,st.rGain),
st.rGain, dbo.fMxIsHeldLong(l.heldLongDate, o.OrderDate)))
[LongTargetYTDRGainAdjust]
from @portTable p
join dbo.MoxyPreAllocation a on a.portId=p.portId
join dbo.MoxyTranType tt on tt.tranCode = a.tranCode
join dbo.MoxyOrders o on o.orderId=a.orderId
join dbo.vMoxyPreAllocLotDetail d on d.orderId=a.orderId and d.portId=a.portId
join dbo.MoxyTaxLot l on l.taxLotId=d.taxLotId
join dbo.MoxySecMaster s on s.secType=a.secType and s.symbol=a.symbol
join dbo.MoxySecType st on st.secType=a.secType
where a.orderId<>@excludeOrderId
and (a.tranCode=1 or a.tranCode=3) -- closing tran: SELL or COVER
and a.adjPosition<>0
and o.orderDate>=p.FiscalYearStart and o.orderDate<p.NextYearStart
group by a.portId) tl on tl.portId=p.portId
where p.hasTargetAdjust<>0
Another procedure sat on an open cursor.
DECLARE CsrPreAllocation CURSOR LOCAL FOR
SELECT TP.PortID, TP.Quantity, TP.ShareValue, TP.ValueBasis, TP.CashValueBasis,
TP.SettleCurrency, TP.TdLocalFxRate, TP.TdSettleFxRate,
P.Symbol, P.Quantity, P.AllocQty, P.ShareValue, P.ValueBasis, P.CashValueBasis,
P.SettleCurrency, P.TdLocalFxRate, P.TdSettleFxRate, P.AdjPosition
FROM tempdb.dbo.MoxyUskPreAllocation TP, dbo.MoxyPreAllocation P
WHERE TP.OrderID = @OrderID AND TP.OrderID = P.OrderID AND TP.PortID = P.PortID AND TP.UseKey = @PreAllocUseKey
AND (@IsDelta = 0 OR TP.Upd = 1)
AND (TP.Symbol <> P.Symbol
OR TP.Quantity <> P.Quantity
OR TP.SettleCurrency <> P.SettleCurrency
OR TP.TdLocalFxRate <> P.TdLocalFxRate
OR TP.TdSettleFxRate <> P.TdSettleFxRate
OR TP.ShareValue <> P.ShareValue
OR @AdjPosition <> P.AdjPosition)
UNION
SELECT TP.PortID, TP.Quantity, TP.ShareValue, TP.ValueBasis, TP.CashValueBasis,
TP.SettleCurrency, TP.TdLocalFxRate, TP.TdSettleFxRate,
P.Symbol, P.Quantity, P.AllocQty, P.ShareValue, P.ValueBasis, P.CashValueBasis,
P.SettleCurrency, P.TdLocalFxRate, P.TdSettleFxRate, P.AdjPosition
FROM tempdb.dbo.MoxyUskPreAllocation TP, dbo.MoxyPreAllocation P
WHERE TP.OrderID = @OrderID AND TP.OrderID = P.OrderID AND TP.PortID = P.PortID AND TP.UseKey = @PreAllocUseKey
AND (@IsDelta = 0 OR TP.Upd = 1)
AND EXISTS (SELECT 1 FROM tempdb.dbo.MoxyUskPreallocLotDetail TL
WHERE TL.UseKey = @TaxLotUseKey
AND P.OrderID = TL.OrderID AND P.PortID = TL.PortID
AND NOT EXISTS (SELECT 1 FROM dbo.MoxyPreAllocLotDetail D
WHERE D.OrderID = TL.OrderID AND D.PortID = TL.PortID
AND D.TaxLotID = TL.TaxLotID AND D.CloseQuantity = TL.CloseQuantity))
Microsoft just sent me a PSSDIAG script to run the next time someone is experiencing poor performance. I'm hoping it will help locate the problem.
Thanks, Dave
January 16, 2007 at 1:42 pm
I am assuming there is not an index on the table in the tempdb db or on the table variable. One thing I have noticed is that table variables and temp tables performance is different. I would try to create a # temp table instead of the table variable in the first procedure. I would also create an index on the portid, fiscalyearstart,nexyearstart fields. I would also try putting a hint after the last where clause with the various join options. eg. option(hash join) and see what that does with the plan. I am assuming your doing all loop joins with that many joined table and such bad performance. When was the last time you updated the statistics on the tables. How many rows are we talking about in these tables/temp tables. Just trying to give you some spots to try. I don't think the pssdiag script will tell you much. With the second script being a union and so many ors your clearly doing a table scan. I would try to take the two statements and seperate them and dump there output into a temp table, then use that to run a cursor off of. Unions can sometimes slow that stuff down.
Tom
January 16, 2007 at 1:45 pm
I forgot to add that I would turn the update into a select so I could tune that statement. At least to run the estimated execution plan.
-- pMxGetGainInfo
--update @portGainTable
-- set LongTargetYTDRGain = p.LongTargetYTDRGain + tl.LongTargetYTDRGainAdjust
select *
from @portGainTable p
join ( select a.portId,
sum(dbo.fMxGetLongGain
( dbo.fMxGetSysTotalGain
(d.UnallocCloseQuantity*tt.signOfQty, a.TranCode, dbo.fMxGetShareValueNoAI
(a.targetPrice, s.valuationFactor, o.paydownFactor
), l.isZeroMV, l.UnitCost,a.TdLocalFxRate, 0,st.rGain
), st.rGain, dbo.fMxIsHeldLong
(l.heldLongDate, o.OrderDate
)
)
) [LongTargetYTDRGainAdjust]
from @portTable p
join dbo.MoxyPreAllocation a
on a.portId=p.portId
join dbo.MoxyTranType tt
on tt.tranCode = a.tranCode
join dbo.MoxyOrders o
on o.orderId=a.orderId
join dbo.vMoxyPreAllocLotDetail d
on d.orderId=a.orderId and d.portId=a.portId
join dbo.MoxyTaxLot l
on l.taxLotId=d.taxLotId
join dbo.MoxySecMaster s
on s.secType=a.secType and s.symbol=a.symbol
join dbo.MoxySecType st
on st.secType=a.secType
where a.orderId<>@excludeOrderId
and (a.tranCode=1 or a.tranCode=3) -- closing tran: SELL or COVER
and a.adjPosition<>0
and o.orderDate>=p.FiscalYearStart
and o.orderDate<p.NextYearStart
group by a.portId) tl on tl.portId=p.portId
where p.hasTargetAdjust<>0
Tom
January 16, 2007 at 2:40 pm
Hi Tom,
Keep in mind this is the vendor's code so there is nothing I can do to the code. The only way they would change the code is if I proved to them it was causing a problem and since we can never intentionally replicate the performance problem I’m unable to prove the problem is due to the code. Adding indexes is an option I can try.
MoxyUskPreAllocation has a PK Clustered Index defined on UseKey, OrderID and PortID with a FILLFACTOR = 80
MoxyUskPreAllocLotDetail has a PK Clustered Index defined on UseKey, OrderID, PortID and TaxLog with a FILLFACTOR = 85
Almost every column is assigned a Default constraint.
Each night we run DBCC CHECKDB, CHECKALLOC, CHECKCATALOG and DBREINDEX. After speaking with Microsoft today we are going to run "update statistics" during the day every 3 or 4 hours to see if that helps. Do you run it during the day?
MoxyUskPreAllocation has under 9,000 rows
MoxyUskPreAllocLotDetail has just over 4,000 rows.
MoxyPreAllocation = 49,574 rows
MoxyTranType = 5 rows
MoxyOrders = 4,314 rows
MoxyTaxLot = 715,591 rows
MoxySecMaster = 36,782 rows
MoxySecType = 756 rows
MoxyPreAlocLotDetail = 40,862
The view vMoxyPreAllocLotDetail references MoxyAllocLotDetail and MoxyAllocation, where MoxyAllocLotDetal has over 89,000 rows and MoxyAllocation contains over 61,000 rows.
CREATE VIEW vMoxyPreAllocLotDetail
AS
SELECT t.OrderID, t.PortID, t.TaxLotID, t.CloseQuantity, t.CloseQuantity -
(SELECT COALESCE( SUM( d.CloseQuantity ), 0 )
FROM MoxyAllocLotDetail d, MoxyAllocation a
WHERE d.taxLotID = t.taxLotID
AND a.allocID = d.allocID
AND a.OrderID = t.OrderID AND a.PortID = t.PortID
) UnallocCloseQuantity
FROM MoxyPreAllocLotDetail t
I'm not sure the vendor understood the concept of temp tables otherwise they would have seen that permanent tables should not be created in tempdb.
Thanks again, Dave
January 16, 2007 at 3:15 pm
Dave,
Your in a tough spot. I have a vendor here for one of our oracle instances, that has a lot of procedures. I have had to add hints to their procedures because the optimizer was choosing the wrong paths. I documented the changes internally and every code revision I put my hints back into their code. In my case it took a 20 hour process down to 4 hours.
In your case since you have the restored copy, I would really try the hash and merge hints in those queries. The number of logical reads is telling me that it has to be choosing the wrong plan. set statistics io on and set statistics time on will really isolate what tables are the root of the problem and at least give you a starting point for analyzing any new indexes. I would really modify the code in your test environment and prove to them that those hints are needed. If they are resistant, maybe you can get lucky and the Business clients will push you for better performance and will stand behind your recomended changes.
I'll give you an example of a bad loop join:
MoxyTaxLot = 715,591 rows
MoxySecMaster = 36,782 rows
table scan and loop join= 26 billion logical reads.
Keep testing and run those statistics and you'll start to see the bottleneck.
Tom
January 17, 2007 at 7:52 am
Thanks Tom. I'll give it a try.
January 17, 2007 at 9:11 am
Tom sounds like he has you on the right path...just to give you a little support I was in the same situation you are in the past. Vendor product with weird times of slowness during the day which could never be replicated. Updates and queries would take minutes vs. seconds normally.
After Months of discussion it finally got to the right people and after their analysis it was discovered that a main table in use needed another index.
After the index was inplace it took queries and updates down from several minutes to a matter of a second and I NEVER heard from end users again regarding the speed.
Sounds like you are doing everything right from my limited knowledge of indepth troubleshooting and Tom has done an excellent job helping you.
Good Luck
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply