Timing Issue

Question

Timing Issue

tinausa

SSCrazy

Points: 2697
More actions
December 6, 2013 at 12:53 pm

#302113

I am running SQL Server 2012 on an i7 Windows 7-64 PC.
Lately I have noticed that when I execute a stored proc, som parts of it do no trun.
These stored proc have not been modified. The behaviour is not consistent and not in the same code.
Some times a table that needs to be deleted and recreated does not get deleted.
Or an update query would not update.
I am guessing that this could be due to timing issues - a query has not finished executing, when the next one kicks in - given the inconsistent nature of the the problem.
Has anyone noticed this? This has begun occurring in the last two months or so. I never saw it before that.
What else could be the reason? It happens in different stored proc in different places. These have been running without problems for a while.
Thank,
Tina

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 1

Try to make a log of the executions/SPs in order to locate your issue. It seems the executions order and theirs finishing times are important in your case.

Igor Micev,My blog: www.igormicev.com

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Are you using NOLOCK hints? This could be one cause.

It would help a lot to see what the SP does.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 3

Luis Cazares (12/6/2013)
Are you using NOLOCK hints? This could be one cause.
It would help a lot to see what the SP does.

Try to avoid using hints, unless when you're very sure.

My thought was if you can log the Start and End times of the SPs/Queries executions? and then see whether what you're guessing is true.

Igor Micev,My blog: www.igormicev.com

tinausa SSCrazy Points: 2697 More actions · Answer 4

tinausa

SSCrazy

Points: 2697

December 7, 2013 at 6:53 am

#1671718

Thanks SSCrazy, good idea. I will do that.

tinausa SSCrazy Points: 2697 More actions · Answer 5

tinausa

SSCrazy

Points: 2697

December 7, 2013 at 6:53 am

#1671719

I am not using NOLOCKS.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 6

is there any TRY CATCH Clause you are using inside that store procedure ?

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 7

Yes there is TRY .. CATCH in T-SQL - http://technet.microsoft.com/en-us/library/ms175976.aspx

Regards,

IgorMi

Igor Micev,My blog: www.igormicev.com

Umer Akram SSC-Insane Points: 22209 More actions · Answer 8

IgorMi (12/9/2013)
Yes there is TRY .. CATCH in T-SQL - http://technet.microsoft.com/en-us/library/ms175976.aspx
Regards,
IgorMi

ahaan nice, thats why your procedure is in the silent mode 🙂

try to execute your procedure without TRY CATCH to check on which particular statement, exception is being generated.

and Rollback transaction in your catch clause to avoid the incomplete execution of batch.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

twin.devil (12/9/2013)
IgorMi (12/9/2013)
Yes there is TRY .. CATCH in T-SQL - http://technet.microsoft.com/en-us/library/ms175976.aspx
Regards,
IgorMi
ahaan nice, thats why your procedure is in the silent mode 🙂

Igor just said there is a try-catch construct available in SQL Server. The OP did not say whether or not there is try catch in their procedure.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Umer Akram SSC-Insane Points: 22209 More actions · Answer 10

GilaMonster (12/9/2013)
ahaan nice, thats why your procedure is in the silent mode 🙂

Igor just said there is a try-catch construct available in SQL Server. The OP did not say whether or not there is try catch in their procedure.[/quote]

Thanks for the correcting me Gila, i just read the 1st two words of reply ... 😀

Perhaps i should i ask my question again .... :hehe:

Igor, are you using CRY CATCH Clause in the Store Procedure which is having this inconsistent behavior?

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 11

twin.devil (12/9/2013)
GilaMonster (12/9/2013)
ahaan nice, thats why your procedure is in the silent mode 🙂
Igor just said there is a try-catch construct available in SQL Server. The OP did not say whether or not there is try catch in their procedure.

Thanks for the correcting me Gila, i just read the 1st two words of reply ... 😀

Perhaps i should i ask my question again .... :hehe:

Igor, are you using CRY CATCH Clause in the Store Procedure which is having this inconsistent behavior?[/quote]

I usually use TRY..CATCH clauses for transactions in stored procedures.

Igor Micev,My blog: www.igormicev.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Igor's not the OP.

Tina, any chance you could post the definitions of the procedure having the problem? Also, consider putting PRINT statements or using RAISERROR to raise informational messages to help track the flow of control through the procedures?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tinausa SSCrazy Points: 2697 More actions · Answer 13

Thanks SSCrazy, but i am not using TRY...Catch.

Tina.

tinausa SSCrazy Points: 2697 More actions · Answer 14

Thanks SSC-Forever, these are about half-a-dozen stored proc that I have created and used for over 8-9 months. They did not begin creating this problem, until recently.

The problem does not always happen and does not happen at the same line. If I were to run it three times, it would not happen once, but maybe it would happen other two times but on different lines.

Examples would be an INSERT would not be inserted or an UPDATE would not update or a delete table would not execute. The error would then show up in a statement following line, which depends on a prior INSERT, UPDATE or DELETE.

I have tried using PRINT statements, and then the problem does not happen. Coming from some programming background, that's what made me suspect a timing issue, as PRINT would automatically give the needed time to complete execution of a prior line. But that was just a thought.

Thanks,

Tina