September 16, 2010 at 12:26 pm
I have a Stored Procedure i have a huge select statememt and i am coputing the columns in the select statement.That Stored Procedure was running fine since long time and then our Vendor who provided the Database for us changed the Tables to Views since then we are having problem running the Stored procedure . it is hanging at the huge select statement. so i worke'd around the view and it got fixed in out test environment. and is not working in the Prod environment.
We refreshed our test with PROD. it has the same Data as of Prod even now our test is working fine i could see that my query run's in 2 min in test and in prod if took like more than 40 min and it was still running so i had to kill the query
Please help
September 16, 2010 at 12:43 pm
Compare the estimated execution plans to ensure that all indexes are the same and enabled on both environments.
Check your MAXDOP on each server and make sure they're the same
Post the query/table definitions here if that doesn't help.
September 16, 2010 at 1:06 pm
please post both execution graphical execution plans (you can save them as .sqlplan and attacht them in a reply) ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 16, 2010 at 1:54 pm
the Index's seems to be the same in both the environments.
But the MAXDOP is different because our environments are set up like that
and so i was using Option MAXDOP =1 even then it did not slove the proble for me
FROM EElistWITH(Nolock)
INNER JOIN EcWITH(NoLock)
ONEElist.EEID = Ec.EecEEID and EElist.COID = Ec.EecCOID
INNER JOIN EPWITH(Nolock)
ON EElist.EEID = Ep.EepEEID
INNER JOIN EDWITH(Nolock)
ON EElist.EEID = Ed.EedEEID and EElist.COID = Ed.EedCOID and Ed.EedDedCode = Eelist.DedCode
INNER JOIN CoWITH(Nolock)
ONEElist.COID= Co.CmpCOID
INNER JOIN DcWITH(Nolock)
ONEELIST.DedCode = Dc.DedDedCode
LEFT OUTER JOIN ABBLFEDWITH(Nolock)
ONEd.EedEEID= ABBLFED.EedEEID And Ed.EedCOID = ABBLFED.EedCOID AND ABBLFED.EedDedCode = 'ABBLF'
This is my Table definition
if i remove these two Joins i was able to get the query Run
INNER JOIN EDWITH(Nolock)
ON EElist.EEID = Ed.EedEEID and EElist.COID = Ed.EedCOID and Ed.EedDedCode =
Eelist.DedCode
LEFT OUTER JOIN ABBLFEDWITH(Nolock)
ONEd.EedEEID= ABBLFED.EedEEID And Ed.EedCOID = ABBLFED.EedCOID AND ABBLFED.EedDedCode = 'ABBLF'
September 16, 2010 at 1:58 pm
Check your execution plan to see where it's hanging. I assume all of those ID and Code fields that you're comparing on have indexes?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply