Im tasked to investigate a stored proc that is slow at certain times ( 20-30mins ) and fast at other times ( 10-15 secs ).

After following suggestions i found out that adding NOLOCK hint on all the select queries makes the execution time fast ( 10-20 secs) .

Although i know that adding NOLOCK means a dirty read of possible incorrect data, does it justify the performance boost?

Speed or data correctness?

Maybe the problem is some other procedure/query/wtv doing writes and locking for too long? (I'm guessing that procedure just does reads.)

It depends on why you are reading.

If its just rough statistics its probably not a problem but if the results are stored and can affect future reads it can escalate.

Try to monitor locking while it runs slow to see what other calls that are blocking.

Also, some times breaking apart larger queries can minimize blocking but its very much situation dependent.

@shellbug this is the first suspect i found. There are scheduled jobs running on the same server that actually causes the slowdown. But since i do not have access to the jobs, i am trying to explore what can be done on my query.

@Voxera is there a line between what is considered tolerable for data correctness and the performance requirements? I mean, would the users be ok with waiting half an hour for the correct data, instead of waiting only seconds for a possible incorrect data.

@noisyass2 that is almost only dependent on the data and the users.

But the more personal interest the viewer has the more they care about correctness.

For example, daily visitor count in a supermarket can be very important if used to judge a campaign but if just for some trivia it would not matter if its slightly of from time to time.

A daily order summary probably don’t have to be exact during the day but once the day is over it better be ;), but the sales person that just closed the deal of the year will most likely complain if their result is not counted immediately.

If exact results are very important an option, while much more difficult, is to log all changes separately so you never have any updates to the data you read, that way you can avoid dirty reads without locking. You just will not get the very latest writes if they overlap you reading.

But if you are reading changing data it is much more difficult to achieve as you would have to use something like event sourcing which is quite a big change in most cases.