Blocking Issue...

Question

Blocking Issue...

Raj-302673

SSC Enthusiast

Points: 166
More actions
May 3, 2007 at 2:19 am

#62295

Hi All ,
I have an issue in my application, sometimes users reported that system is slow, we checked with the DBA guys and found blocking on the database which causing interruption in query execution.
In normal scenario, DBA remove the blocking and send the query which causing blocking. But it cause a huge impact in process.
I have some query:
#1. Is it any way to find out the Root Cause of blocking? As we have the queries which causing the blocking but all are OK .
#2. Is it any way to prevent it?
#3. How to monitor it without run Tracing as we dont have Rights for that .
Regards,
Manu Raj

Thanks & Regards,
Raj

Viewing 8 posts - 1 through 7 (of 7 total)

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

SQLZ SSChampion Points: 12872 More actions · Answer 1

I'd talk to your DBAs and ask them the very same questions you're asking here. That should typically come under their responsibility (I would say).

If you don't have rights to run a trace then it's probably because the DBAs have reason to restrict that right.

Karl
source control for SQL Server

Raj-302673 SSC Enthusiast Points: 166 More actions · Answer 2

Hi SQLZ ,

Is tracing is only the way to monitor the blocking???

Other thing is, in my 1st question, I asking for the Root cause. Do you have any idea, its an indexing problem or space problem or database architecture or something else...

Regards,

Manu Raj

Thanks & Regards,
Raj

SQLZ SSChampion Points: 12872 More actions · Answer 3

Hi Manu,

You monitor blocking in various ways. I'd start with looking at sp_who2 and sp_lock - check them out in BOL. I'd also read up on locking and blocking in BOL.

It's impossible to say what the root cause of your blocking is without having a good idea of what the design (logical and physical) of your system looks like and without knowing what transactions are running against it. This is why I said you should ask your DBA - that's what they're there for. Secondly, it sounds like you've already been told what the root cause is - didn't you say that the DBA sends you the offending transaction? That would be a good place to start with regards to the root cause.

Something you have to realise about locking/blocking is that it is a fundamental and essential part of all transactional databases. Some process updates data in a table and locks that data so that other processes are blocked from being able to read/update the same data. It's a fact of life. There are ways around this - some of which are applicable in certain situations and some of which aren't applicable.

The point is that you will never be able to completely avoid blocking so your aim should be to maximise the performance of transactions such that resources are locked for the least amount of time possible - thereby minimizing the length of time for which other processes are blocked. How you maxmise the performance of your transactions depends on a multitude of factors. Proper coding practices, proper use of transactions, proper database design, hardware, architecture, indexes, etc, etc. I could go on and on.

In a single isolated case what you're interested in (at a very very basic level) is:

1. What process(es) are being blocked

2. What process is causing the blocking

3. Why is it blocking

4. How can you improve the performance of the process causing the blocking (item 2) such that you minimize item 1.

It sounds like your DBA has already given you 1. and 2. So with your DBAs help you should be able to find out the answers to questions 3. and 4.

Hope that helps,

Karl
source control for SQL Server

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 4

Based on my experiences, most blocks originate from the scripts/queries and database designs. Being a DBA, he can only tell you what blocks what and what scripts may be criminals.

As a developer, he should write good scripts/queries. He should try to avoid writing a long transaction in the sense of time to execute. He can include different isolation level and no lock clauses in his code.

Of course, DBA should provide a good database design. To avoid locks, developers and DBAs should cooperate. A good design for a database needs information on the scripts/queries. In the same token, good scripts/queries are based on a good design for a database. Without good cooperations between developers and DBAs, what we could do are finger-pointing at each other.

Raj-302673 SSC Enthusiast Points: 166 More actions · Answer 5

Hi,

I agree with all the inputs, but the thing is that the scripts and query which DBA sent were all OK.

I want to know the Root Causes which can creates blocking in the database, may it not possible but I desperately want to know the list of cases which can cause the Blocking.

According to this list I will proceeds with my end, as per my previous experience I know that DBA will not help as much as I can get here...

Hope a positive response

Thanks & Regards,
Raj

Hans Lindgren SSChampion Points: 10496 More actions · Answer 6

Hi,

What do you think means by blocking? What the people in this thread has already said is that blocking is normal in a RDBMS system. Even if you have two 'normal' queries, they can Block eachother.

I.e: Query 1 is inserting into table A for 10 minutes (by one single insert statement)

Query 2 tries to do a SELECT * from table A.

If Query 2 is executed just after Query 1, Query 2 will be Blocked for 10 minutes.

This is normal and both queries are 'good'.

What kind of Root Cause are you looking for?

Regards,

Hanslindgren

Anders Pedersen SSChampion Points: 11410 More actions · Answer 7

Have you lookd at the front end code that calls the offending stored procedure? At various times I have found front end developers having put transaction around a call to a stored procedure, and leave it open for too long. Or even worse, transaction that is open while waiting for user input.