August 11, 2008 at 11:39 am
Scenario.... the following query took ages to execute
SELECT Getdate(),'01 Aug 2008 00:00:00', '01 Aug 2008 00:00:00', RDWGLFundId, RDWGLTkrId , RDWINCExternalCode,
'CRGL', RDWINSGeoAnal2, TYPE.RDWIYInstrumentTypeId, INST.RDWIYSubCatOption, RDWINSLocalCcy, 0, 0,
RDWGLClosingBase, RDWGLMarketBaseGainLoss, RDWGLCurrGainLoss, RDWGLTotalGainLoss,' ' ,0
FROM RDW_TRAN..mRealizedGainLoss JOIN RDW_TRAN..mPortfolioTransactions
ON CONVERT(VARBINARY(15),RTRIM(RDWPTTransactionNumber)) = CONVERT(VARBINARY(15),RTRIM(RDWGLTrxId))
LEFT JOIN RDW_TRAN..sRDWInstCode ON RDWGLTkrId = RDWINCInstrumentId AND RDWINCInstCodeId = 13
JOIN RDW_TRAN..sRDWInstrument ON RDWGLTkrId = RDWINSInstrumentId
JOIN RDW_TRAN..sRDWInstrumentType INST ON RDWGLTkrId = INST.RDWIYInstrumentTypeID
JOIN RDW_TRAN..sRDWInstrumentType TYPE ON TYPE.RDWIYInstrumentTypeID = INST.RDWIYParentId
WHERE (RDWGLFundId = 972 AND RDWPTFundId = 972 ) AND RDWPTPostingDateTime BETWEEN '01 Aug 2008 00:00:00'
AND '01 Aug 2008 00:00:00'AND RDWGLTKRCode <> 8111 AND TYPE.RDWIYInstrumentTypeId <> 145 AND
(RDWGLCSId <> 1 AND RDWPTCSId <> 1)
but when i ran this in the evening, it just took 4 secs and even now also...
Today, the below query failed
SELECT Getdate(),'08 Aug 2008 00:00:00', '08 Aug 2008 00:00:00', RDWGLFundId, RDWGLTkrId , RDWINCExternalCode,
'CRGL', RDWINSGeoAnal2, TYPE.RDWIYInstrumentTypeId, INST.RDWIYSubCatOption, RDWINSLocalCcy, 0, 0,
RDWGLClosingBase, RDWGLMarketBaseGainLoss, RDWGLCurrGainLoss, RDWGLTotalGainLoss,' ' ,0
FROM RDW_TRAN..mRealizedGainLoss JOIN RDW_TRAN..mPortfolioTransactions
ON CONVERT(VARBINARY(15),RTRIM(RDWPTTransactionNumber)) = CONVERT(VARBINARY(15),RTRIM(RDWGLTrxId))
LEFT JOIN RDW_TRAN..sRDWInstCode ON RDWGLTkrId = RDWINCInstrumentId AND RDWINCInstCodeId = 13
JOIN RDW_TRAN..sRDWInstrument ON RDWGLTkrId = RDWINSInstrumentId
JOIN RDW_TRAN..sRDWInstrumentType INST ON RDWGLTkrId = INST.RDWIYInstrumentTypeID
JOIN RDW_TRAN..sRDWInstrumentType TYPE ON TYPE.RDWIYInstrumentTypeID = INST.RDWIYParentId
WHERE (RDWGLFundId = 972 AND RDWPTFundId = 972 ) AND RDWPTPostingDateTime BETWEEN '08 Aug 2008 00:00:00'
AND '08 Aug 2008 00:00:00'AND RDWGLTKRCode <> 8111 AND TYPE.RDWIYInstrumentTypeId <> 145 AND
(RDWGLCSId <> 1 AND RDWPTCSId <> 1)
if you see the diff between two queries in date and nothing else. The first query took long time first and very less time now. This is repeated in the second time but today evening also, it's taking 5 min for second query but same 4 secs for the first query.
I'm not sure why this is so!! There are no jobs running... No active processes at that time... I even tried these two queries with NOLOCK but of no use.
Can you adivse something on this?
I have a suspicion on indexing... I have no idea on how to check when was the last indexing on the tables run and its status like success or failure or success with warning etc... i'm not sure if it's possible... Can you please let me know about identifying the history of run indexing on specified tables or database along with their status of runs? particuarly, i got this doubt due to its failure to execute on Monday mostly. Remaining days, this query goes through peacefully.
Your earlier response is requested.
Many Thanks in advance,
Sarath
August 11, 2008 at 12:03 pm
Is this a production database?
There could be a number of things you need to address.
Do you have indexes on each table this query involves? Make sure they include the appropiate columns and the Fill Factor is set.
Do you have a DB matenience plan that rebuilds your indexes on a set schedule? If so you can check the log to make sure it ran.
After checking your indexes you can rebuild them manually if need me but if this is a production DB you may not want to do that cause it could cause performance degradation while it rebuilds the indexes.
If all is okay on the index part you may need to tune your query. You'll have to identify which part/s of the query are affecting the performance. Comment outall the joins but one then add another join one at a time. Each time run the query to see how fast it executes.
August 11, 2008 at 12:29 pm
yes, it's a production server... Unfortunatley, i don't have any idea on indexing... do you have any link which helps me to identify indexing?
August 11, 2008 at 12:59 pm
Can you post the table and index definitions for the tables involved in the query please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2008 at 1:20 pm
ON CONVERT(VARBINARY(15),RTRIM(RDWPTTransactionNumber)) = CONVERT(VARBINARY(15),RTRIM(RDWGLTrxId))
By using the convert and the trim on the join it usually takes the query off any indexes that exist and can significantly slow any queries.
Have you tried the "Display Execution Plan" function on the query to fond the worst offenders?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply