Query reporting a lot of seconds 'locked'

This is a highly ambiguous question, as I am not even sure what I am asking, but looking for guidance.

Basically, a system went went live a few weeks back. The DBAs have sent me a screen shot, which is created by an application called 'Fog Light' I believe, which monitors performance. It shows that most stored procs have a low 'duration' of being locked, around 1000 seconds per hour. However, there's one that reports 73,000 seconds.

That number, alone, to me, means not much. Maybe the proc is heavily used? Compared to the rest? However, when we execute it, it开发者_运维技巧 takes 4 seconds to run on production data. Not too bad, considering what it's doing. Their DBAs say that that is acceptable, but are concerned about the number of locking duration it's got against it.

What does that mean? And where would I start hunting for issues? The proc does 5 UNIONS, and selects the result set into a #temp table. It then does a simple filter on the #Temp, and returns the result.

Sorry I don't have much more info - I am unsure where to start looking - and .. is it even a problem?

The first thing I would do is add the following line to the top of the offending stored procedure.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Try to sift though the msdn page on isolation levels.

In general it means you don't have to wait for other transactions to finish. But here are the highlights of READ UNCOMMITTED

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

Also, I guarantee that if you say "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" to someone they will think you are a GENIUS.